oracle 25254,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 case

from (select * from dba_indexes where degree<>‘DEFAULT') a

where status='UNUSABLE'

or to_number(degree)>1

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

PL/SQL Release 9.2.0.6.0 - Production

CORE 9.2.0.6.0 Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production

NLSRTL Version 9.2.0.6.0 - Production

SQL> set autot trace

SQL> 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 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio

NLSRTL Version 10.2.0.4.0 - Production

21:32:11 SYS@coll>set autot trace

21: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 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 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