dba_indexes视图的性能
前端时间，在监控系统中加入了index的状态是否为unusable，以及其并行度是否有设置的监控：
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
