Dba_indexes view Performance Analysis

select case when status='UNUSABLE' then        'alter index '||owner||'.'||index_name||' rebuild online compute statistics;'      when to_number(degree)>1 then        'alter index /* '||degree ||' */'||owner||'.'||index_name||' noparallel;'    end casefrom (select * from dba_indexes where degree<>‘DEFAULT') awhere status='UNUSABLE'or to_number(degree)>1and owner not in ('SYS','SYSTEM','MANAGER','WMSYS');

The statement runs quickly, but it is found from statspack that the logic read of this statement is as high as 26846 at a time. UseSet autotraceAfter comparing the execution plans and statistics of 9i and 10g, we found that the cost of 9i querying this view is very high, while 10G has improved. In Oracle9i, optimizer_mode is CHOOSE by default, so RBO is used for data dictionary query, while Oracle10g is ALL_ROWS by default, so CBO is used.

SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionPL/SQL Release 9.2.0.6.0 - ProductionCORE  9.2.0.6.0    ProductionTNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - ProductionNLSRTL Version 9.2.0.6.0 - ProductionSQL> set autot traceSQL> select * from dba_indexes;1242 rows selected.Execution Plan----------------------------------------------------------  0   SELECT STATEMENT Optimizer=CHOOSE  1  0  NESTED LOOPS (OUTER)  2  1   NESTED LOOPS (OUTER)  3  2    NESTED LOOPS  4  3     NESTED LOOPS  5  4      NESTED LOOPS (OUTER)  6  5       NESTED LOOPS  7  6        NESTED LOOPS (OUTER)  8  7         NESTED LOOPS  9  8          TABLE ACCESS (FULL) OF 'OBJ$' 10  8          TABLE ACCESS (BY INDEX ROWID) OF 'IND$' 11  10           INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE) 12  7         TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 13  12          INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 14  6        TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 15  14         INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 16  5       TABLE ACCESS (CLUSTER) OF 'USER$' 17  16        INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 18  4      TABLE ACCESS (CLUSTER) OF 'USER$' 19  18       INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 20  3     TABLE ACCESS (CLUSTER) OF 'USER$' 21  20      INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 22  2    TABLE ACCESS (CLUSTER) OF 'SEG$' 23  22     INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 24  1   TABLE ACCESS (CLUSTER) OF 'TS$' 25  24    INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)Statistics----------------------------------------------------------     0 recursive calls     0 db block gets   42924 consistent gets     0 physical reads     0 redo size   98000 bytes sent via SQL*Net to client    1558 bytes received via SQL*Net from client     84 SQL*Net roundtrips to/from client     0 sorts (memory)     0 sorts (disk)    1242 rows processed

