dba

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 - Production

SQL> set autot traceSQL> select * from dba_indexes;

1242 rows selected.

Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS (OUTER)2 1 NESTED LOOPS (OUTER)3 2 NESTED LOOPS4 3 NESTED LOOPS5 4 NESTED LOOPS (OUTER)6 5 NESTED LOOPS7 6 NESTED LOOPS (OUTER)8 7 NESTED LOOPS9 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 calls0 db block gets42924 consistent gets0 Physical reads0 redo size98000 bytes sent via SQL*Net to client1558 bytes received via SQL*Net from client84 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1242 rows processed

SQL>select * from v$version;BANNER----------------------------------------------------------------Oracle."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 calls0 db block gets876 consistent gets0 physical reads0 redo size92582 bytes sent via SQL*Net to client1339 bytes received via SQL*Net from client79 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1162 rows processed

select /*+ rule */* from dba_indexes;

1162 rows selected.

Elapsed: 00:00:00.55

Execution 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 calls0 db block gets25254 consistent gets26 physical reads0 redo size93977 bytes sent via SQL*Net to client1339 bytes received via SQL*Net from client79 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1162 rows processed