|
Real Application Clusters
|
Last time, I attempted to add node_no column to the head of the index so that block
contention among instances can be avoided. node_no identifies an instance.
By doing this, I'm sure we can prevent the contention of the same block from occurring
at insertion process.
As I changed index configuration, I needed to check if this change affected scan process
that was the essential purpose of the index. According to the result, index scan
was shifted to full table scan.
Adding node_no does not mean anything if the scan process slows down.
So, I added hint clause to let index scan be executed. This time, I can stick with
the index scan.
******************************************************
(Data from last week)
SQL> select /*+ index(logtbl1 idx_logtbl1) */ *
2 from logtbl1
3 where ins_date > sysdate -1 and ins_date <= sysdate;
6666 rows selected
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=832
Bytes=215488)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'LOGTBL1' (Cost=29
Card=832 Bytes=215488)
3 2 INDEX (FULL SCAN) OF 'IDX_LOGTBL1' (NON-UNIQUE)
(Cost=26 Card=832)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1624 consistent gets
0 physical reads
0 redo size
926564 bytes sent via SQL*Net to client
5387 bytes received via SQL*Net from client
446 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6666 rows processed
******************************************************
|
The execution plan above indicates that index full scan is executed.
Index skip scan is newly introduced in Oracle9i. Even though the first
column of the compound index is not described in where clause,
the compound index is still available.
Let's try index skip scan.
******************************************************
SQL> select /*+ index_ss(logtbl1 idx_logtbl1) */ *
2 from logtbl1
3 where ins_date > sysdate -1 and ins_date <= sysdate;
6666 rows selected
Exexution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=832
Bytes=215488)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'LOGTBL1' (Cost=14
Card=832 Bytes=215488)
3 2 INDEX (SKIP SCAN) OF 'IDX_LOGTBL1' (NON-UNIQUE)
(Cost=11 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1938 consistent gets
0 physical reads
0 redo size
928575 bytes sent via SQL*Net to client
5387 bytes received via SQL*Net from client
446 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6666 rows processed
******************************************************
|
consistent gets slightly increases.
Let's check SQL TRACE.
(Index full scan)
********************************************************************************
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 446 0.29 0.30 0 1624 0 6666
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 448 0.29 0.30 0 1624 0 6666
Rows Row Source Operation
------- ---------------------------------------------------
6666 FILTER (cr=1624 r=0 w=0 time=285041 us)
6666 TABLE ACCESS BY INDEX ROWID LOGTBL1 (cr=1624 r=0 w=0 time=278196 us)
6666 INDEX FULL SCAN IDX_LOGTBL1 (cr=1047 r=0 w=0 time=259729 us)(object id 7584)
********************************************************************************
|
(Index skip scan)
********************************************************************************
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 446 0.09 0.11 0 1938 0 6666
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 448 0.09 0.11 0 1938 0 6666
Rows Row Source Operation
------- ---------------------------------------------------
6666 FILTER (cr=1938 r=0 w=0 time=86471 us)
6666 TABLE ACCESS BY INDEX ROWID LOGTBL1 (cr=1938 r=0 w=0 time=79785 us)
6666 INDEX SKIP SCAN IDX_LOGTBL1 (cr=1361 r=0 w=0 time=57261 us)(object id 7584)
********************************************************************************
|
Number of blocks fetched at index skip scan is larger than that at
index full scan, but the time required for processing index skip scan
is less than that of index full scan.
I have added hint clause to SQL statement this time but there is another way.
If you execute ANALYZE, the optimizer automatically determines and
selects index skip scan.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle