Oracle如何查看執行計劃

一、如何查看執行計劃

在Oracle數據庫里，我們通常可以使用如下方法(包括但不限于)得到目標SQL的執行計劃：

1. explain plan 命令

2. DBMS_XPLAN包

3. SQLPLUS中的AUTOTRACE開關

4. 10046事件

5. 10053事件

6. AWR報告或Statspack報告

7. 一些現成的腳本(如     display_cursor_9i.sql等)

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

1.1 explain plan 命令

      習慣使用PL/SQL Developer的人都知道，按下快捷鍵F5后就可以顯示目標SQL的執行計劃，實際上，PL/SQL Developer就調用了explain plan命令，快捷鍵F5只不過是在explain plan命令上的一層封裝而已。

      explain plan命令的語法是依次執行如下兩條命令：

• explain plan for +     目標SQL

• select * from     table(dbms_xplan.display)

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

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 及其以上的版本里，如果我們對目標SQL執行explain plan 命令，則Oracle就將解析目標SQL所產生的執行計劃的具體執行步驟寫入PLAN_TABLE$，隨后執行“select * from table(dbms_xplan.display)”只是從PLAN_TABLE$中將這些具體執行步驟以格式化的方式顯示出來。PLAN_TABLES$是一個ON COMMIT PRESERVE ROWS的GLOBALTEMPORARY TABLE，所以這里Oracle可以做到各個的Session只能看到自己執行的SQL所產生的執行計劃，并且各個Session往PLAN_TABLE$寫入執行計劃的過程互不干擾。

1.2 DBMS_XPLAN包

使用DBMS_XPLAN包中的方法是在Oracle數據庫中得到目標SQL的執行計劃的第二種方法。針對不同的應用場景，你可以選擇如下四種方法中的一種：

1. select * from     table(dbms_xplan.display);

2. select * from     table(dbms_xplan.display_cursor(null,null,'advanced'));

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

4. select * from     table(dbms_xplan.display_awr('sql_id'));

方法1是需要與explain plan命令配合使用，上節已經介紹過。

方法2用于在SQLPLUS中查看剛剛執行過的SQL的執行計劃。這里針對方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個和第二個參數的值均為null，第三個參數值是“advanced”，第三個輸入參數的值也可以是“all”，只不過用“advanced”后的顯示結果會比“all”顯示的結果更詳細一些。

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”得到的結果與“advanced”的顯示結果相比，少了“Outline Data”部分的內容。

方法3用于查看指定SQL的執行計劃。這里針對方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個參數的值是指定SQL的SQL ID或者SQL HASH VALUE，第二個參數的值是要查看的執行計劃所在的Chile Cursor Number,第三個參數已經在介紹方法2時已經提到過，這個參數值一般都用“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，對應的ChildCursor Number為0。

本質上SQL ID和SQL HASH_VALUE是一回事，它們是可以互相轉換的，這也是方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個參數的值可以是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

從上述結果可以看到，目標SQL的SQL ID(3yfu3wh250aqt)經過運算后得到的值就是該SQL的SQL HASH VALUE(38808281)。

只要目標SQL的執行計劃所在的Child Cursor還沒有被age out出Shard Pool，就可以使用方法3查看該SQL的執行計劃

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

方法4 用于查看指定SQL的所有歷史執行計劃。

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

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

1.3 AUTOTRACE開關

      在SQLPLUS中將AUTOTRACE開關打開也能得到目標SQL的執行計劃，而且，除此之外還能得到目標SQL在執行時的資源消耗量，即通過設置AUTOTRACE開關我們可以額外觀察到目標SQL執行時所耗費的物理讀、邏輯讀、產生redo的數量以及排序的數量等。

      在SQLPLUS中設置AUTOTRACE開關的語法如下：

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

[EXPLAIN][STATISTICS]

1. 在SQLPLUS的當前Session中執行命令SET AUTOTRACE ON，可以在當前Session中將AUTOTRACE開關完全打開。這樣，在這個Session隨后執行的所有SQL除了顯示SQL執行結果外，還會額外顯示這些SQL所對應的執行計劃和資源消耗情況。

2. 在SQLPLUS的當前Session中執行命令SET AUTOTRACE OFF，可以在當前Session中將AUTOTRACE開關關閉，這樣，在這個Session中隨后執行的所有SQL都只會顯示SQL執行結果，AUTOTRACE開關的默認值就是OFF。

