Oracle如何查看執(zhí)行計(jì)劃

一、如何查看執(zhí)行計(jì)劃

十余年建站經(jīng)驗(yàn)，網(wǎng)站建設(shè)、成都做網(wǎng)站客戶的見(jiàn)證與正確選擇。成都創(chuàng)新互聯(lián)提供完善的營(yíng)銷型網(wǎng)頁(yè)建站明細(xì)報(bào)價(jià)表。后期開(kāi)發(fā)更加便捷高效，我們致力于追求更美、更快、更規(guī)范。

在Oracle數(shù)據(jù)庫(kù)里，我們通?？梢允褂萌缦路椒?包括但不限于)得到目標(biāo)SQL的執(zhí)行計(jì)劃：

explain plan命令
DBMS_XPLAN包
SQLPLUS中的AUTOTRACE開(kāi)關(guān)
10046事件
10053事件
AWR報(bào)告或Statspack報(bào)告
一些現(xiàn)成的腳本(如 display_cursor_9i.sql等)

前四種方法使用得比較普遍

1.1 explain plan命令

習(xí)慣使用PL/SQL Developer的人都知道，按下快捷鍵F5后就可以顯示目標(biāo)SQL的執(zhí)行計(jì)劃，實(shí)際上，PL/SQL Developer就調(diào)用了explain plan命令，快捷鍵F5只不過(guò)是在explain plan命令上的一層封裝而已。

explain plan命令的語(yǔ)法是依次執(zhí)行如下兩條命令：

explain plan for + 目標(biāo)SQL
select * from table(dbms_xplan.display)

先使用explain plan命令對(duì)目標(biāo)SQL做explain，再使用“select * from table(dbms_xplan.display)”查看上述使用explain plan命令后得到的執(zhí)行計(jì)劃。

explain plan for select empno,ename,dname fromscott.emp,scott.dept where emp.deptno=dept.deptno;

select * from table(dbms_xplan.display);

explain plan命令到底做了什么事情呢？在Oracle 10g及其以上的版本里，如果我們對(duì)目標(biāo)SQL執(zhí)行explain plan命令，則Oracle就將解析目標(biāo)SQL所產(chǎn)生的執(zhí)行計(jì)劃的具體執(zhí)行步驟寫(xiě)入PLAN_TABLE$，隨后執(zhí)行“select * from table(dbms_xplan.display)”只是從PLAN_TABLE$中將這些具體執(zhí)行步驟以格式化的方式顯示出來(lái)。PLAN_TABLES$是一個(gè)ON COMMIT PRESERVE ROWS的GLOBALTEMPORARY TABLE，所以這里Oracle可以做到各個(gè)的Session只能看到自己執(zhí)行的SQL所產(chǎn)生的執(zhí)行計(jì)劃，并且各個(gè)Session往PLAN_TABLE$寫(xiě)入執(zhí)行計(jì)劃的過(guò)程互不干擾。

1.2 DBMS_XPLAN包

使用DBMS_XPLAN包中的方法是在Oracle數(shù)據(jù)庫(kù)中得到目標(biāo)SQL的執(zhí)行計(jì)劃的第二種方法。針對(duì)不同的應(yīng)用場(chǎng)景，你可以選擇如下四種方法中的一種：

select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));
select * from table(dbms_xplan.display_awr('sql_id'));

方法1是需要與explain plan命令配合使用，上節(jié)已經(jīng)介紹過(guò)。

方法2用于在SQLPLUS中查看剛剛執(zhí)行過(guò)的SQL的執(zhí)行計(jì)劃。這里針對(duì)方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個(gè)和第二個(gè)參數(shù)的值均為null，第三個(gè)參數(shù)值是“advanced”，第三個(gè)輸入?yún)?shù)的值也可以是“all”，只不過(guò)用“advanced”后的顯示結(jié)果會(huì)比“all”顯示的結(jié)果更詳細(xì)一些。

set linesize 800 pagesize 900

col plan_table_output for a200

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

select * fromtable(dbms_xplan.display_cursor(null,null,'advanced'));

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

select * fromtable(dbms_xplan.display_cursor(null,null,'all'));

“all”得到的結(jié)果與“advanced”的顯示結(jié)果相比，少了“Outline Data”部分的內(nèi)容。

