dba_indexes视图的性能分析

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');

语句运行的速度很快，但是从statspack中发现这条语句的逻辑读单次高达26846。使用set autotrace比较了下9i和10g的执行计划和统计信息，发现9i查询这个视图的代价非常的高，而10g则有了一定的改善。在Oracle9i中，optimizer_mode默认是CHOOSE，所以查询数据字典使用了RBO，而Oracle10g则默认为ALL_ROWS，所以采用了CBO。

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