3. 在SQLPLUS的當前Session中執行命令SET AUTOTRACE     TRACEONLY，可以在當前Session中將AUTOTRACE開關以不顯示SQL執行結果的具體內容的方式完全打開。這種方式與SET AUTOTRACE ON的唯一區別在于TRACEONLY只顯示SQL執行結果的數量，而不會顯示執行結果的具體內容。適用于SQL執行結果的具體內容特別長，會連續刷屏的SQL，這種情況下我們往往并不關心這些SQL的執行結果的具體內容，而只是關心它們的執行計劃和資源消耗量。

4. 在SQLPLUS的當前Session中執行命令SET AUTOTRACE     TRACEONLY EXPLAIN，可以在當前Session中將AUTOTRACE開關以只顯示SQL執行計劃的方式打開。這種方式與TRACEONLY的區別在于TRACEONLY EXPLAIN不會顯示目標SQL的資源消耗量和執行結果，而只會顯示目標SQL的執行計劃。

5. 在SQLPLUS的當前Session中執行命令SET AUTOTRACE     TRACEONLY STATISTICS，可以在當前Session中將AUTOTRACE開關以只顯示SQL的資源消耗量的方式打開，與TRACEONLY的唯一區別在于TRACEONLY STATISTICS不顯示目標SQL的執行計劃，而只會顯示目標SQL執行結果的數據和資源消耗量。

      設置AUTOTRACE開關的相關命令也沒用了Oracle一貫的可以使用簡寫的慣例：

• 關鍵字AUTOTRACE可以用簡寫AUTOT來代替

• 關鍵字TRACEONLY可以用簡寫TRACE來代替

• 關鍵字EXPLAIN可以用簡寫EXP來代替

• 關鍵字STATISTICS可以用簡寫STAT來代替

SET AUTOTRACE ON

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

SET AUTOTRACE TRACEONLY

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數據庫中查看目標SQL的執行計劃的另一種方法。這種方法與使用explain plan命令、DBMS_XPLAN包和AUTOTRACE開關的不同之處在于，所得到的執行計劃中明確顯示了目標SQL實際執行計劃中每一個執行步驟所消耗的邏輯讀、物理讀和花費的時間。這種細粒度的明細顯示在我們診斷復雜SQL的性能問題時尤為有用，而且這也是其他三種方法所不能提供的(實際上，用GATHER_PLAN_STATISTICS Hint配合DBMS_XPLN包一起使用可以達到類似10046事件這種細粒度的明細顯示效果)。

      用10046事件得到目標SQL的執行計劃是很容易的，只需要依次執行如下三個步驟：

• 首先在當前Session中激活10046事件；

• 接著在此Session中執行目標SQL；

• 最后在此Session中關閉10046事件。

      當執行完上述步驟后，Oracle就會將目標SQL的執行計劃和明細資源消耗寫入此Session所對應的trace文件中，查看這個trace文件就能知道目標SQL的執行計劃和資源消耗明細了。Oracle會在參數USER_DUMP_DEST所代表的目標下生成這個trace文件，其命名格式為“實例名_ora_當前Session的spid.trc”，例如orcl_ora_86541.trc。

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

• 在當前Session中執行alter session set     events '10046 trace name context forever ,level 12'

• 在當前Session中執行oradebug event     10046 trace name context forever,level 12

      上述命令中的關鍵字“level”后的數字是表示設置的10046事件的level值。這個值是可以修改的，我們通常使用的值為12，表示產生的trace文件中除了目標SQL的執行計劃和資源消耗明細之外，還會包含目標SQL所使用的綁定變量的值以及該Session所經歷的等待事件。除了上述level值之外，其他部分是固定語法，我們無法修改。使用第2種方法，在激活10046事件后執行命令oradebugtracefile_name來得到當前Session所對應的trace文件的具體路徑和名稱。

      對應的，在當前Session中關閉10046事件的兩種方法：

• 在當前Session中執行alter session set     events '10046 trace name context off'

• 在當前Session中執行oradebug event     10046 trace name context off

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

     關于10046事件可以參考之前的博客：

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

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

參考：《基于Oracle的SQL優化》