方法3用于查看指定SQL的執(zhí)行計(jì)劃。這里針對(duì)方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個(gè)參數(shù)的值是指定SQL的SQL ID或者SQL HASH VALUE，第二個(gè)參數(shù)的值是要查看的執(zhí)行計(jì)劃所在的Chile Cursor Number,第三個(gè)參數(shù)已經(jīng)在介紹方法2時(shí)已經(jīng)提到過(guò)，這個(gè)參數(shù)值一般都用“advanced”。

selectsql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'selectempno,ename%';

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER

------------------------------------------------------------------------------- ------------- ---------- -------------

selectempno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno 3yfu3wh250aqt 38808281 0

sql_id為3yfu3wh250aqt，SQL HASH_VALUE為38808281，對(duì)應(yīng)的ChildCursor Number為0。

本質(zhì)上SQL ID和SQL HASH_VALUE是一回事，它們是可以互相轉(zhuǎn)換的，這也是方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個(gè)參數(shù)的值可以是SQL ID，也可以是SQLHASH_VALUE的原因。

selectlower(trim('3yfu3wh250aqt')) sql_id,

trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',

substr(lower(trim('3yfu3wh250aqt')),level,1))-1)*power(32,length(trim('3yfu3wh250aqt'))-level)),

power(2,32)))hash_value

fromdual

connectby level<=length(trim('3yfu3wh250aqt'));

SQL_ID HASH_VALUE

-------------------------

3yfu3wh250aqt 38808281

從上述結(jié)果可以看到，目標(biāo)SQL的SQL ID(3yfu3wh250aqt)經(jīng)過(guò)運(yùn)算后得到的值就是該SQL的SQL HASH VALUE(38808281)。

只要目標(biāo)SQL的執(zhí)行計(jì)劃所在的Child Cursor還沒(méi)有被age out出Shard Pool，就可以使用方法3查看該SQL的執(zhí)行計(jì)劃

select* from table(dbms_xplan.display_cursor('3yfu3wh250aqt',0,'advanced'));

方法4用于查看指定SQL的所有歷史執(zhí)行計(jì)劃。

使用方法2、3能夠顯示目標(biāo)SQL執(zhí)行計(jì)劃的前提條件是該SQL的執(zhí)行計(jì)劃還在Shared Pool中，而如果該SQL的執(zhí)行計(jì)劃已經(jīng)被age out出SharedPool，那么只要該SQL的執(zhí)行計(jì)劃被Oracle采集到AWR Repository中，就可以使用方法4來(lái)查看該SQL的所有歷史執(zhí)行計(jì)劃。

用DBMS_XPLAN.DISPLAY_AWR和之前用DBMS_XPLAN.DISPLAY_CURSOR顯示的執(zhí)行計(jì)劃相比，有一個(gè)非常不好的地方——就是用DISPLAY_AWR顯示的執(zhí)行計(jì)劃中看不到執(zhí)行步驟對(duì)應(yīng)的謂詞條件。根本的原因是Oracle在把執(zhí)行計(jì)劃的采樣數(shù)據(jù)從V$SQL_PLAN挪到AWR Repository的基表WRH$_SQL_PLAN中時(shí)沒(méi)有保留V$SQL_PLAN中記錄謂詞條件(包括驅(qū)動(dòng)查詢條件和過(guò)濾查詢條件)的列ACCESS_PREDICATES和FILTER_PREDICATES的值，所以不是DBMS_XPLAN.DISPLAY_CURSOR不想顯示謂詞條件，而是根本就同有謂詞條件可供顯示。

1.3 AUTOTRACE開(kāi)關(guān)

在SQLPLUS中將AUTOTRACE開(kāi)關(guān)打開(kāi)也能得到目標(biāo)SQL的執(zhí)行計(jì)劃，而且，除此之外還能得到目標(biāo)SQL在執(zhí)行時(shí)的資源消耗量，即通過(guò)設(shè)置AUTOTRACE開(kāi)關(guān)我們可以額外觀察到目標(biāo)SQL執(zhí)行時(shí)所耗費(fèi)的物理讀、邏輯讀、產(chǎn)生redo的數(shù)量以及排序的數(shù)量等。

