dba_indexes視圖的性能分析
更新時(shí)間：2008年08月16日 21:10:06 作者：
前端時(shí)間，在監(jiān)控系統(tǒng)中加入了index的狀態(tài)是否為unusable，以及其并行度是否有設(shè)置的監(jiān)控：
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');
語(yǔ)句運(yùn)行的速度很快，但是從statspack中發(fā)現(xiàn)這條語(yǔ)句的邏輯讀單次高達(dá)26846。使用set autotrace比較了下9i和10g的執(zhí)行計(jì)劃和統(tǒng)計(jì)信息，發(fā)現(xiàn)9i查詢這個(gè)視圖的代價(jià)非常的高，而10g則有了一定的改善。在Oracle9i中，optimizer_mode默認(rèn)是CHOOSE，所以查詢數(shù)據(jù)字典使用了RBO，而Oracle10g則默認(rèn)為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
相關(guān)文章
你也許連刪庫(kù)跑路都不會(huì)（delete、drop和truncate刪除數(shù)據(jù)）
這篇文章主要給大家介紹了關(guān)于delete、drop和truncate刪除數(shù)據(jù)的方式，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
Nebula?Graph解決風(fēng)控業(yè)務(wù)實(shí)踐
本文主要講述?Nebula?Graph?是如何通過(guò)眾安保險(xiǎn)的選型，以及?Nebula?Graph?又是如何落地到具體業(yè)務(wù)場(chǎng)景幫助眾安保險(xiǎn)解決風(fēng)控問(wèn)題，有需要的朋友可以借鑒參考下2022-03-03
如何使用navicat遠(yuǎn)程連接openGauss
公司要在openEuler系統(tǒng)中安裝openGauss數(shù)據(jù)庫(kù),經(jīng)過(guò)幾天的查資料,終于是安裝成功了,并且能在navicat中遠(yuǎn)程連接使用,本文給大家介紹如何使用navicat遠(yuǎn)程連接openGauss,感興趣的朋友跟隨小編一起看看吧2023-10-10
非安裝版的PostgreSQL8.3.3的首次使用經(jīng)歷第一次用Postgre，已經(jīng)是好多年以前了，隱約記得是在linux下邊，build半天，然后手動(dòng)配置庫(kù)文件之類。2008-09-09
TDSQL 安裝部署附圖的實(shí)現(xiàn)(圖文)
這篇文章主要介紹了TDSQL 安裝部署附圖的實(shí)現(xiàn)(圖文)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-10-10
這篇文章主要介紹了數(shù)據(jù)計(jì)算中間件技術(shù)綜述 ，傳統(tǒng)企業(yè)大數(shù)據(jù)架構(gòu)的問(wèn)題，通過(guò)一張圖就能看懂，感興趣的朋友跟隨小編一起通過(guò)本文學(xué)習(xí)吧2018-11-11
數(shù)據(jù)庫(kù)學(xué)習(xí)建議之提高數(shù)據(jù)庫(kù)速度的十條建議
很多網(wǎng)站的重要信息都是保存在數(shù)據(jù)庫(kù)中，用戶通過(guò)提交訪問(wèn)數(shù)據(jù)庫(kù)來(lái)獲取用戶信息。如果數(shù)據(jù)庫(kù)速度非常的快，有助于節(jié)省服務(wù)器的資源，那么如何優(yōu)化數(shù)據(jù)庫(kù)的速度呢，下面通過(guò)此篇文章一起學(xué)習(xí)數(shù)據(jù)庫(kù)學(xué)習(xí)建議之提高數(shù)據(jù)庫(kù)速度的十條建議2015-11-11
關(guān)于Navicat連接MySql數(shù)據(jù)庫(kù)慢的問(wèn)題
這篇文章主要介紹了關(guān)于Navicat連接MySql數(shù)據(jù)庫(kù)慢的問(wèn)題,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
Lakehouse數(shù)據(jù)湖并發(fā)控制陷阱分析
這篇文章主要為大家介紹了Lakehouse數(shù)據(jù)湖并發(fā)控制陷阱分析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪2022-03-03
前端時(shí)間，在監(jiān)控系統(tǒng)中加入了index的狀態(tài)是否為unusable，以及其并行度是否有設(shè)置的監(jiān)控：2008-08-08