前文:
執(zhí)行計(jì)劃可以用來分析SQL的執(zhí)行情況,在Oracle中有著多種獲取執(zhí)行計(jì)劃的方法,每種方法各有優(yōu)劣,因此本文對(duì)執(zhí)行計(jì)劃的獲取方法、優(yōu)劣以及使用場(chǎng)景進(jìn)行一個(gè)總結(jié)。
正文:
(1).explain plan for
select * from t1,t2
where t1.id=t2.id
and t1.id in(5,6);
select * from table(dbms_xplan.display());

優(yōu)點(diǎn):
無需真正執(zhí)行,快捷方便
缺點(diǎn):
1.沒有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息(產(chǎn)生多少邏輯讀,多少次物理讀,多少次遞歸調(diào)用等);
2.無法判斷是處理了多少行;
3.無法判斷表被訪問了多少次。
使用場(chǎng)景:
跟蹤某條SQL最簡(jiǎn)單的方法;
如果某SQL執(zhí)行時(shí)間很長(zhǎng)才能出結(jié)果,或無返回結(jié)果,這時(shí)使用此方法;
如果想要確保看到真實(shí)的執(zhí)行計(jì)劃,不能用此方法;
(2).set autotrace on

優(yōu)點(diǎn):
1.可以輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息(產(chǎn)生多少邏輯讀,多少次物理讀,多少次遞歸調(diào)用等);
2.雖然必須要等語句執(zhí)行完畢后才可以輸出執(zhí)行計(jì)劃,但可以使用traceonly控制返回結(jié)果不打印。
缺點(diǎn):
1.必須要等語句真正執(zhí)行完畢后,才可以輸出結(jié)果;
2.無法看到表被訪問了多少次。
使用場(chǎng)景:
跟蹤某條SQL最簡(jiǎn)單的方法;
如果想要確保看到真實(shí)的執(zhí)行計(jì)劃,不能用此方法;
(3).statistics_level=all;
alter session set statistics_level=all;
執(zhí)行SQL
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

優(yōu)點(diǎn):
1.可以清晰的從STARTS得出表被訪問多少次;
2.可以清晰的從E_ROWS和A-ROWS中得到預(yù)測(cè)的行數(shù)和真實(shí)的行數(shù),從而可以準(zhǔn)確的判斷Oracle評(píng)估是否準(zhǔn)確;
3.雖然沒有專門的輸出統(tǒng)計(jì)信息,但是執(zhí)行計(jì)劃中的BUFFERS就是真實(shí)的邏輯讀次數(shù),Reads表示物理讀(本次SQL執(zhí)行并未發(fā)生物理讀,因此沒有顯示)。
缺點(diǎn):
1.必須要等到語句真實(shí)執(zhí)行完畢后才可以輸出結(jié)果;
2.無法控制記錄打印輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息;
3.無法看出遞歸調(diào)用的次數(shù)。
使用場(chǎng)景:
想要獲取表被訪問的次數(shù),只能用此方法;
(4).通過dbms_xplan.display_cursor輸入sql_id參數(shù)直接獲取
從共享池獲得sql_id
select sql_id,sql_text from v$sql where sql_text like '%t1,t2%';
--或者通過其他方式獲取到sql_id(比如awr)
select * from table(dbms_xplan.display_cursor('&sq_id'));

優(yōu)點(diǎn):
1.知道sql_id立即可得到執(zhí)行計(jì)劃,和explain plan for 一樣無需執(zhí)行;
2.可以得到準(zhǔn)確的執(zhí)行計(jì)劃
缺點(diǎn):
1.沒有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息(產(chǎn)生多少邏輯讀,多少次物理讀,多少次遞歸調(diào)用等);
2.無法判斷處理了多少行;
3.無法判斷表被訪問了多少次。
使用場(chǎng)景:
觀察某條SQL有多條執(zhí)行計(jì)劃的情況,這時(shí)使用該方法;
如果某SQL執(zhí)行時(shí)間很長(zhǎng)才能出結(jié)果,可以使用此方法直接調(diào)用;
(5).10046 trace跟蹤
alter session set events '10046 trace name context forever,level 12';(開啟跟蹤)
執(zhí)行SQL
alter session set events '10046 trace name context off'; (關(guān)閉跟蹤)
到$ORACLE_BASE/diag/rdbms/orcl11g/orcl11g/trace目錄下找trace文件,
或者通過該SQL查詢當(dāng)前session的trace
select?tracefile?from?v$process?where?addr?in?(select?paddr?from?v$session?where?sid?in?(select?sid?from?v$mystat));
tkprof prod_ora_69722.trc output=/home/oracle/20220419_prod_t1t2.txt sys=no waits=yes explain=fr/** (格式化命令)
level 1:SQL 語句,執(zhí)行計(jì)劃和執(zhí)行狀態(tài)
level 4:(level 1)的內(nèi)容加上綁定變量信息
level 8:(level 1)的信息加上等待事件信息
level 12:(level 1)+(level 4)+(level 8)

優(yōu)點(diǎn):
1.可以看出SQL語句對(duì)應(yīng)的等待事件;
2.如果SQL語句中有函數(shù)調(diào)用,SQL中有SQL,都會(huì)被列出來;
3.可以方便的看出處理的行數(shù),產(chǎn)生的物理邏輯讀;
4.可以方便的看出解析時(shí)間和執(zhí)行時(shí)間;
5.可以跟蹤整個(gè)程序包。
缺點(diǎn):
1.步驟繁瑣,比較麻煩;
2.無法判斷表被訪問了多少次;
3.執(zhí)行計(jì)劃中的謂詞部分不能清晰的展現(xiàn)出來。
使用場(chǎng)景:
如果SQL中含有函數(shù),函數(shù)中套有SQL等多層調(diào)用,想準(zhǔn)確分析只能使用此方法;
如果想要查看SQL產(chǎn)生的等待事件,需要使用此方法
(6).awrsqrpt.sql
在sqlplus中執(zhí)行@?/rdbms/admin/awrsqrpt.sql,按照提示,輸入相關(guān)信息即可獲得報(bào)告。


優(yōu)點(diǎn):
可以觀察具有多條執(zhí)行計(jì)劃的SQL,并且有詳細(xì)的執(zhí)行時(shí)間以及資源開銷
缺點(diǎn):
操作比較繁瑣,需要將報(bào)告導(dǎo)出查看,有些環(huán)境限制無法導(dǎo)出。
使用場(chǎng)景:
同一條SQL有多個(gè)執(zhí)行計(jì)劃時(shí),若進(jìn)行分析,可以使用該方法;另外如果SQL含有中文字符,在sqlplus中顯示亂碼,可以使用該方式生成html格式的報(bào)告,即可正常顯示中文字符。
以上就是Oracle獲取執(zhí)行計(jì)劃的六種方法,以及使用場(chǎng)景,若讀者有不同的想法,歡迎討論。另外,本文受啟發(fā)于粱敬彬老師所著的《收獲,不止SQL優(yōu)化》一書,該書對(duì)我在SQL優(yōu)化的理解上有著很多幫助,在此感謝粱敬彬老師。




