dba_indexes视图的性能分析
<p>select case when status='UNUSABLE' then<br>
'alter index '||owner||'.'||index_name||' rebuild online compute statistics;'<br>
when to_number(degree)>1 then<br>
'alter index /* '||degree ||' */'||owner||'.'||index_name||' noparallel;'<br>
end case<br>
from (select * from dba_indexes where degree<>‘DEFAULT') a<br>
where status='UNUSABLE'<br>
or to_number(degree)>1<br>
and owner not in ('SYS','SYSTEM','MANAGER','WMSYS');</p>
<p>
语句运行的速度很快，但是从statspack中发现这条语句的逻辑读单次高达26846。使用<strong>set autotrace</strong>比较了下9i和10g的执行计划和统计信息，发现9i查询这个视图的代价非常的高，而10g则有了一定的改善。在Oracle9i中，optimizer_mode默认是CHOOSE，所以查询数据字典使用了RBO，而Oracle10g则默认为ALL_ROWS，所以采用了CBO。</p>
<p>
SQL> select * from v$version;</p>
<p>
BANNER<br>
----------------------------------------------------------------<br>
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production<br>
PL/SQL Release 9.2.0.6.0 - Production<br>
CORE 9.2.0.6.0 Production<br>
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production<br>
NLSRTL Version 9.2.0.6.0 - Production</p>
<p>
SQL> set autot trace<br>
SQL> select * from dba_indexes;</p>
<p>
1242 rows selected.</p>
<p>
Execution Plan<br>
----------------------------------------------------------<br>
0 SELECT STATEMENT Optimizer=CHOOSE<br>
1 0 NESTED LOOPS (OUTER)<br>
2 1 NESTED LOOPS (OUTER)<br>
3 2 NESTED LOOPS<br>
4 3 NESTED LOOPS<br>
5 4 NESTED LOOPS (OUTER)<br>
6 5 NESTED LOOPS<br>
7 6 NESTED LOOPS (OUTER)<br>
8 7 NESTED LOOPS<br>
9 8 TABLE ACCESS (FULL) OF 'OBJ$'<br>
10 8 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'<br>
11 10 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)<br>
12 7 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'<br>
13 12 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)<br>
14 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'<br>
15 14 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)<br>
16 5 TABLE ACCESS (CLUSTER) OF 'USER$'<br>
17 16 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)<br>
18 4 TABLE ACCESS (CLUSTER) OF 'USER$'<br>
19 18 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)<br>
20 3 TABLE ACCESS (CLUSTER) OF 'USER$'<br>
21 20 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)<br>
22 2 TABLE ACCESS (CLUSTER) OF 'SEG$'<br>
23 22 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)<br>
24 1 TABLE ACCESS (CLUSTER) OF 'TS$'<br>
25 24 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)</p>
<p>
Statistics<br>
----------------------------------------------------------<br>
0 recursive calls<br>
0 db block gets<br>
42924 consistent gets<br>
0 physical reads<br>
0 redo size<br>
98000 bytes sent via SQL*Net to client<br>
1558 bytes received via SQL*Net from client<br>
84 SQL*Net roundtrips to/from client<br>
0 sorts (memory)<br>
0 sorts (disk)<br>
1242 rows processed</p>
<p>
</p>
<p>
SQL>select * from v$version;<br>
BANNER<br>
----------------------------------------------------------------<br>
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi<br>
PL/SQL Release 10.2.0.4.0 - Production<br>
CORE 10.2.0.4.0 Production<br>
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio<br>
NLSRTL Version 10.2.0.4.0 - Production</p>
<p>
21:32:11 SYS@coll>set autot trace<br>
21:32:15 SYS@coll>select * from dba_indexes;</p>
<p>
1162 rows selected.</p>
<p>
Execution Plan<br>
----------------------------------------------------------<br>
Plan hash value: 3901056803</p>
<p>
----------------------------------------------------------------------------------------------<br>
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br>
----------------------------------------------------------------------------------------------<br>
| 0 | SELECT STATEMENT | | 1164 | 330K| 237 (3)| 00:00:03 |<br>
|* 1 | HASH JOIN RIGHT OUTER | | 1164 | 330K| 237 (3)| 00:00:03 |<br>
| 2 | TABLE ACCESS FULL | TS$ | 9 | 189 | 4 (0)| 00:00:01 |<br>
|* 3 | HASH JOIN RIGHT OUTER | | 1164 | 306K| 232 (3)| 00:00:03 |<br>
| 4 | TABLE ACCESS FULL | SEG$ | 2635 | 102K| 18 (0)| 00:00:01 |<br>
|* 5 | HASH JOIN | | 1164 | 261K| 214 (3)| 00:00:03 |<br>
| 6 | TABLE ACCESS FULL | USER$ | 35 | 560 | 2 (0)| 00:00:01 |<br>
|* 7 | HASH JOIN | | 1164 | 243K| 211 (3)| 00:00:03 |<br>
| 8 | TABLE ACCESS FULL | USER$ | 35 | 560 | 2 (0)| 00:00:01 |<br>
|* 9 | HASH JOIN RIGHT OUTER | | 1164 | 225K| 208 (2)| 00:00:03 |<br>
| 10 | TABLE ACCESS FULL | USER$ | 35 | 560 | 2 (0)| 00:00:01 |<br>
|* 11 | HASH JOIN | | 1164 | 206K| 206 (2)| 00:00:03 |<br>
|* 12 | HASH JOIN OUTER | | 1164 | 172K| 174 (2)| 00:00:03 |<br>
| 13 | MERGE JOIN | | 1164 | 142K| 142 (2)| 00:00:02 |<br>
|* 14 | TABLE ACCESS BY INDEX ROWID| IND$ | 1164 | 104K| 109 (0)| 00:00:02 |<br>
| 15 | INDEX FULL SCAN | I_IND1 | 1164 | | 2 (0)| 00:00:01 |<br>
|* 16 | SORT JOIN | | 10589 | 341K| 33 (7)| 00:00:01 |<br>
|* 17 | TABLE ACCESS FULL | OBJ$ | 10589 | 341K| 32 (4)| 00:00:01 |<br>
| 18 | TABLE ACCESS FULL | OBJ$ | 10592 | 279K| 31 (0)| 00:00:01 |<br>
| 19 | TABLE ACCESS FULL | OBJ$ | 10592 | 310K| 31 (0)| 00:00:01 |<br>
----------------------------------------------------------------------------------------------</p>
<p>
Predicate Information (identified by operation id):<br>
---------------------------------------------------</p>
<p>
1 - access("I"."TS#"="TS"."TS#"(+))<br>
3 - access("I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+) AND<br>
"I"."TS#"="S"."TS#"(+))<br>
5 - access("IO"."OWNER#"="IU"."USER#")<br>
7 - access("U"."USER#"="O"."OWNER#")<br>
9 - access("ITO"."OWNER#"="ITU"."USER#"(+))<br>
11 - access("I"."BO#"="IO"."OBJ#")<br>
12 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))<br>
14 - filter(BITAND("I"."FLAGS",4096)=0)<br>
16 - access("O"."OBJ#"="I"."OBJ#")<br>
filter("O"."OBJ#"="I"."OBJ#")<br>
17 - filter(BITAND("O"."FLAGS",128)=0)</p>
<p>
Statistics<br>
----------------------------------------------------------<br>
0 recursive calls<br>
0 db block gets<br>
876 consistent gets<br>
0 physical reads<br>
0 redo size<br>
92582 bytes sent via SQL*Net to client<br>
1339 bytes received via SQL*Net from client<br>
79 SQL*Net roundtrips to/from client<br>
1 sorts (memory)<br>
0 sorts (disk)<br>
1162 rows processed</p>
<p>
select /*+ rule */* from dba_indexes;</p>
<p>
1162 rows selected.</p>
<p>
Elapsed: 00:00:00.55</p>
<p>
Execution Plan<br>
----------------------------------------------------------<br>
Plan hash value: 2107813288</p>
<p>
--------------------------------------------------------------<br>
| Id | Operation | Name |<br>
--------------------------------------------------------------<br>
| 0 | SELECT STATEMENT | |<br>
| 1 | NESTED LOOPS OUTER | |<br>
| 2 | NESTED LOOPS OUTER | |<br>
| 3 | NESTED LOOPS | |<br>
| 4 | NESTED LOOPS | |<br>
| 5 | NESTED LOOPS OUTER | |<br>
| 6 | NESTED LOOPS | |<br>
| 7 | NESTED LOOPS OUTER | |<br>
| 8 | NESTED LOOPS | |<br>
|* 9 | TABLE ACCESS FULL | OBJ$ |<br>
|* 10 | TABLE ACCESS BY INDEX ROWID| IND$ |<br>
|* 11 | INDEX UNIQUE SCAN | I_IND1 |<br>
| 12 | TABLE ACCESS BY INDEX ROWID | OBJ$ |<br>
|* 13 | INDEX UNIQUE SCAN | I_OBJ1 |<br>
| 14 | TABLE ACCESS BY INDEX ROWID | OBJ$ |<br>
|* 15 | INDEX UNIQUE SCAN | I_OBJ1 |<br>
| 16 | TABLE ACCESS CLUSTER | USER$ |<br>
|* 17 | INDEX UNIQUE SCAN | I_USER# |<br>
| 18 | TABLE ACCESS CLUSTER | USER$ |<br>
|* 19 | INDEX UNIQUE SCAN | I_USER# |<br>
| 20 | TABLE ACCESS CLUSTER | USER$ |<br>
|* 21 | INDEX UNIQUE SCAN | I_USER# |<br>
| 22 | TABLE ACCESS CLUSTER | SEG$ |<br>
|* 23 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# |<br>
| 24 | TABLE ACCESS CLUSTER | TS$ |<br>
|* 25 | INDEX UNIQUE SCAN | I_TS# |<br>
--------------------------------------------------------------</p>
<p>
Predicate Information (identified by operation id):<br>
---------------------------------------------------</p>
<p>
9 - filter(BITAND("O"."FLAGS",128)=0)<br>
10 - filter(BITAND("I"."FLAGS",4096)=0)<br>
11 - access("O"."OBJ#"="I"."OBJ#")<br>
13 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))<br>
15 - access("I"."BO#"="IO"."OBJ#")<br>
17 - access("ITO"."OWNER#"="ITU"."USER#"(+))<br>
19 - access("U"."USER#"="O"."OWNER#")<br>
21 - access("IO"."OWNER#"="IU"."USER#")<br>
23 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND<br>
"I"."BLOCK#"="S"."BLOCK#"(+))<br>
25 - access("I"."TS#"="TS"."TS#"(+))</p>
<p>
Note<br>
-----<br>
- rule based optimizer used (consider using cbo)</p>
<p>
Statistics<br>
----------------------------------------------------------<br>
0 recursive calls<br>
0 db block gets<br>
25254 consistent gets<br>
26 physical reads<br>
0 redo size<br>
93977 bytes sent via SQL*Net to client<br>
1339 bytes received via SQL*Net from client<br>
79 SQL*Net roundtrips to/from client<br>
0 sorts (memory)<br>
0 sorts (disk)<br>
1162 rows processed</p>
頁:
[1]