在SQLPLUS中設(shè)置AUTOTRACE開(kāi)關(guān)的語(yǔ)法如下：

SET AUTOTRACE{OFF|ON|TRACE[ONLY]}

[EXPLAIN][STATISTICS]

在SQLPLUS的當(dāng)前Session中執(zhí)行命令SET AUTOTRACE ON，可以在當(dāng)前Session中將AUTOTRACE開(kāi)關(guān)完全打開(kāi)。這樣，在這個(gè)Session隨后執(zhí)行的所有SQL除了顯示SQL執(zhí)行結(jié)果外，還會(huì)額外顯示這些SQL所對(duì)應(yīng)的執(zhí)行計(jì)劃和資源消耗情況。
在SQLPLUS的當(dāng)前Session中執(zhí)行命令SET AUTOTRACE OFF，可以在當(dāng)前Session中將AUTOTRACE開(kāi)關(guān)關(guān)閉，這樣，在這個(gè)Session中隨后執(zhí)行的所有SQL都只會(huì)顯示SQL執(zhí)行結(jié)果，AUTOTRACE開(kāi)關(guān)的默認(rèn)值就是OFF。
在SQLPLUS的當(dāng)前Session中執(zhí)行命令SET AUTOTRACE TRACEONLY，可以在當(dāng)前Session中將AUTOTRACE開(kāi)關(guān)以不顯示SQL執(zhí)行結(jié)果的具體內(nèi)容的方式完全打開(kāi)。這種方式與SET AUTOTRACE ON的唯一區(qū)別在于TRACEONLY只顯示SQL執(zhí)行結(jié)果的數(shù)量，而不會(huì)顯示執(zhí)行結(jié)果的具體內(nèi)容。適用于SQL執(zhí)行結(jié)果的具體內(nèi)容特別長(zhǎng)，會(huì)連續(xù)刷屏的SQL，這種情況下我們往往并不關(guān)心這些SQL的執(zhí)行結(jié)果的具體內(nèi)容，而只是關(guān)心它們的執(zhí)行計(jì)劃和資源消耗量。
在SQLPLUS的當(dāng)前Session中執(zhí)行命令SET AUTOTRACE TRACEONLY EXPLAIN，可以在當(dāng)前Session中將AUTOTRACE開(kāi)關(guān)以只顯示SQL執(zhí)行計(jì)劃的方式打開(kāi)。這種方式與TRACEONLY的區(qū)別在于TRACEONLY EXPLAIN不會(huì)顯示目標(biāo)SQL的資源消耗量和執(zhí)行結(jié)果，而只會(huì)顯示目標(biāo)SQL的執(zhí)行計(jì)劃。
在SQLPLUS的當(dāng)前Session中執(zhí)行命令SET AUTOTRACE TRACEONLY STATISTICS，可以在當(dāng)前Session中將AUTOTRACE開(kāi)關(guān)以只顯示SQL的資源消耗量的方式打開(kāi)，與TRACEONLY的唯一區(qū)別在于TRACEONLY STATISTICS不顯示目標(biāo)SQL的執(zhí)行計(jì)劃，而只會(huì)顯示目標(biāo)SQL執(zhí)行結(jié)果的數(shù)據(jù)和資源消耗量。

設(shè)置AUTOTRACE開(kāi)關(guān)的相關(guān)命令也沒(méi)用了Oracle一貫的可以使用簡(jiǎn)寫(xiě)的慣例：

關(guān)鍵字AUTOTRACE可以用簡(jiǎn)寫(xiě)AUTOT來(lái)代替
關(guān)鍵字TRACEONLY可以用簡(jiǎn)寫(xiě)TRACE來(lái)代替
關(guān)鍵字EXPLAIN可以用簡(jiǎn)寫(xiě)EXP來(lái)代替
關(guān)鍵字STATISTICS可以用簡(jiǎn)寫(xiě)STAT來(lái)代替

SET AUTOTRACE ON

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

SET AUTOTRACETRACEONLY

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

SET AUTOTRACE OFF

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

1.4 10046事件與tkprof命令