SQL>select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE  10.2.0.4.0   ProductionTNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - ProductioNLSRTL Version 10.2.0.4.0 - Production21:32:11 SYS@coll>set autot trace21:32:15 SYS@coll>select * from dba_indexes;1162 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3901056803----------------------------------------------------------------------------------------------| Id | Operation              | Name  | Rows | Bytes | Cost (%CPU)| Time   |----------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT          |    | 1164 |  330K|  237  (3)| 00:00:03 ||* 1 | HASH JOIN RIGHT OUTER       |    | 1164 |  330K|  237  (3)| 00:00:03 ||  2 |  TABLE ACCESS FULL         | TS$  |   9 |  189 |   4  (0)| 00:00:01 ||* 3 |  HASH JOIN RIGHT OUTER       |    | 1164 |  306K|  232  (3)| 00:00:03 ||  4 |  TABLE ACCESS FULL        | SEG$  | 2635 |  102K|  18  (0)| 00:00:01 ||* 5 |  HASH JOIN            |    | 1164 |  261K|  214  (3)| 00:00:03 ||  6 |   TABLE ACCESS FULL        | USER$ |  35 |  560 |   2  (0)| 00:00:01 ||* 7 |   HASH JOIN            |    | 1164 |  243K|  211  (3)| 00:00:03 ||  8 |   TABLE ACCESS FULL       | USER$ |  35 |  560 |   2  (0)| 00:00:01 ||* 9 |   HASH JOIN RIGHT OUTER     |    | 1164 |  225K|  208  (2)| 00:00:03 || 10 |    TABLE ACCESS FULL       | USER$ |  35 |  560 |   2  (0)| 00:00:01 ||* 11 |    HASH JOIN           |    | 1164 |  206K|  206  (2)| 00:00:03 ||* 12 |    HASH JOIN OUTER       |    | 1164 |  172K|  174  (2)| 00:00:03 || 13 |     MERGE JOIN         |    | 1164 |  142K|  142  (2)| 00:00:02 ||* 14 |     TABLE ACCESS BY INDEX ROWID| IND$  | 1164 |  104K|  109  (0)| 00:00:02 || 15 |      INDEX FULL SCAN      | I_IND1 | 1164 |    |   2  (0)| 00:00:01 ||* 16 |     SORT JOIN         |    | 10589 |  341K|  33  (7)| 00:00:01 ||* 17 |      TABLE ACCESS FULL     | OBJ$  | 10589 |  341K|  32  (4)| 00:00:01 || 18 |     TABLE ACCESS FULL      | OBJ$  | 10592 |  279K|  31  (0)| 00:00:01 || 19 |    TABLE ACCESS FULL      | OBJ$  | 10592 |  310K|  31  (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  1 - access("I"."TS#"="TS"."TS#"(+))  3 - access("I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+) AND       "I"."TS#"="S"."TS#"(+))  5 - access("IO"."OWNER#"="IU"."USER#")  7 - access("U"."USER#"="O"."OWNER#")  9 - access("ITO"."OWNER#"="ITU"."USER#"(+)) 11 - access("I"."BO#"="IO"."OBJ#") 12 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+)) 14 - filter(BITAND("I"."FLAGS",4096)=0) 16 - access("O"."OBJ#"="I"."OBJ#")    filter("O"."OBJ#"="I"."OBJ#") 17 - filter(BITAND("O"."FLAGS",128)=0)Statistics----------------------------------------------------------     0 recursive calls     0 db block gets    876 consistent gets     0 physical reads     0 redo size   92582 bytes sent via SQL*Net to client    1339 bytes received via SQL*Net from client     79 SQL*Net roundtrips to/from client     1 sorts (memory)     0 sorts (disk)    1162 rows processedselect /*+ rule */* from dba_indexes;1162 rows selected.Elapsed: 00:00:00.55Execution Plan----------------------------------------------------------Plan hash value: 2107813288--------------------------------------------------------------| Id | Operation              | Name      |--------------------------------------------------------------|  0 | SELECT STATEMENT          |        ||  1 | NESTED LOOPS OUTER         |        ||  2 |  NESTED LOOPS OUTER        |        ||  3 |  NESTED LOOPS           |        ||  4 |   NESTED LOOPS          |        ||  5 |   NESTED LOOPS OUTER       |        ||  6 |    NESTED LOOPS         |        ||  7 |    NESTED LOOPS OUTER      |        ||  8 |     NESTED LOOPS        |        ||* 9 |     TABLE ACCESS FULL     | OBJ$      ||* 10 |     TABLE ACCESS BY INDEX ROWID| IND$      ||* 11 |      INDEX UNIQUE SCAN     | I_IND1     || 12 |     TABLE ACCESS BY INDEX ROWID | OBJ$      ||* 13 |     INDEX UNIQUE SCAN     | I_OBJ1     || 14 |    TABLE ACCESS BY INDEX ROWID | OBJ$      ||* 15 |     INDEX UNIQUE SCAN      | I_OBJ1     || 16 |    TABLE ACCESS CLUSTER     | USER$     ||* 17 |    INDEX UNIQUE SCAN      | I_USER#    || 18 |   TABLE ACCESS CLUSTER      | USER$     ||* 19 |    INDEX UNIQUE SCAN       | I_USER#    || 20 |   TABLE ACCESS CLUSTER      | USER$     ||* 21 |   INDEX UNIQUE SCAN       | I_USER#    || 22 |  TABLE ACCESS CLUSTER       | SEG$      ||* 23 |   INDEX UNIQUE SCAN        | I_FILE#_BLOCK# || 24 |  TABLE ACCESS CLUSTER       | TS$      ||* 25 |  INDEX UNIQUE SCAN        | I_TS#     |--------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  9 - filter(BITAND("O"."FLAGS",128)=0) 10 - filter(BITAND("I"."FLAGS",4096)=0) 11 - access("O"."OBJ#"="I"."OBJ#") 13 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+)) 15 - access("I"."BO#"="IO"."OBJ#") 17 - access("ITO"."OWNER#"="ITU"."USER#"(+)) 19 - access("U"."USER#"="O"."OWNER#") 21 - access("IO"."OWNER#"="IU"."USER#") 23 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND       "I"."BLOCK#"="S"."BLOCK#"(+)) 25 - access("I"."TS#"="TS"."TS#"(+))Note-----  - rule based optimizer used (consider using cbo)Statistics----------------------------------------------------------     0 recursive calls     0 db block gets   25254 consistent gets     26 physical reads     0 redo size   93977 bytes sent via SQL*Net to client    1339 bytes received via SQL*Net from client     79 SQL*Net roundtrips to/from client     0 sorts (memory)     0 sorts (disk)    1162 rows processed