連結：http://blog.itpub.net/28602568/viewspace-1771776/

估算---&gt plan_table[$]中讀取

1）Toad、PL/SQL Developer工具

2）explain plan for select 1 from dual; --&gt SELECT * FROM plan_table/*或plan_table$表*/; /*plan_table和plan_table$區別*/

3）SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

4) 10046、10053....事件

真實---&gt 從記憶體v$..檢視得到的執行計劃是真實的

1)select * from table(dbms_xplan.display_cursor('a3s3279q7xaxs',1,'advanced')); --&gt預設child_number=0,advanced選項帶有高階執行計劃hint...,

select * from table(dbms_xplan.display_awr('sql_id')); --&gt AWR

2)如下sql可實現透過查詢v$sql_plan真實執行計劃來獲取類似plsql F5快捷鍵格式的資訊。

select lpad(' ',
(case
when p.DEPTH = 0 or DEPTH is null then
1
else
DEPTH
end),
' ') || p.OPERATION || ' ' || options || ' ' || p.OPTIMIZER description,
p.OBJECT_OWNER,
object_name,
object_type,
cost,
p.CARDINALITY,
p.BYTES,
DEPTH
from v$sql_plan p
where p.SQL_ID = 'a3s3279q7xaxs'
and child_number = 1
order by p.id;

顯示如下：

description	OBJECT_OWNER	OBJECT_NAME	object_type	cost	CARDINALITY	BYTES	DEPTH
MERGE STATEMENT ALL_ROWS				528			0
MERGE	FWSB	CS_ORDER_BATCH_CHG_ACCE					1
VIEW							2
NESTED LOOPS							3
NESTED LOOPS				528	1	297	4
VIEW				526	1	226	5
SORT ORDER BY				526	1	107	6
WINDOW SORT PUSHED RANK				526	1	107	7
NESTED LOOPS							8
NESTED LOOPS				524	1	107	9
HASH JOIN				276	124	11532	10
NESTED LOOPS							11
NESTED LOOPS				223	78	4992	12
SORT UNIQUE				52	19	323	13
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	52	19	323	14
INDEX RANGE SCAN	FWSB	IDX_COBCA_OPERATOR	INDEX	11	986		15
INDEX RANGE SCAN	FWSB	INDEX_ORDER_ID	INDEX	2	23		13
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_ITEM	TABLE	17	4	188	12
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	52	158	4582	11
INDEX RANGE SCAN	FWSB	IDX_COBCA_OPERATOR	INDEX	11	986		12
INDEX UNIQUE SCAN	FWSB	PK_CS_ORDER_BATCH_CHG_ACCE	INDEX (UNIQUE)	1	1		10
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	2	1	14	9
INDEX UNIQUE SCAN	FWSB	PK_CS_ORDER_BATCH_CHG_ACCE	INDEX (UNIQUE)	1	1		5
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	2	1	71	4

具體資料介紹可參考

[Oracle] 獲取執行計劃的幾種方法

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在 SQL、SQL最佳化篇分類目錄。將固定連線加入收藏夾。

Oracle 獲取執行計劃的幾種方法

[Oracle] 獲取執行計劃的幾種方法

相關文章