使用10046事件是在Oracle數(shù)據(jù)庫(kù)中查看目標(biāo)SQL的執(zhí)行計(jì)劃的另一種方法。這種方法與使用explain plan命令、DBMS_XPLAN包和AUTOTRACE開(kāi)關(guān)的不同之處在于，所得到的執(zhí)行計(jì)劃中明確顯示了目標(biāo)SQL實(shí)際執(zhí)行計(jì)劃中每一個(gè)執(zhí)行步驟所消耗的邏輯讀、物理讀和花費(fèi)的時(shí)間。這種細(xì)粒度的明細(xì)顯示在我們?cè)\斷復(fù)雜SQL的性能問(wèn)題時(shí)尤為有用，而且這也是其他三種方法所不能提供的(實(shí)際上，用GATHER_PLAN_STATISTICS Hint配合DBMS_XPLN包一起使用可以達(dá)到類似10046事件這種細(xì)粒度的明細(xì)顯示效果)。

用10046事件得到目標(biāo)SQL的執(zhí)行計(jì)劃是很容易的，只需要依次執(zhí)行如下三個(gè)步驟：

首先在當(dāng)前Session中激活10046事件；
接著在此Session中執(zhí)行目標(biāo)SQL；
最后在此Session中關(guān)閉10046事件。

當(dāng)執(zhí)行完上述步驟后，Oracle就會(huì)將目標(biāo)SQL的執(zhí)行計(jì)劃和明細(xì)資源消耗寫(xiě)入此Session所對(duì)應(yīng)的trace文件中，查看這個(gè)trace文件就能知道目標(biāo)SQL的執(zhí)行計(jì)劃和資源消耗明細(xì)了。Oracle會(huì)在參數(shù)USER_DUMP_DEST所代表的目標(biāo)下生成這個(gè)trace文件，其命名格式為“實(shí)例名_ora_當(dāng)前Session的spid.trc”，例如orcl_ora_86541.trc。

通常可以使用如下兩種方法在當(dāng)前Session中激活10046事件：

在當(dāng)前Session中執(zhí)行alter session set events '10046 trace name context forever ,level 12'
在當(dāng)前Session中執(zhí)行oradebug event 10046 trace name context forever,level 12

上述命令中的關(guān)鍵字“l(fā)evel”后的數(shù)字是表示設(shè)置的10046事件的level值。這個(gè)值是可以修改的，我們通常使用的值為12，表示產(chǎn)生的trace文件中除了目標(biāo)SQL的執(zhí)行計(jì)劃和資源消耗明細(xì)之外，還會(huì)包含目標(biāo)SQL所使用的綁定變量的值以及該Session所經(jīng)歷的等待事件。除了上述level值之外，其他部分是固定語(yǔ)法，我們無(wú)法修改。使用第2種方法，在激活10046事件后執(zhí)行命令oradebugtracefile_name來(lái)得到當(dāng)前Session所對(duì)應(yīng)的trace文件的具體路徑和名稱。

對(duì)應(yīng)的，在當(dāng)前Session中關(guān)閉10046事件的兩種方法：

在當(dāng)前Session中執(zhí)行alter session set events '10046 trace name context off'
在當(dāng)前Session中執(zhí)行oradebug event 10046 trace name context off

需要注意的是10046事件所產(chǎn)生的原始trace文件習(xí)慣稱之為裸trace文件(raw trace)，Oracle記錄在裸trace文件中的內(nèi)容一眼看上去并不是那么觀，也不是那么容易看懂。為了祼trace文件能夠以一種更直觀、更容易懂的方式展現(xiàn)出來(lái)，Oracle提供了tkprof命令，這個(gè)命令是Oracle自帶的，可以用它來(lái)翻譯祼trace文件。

關(guān)于10046事件可以參考之前的博客：

http://hbxztc.blog.51cto.com/1587495/1898624

http://hbxztc.blog.51cto.com/1587495/1898753

參考：《基于Oracle的SQL優(yōu)化》

文章題目：Oracle如何查看執(zhí)行計(jì)劃
文章位置：http://muchs.cn/article18/ihihdp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供響應(yīng)式網(wǎng)站、網(wǎng)站排名、營(yíng)銷型網(wǎng)站建設(shè)、網(wǎng)站制作、標(biāo)簽優(yōu)化、網(wǎng)站營(yíng)銷

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容