dba_indexes视图的性能分析
程序员文章站
2022-11-21 11:56:41
select case when status='unusable' then
'alter index '||owner||'.'||index_nam...
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