Oracle中獲取執(zhí)行計劃的幾種方法分析

1. 預(yù)估執(zhí)行計劃 - Explain Plan
Explain plan以SQL語句作為輸入，得到這條SQL語句的執(zhí)行計劃，并將執(zhí)行計劃輸出存儲到計劃表中。
首先，在你要執(zhí)行的SQL語句前加explain plan for，此時將生成的執(zhí)行計劃存儲到計劃表中，語句如下：
explain plan for SQL語句
然后，在計劃表中查詢剛剛生成的執(zhí)行計劃，語句如下：
select * from table(dbms_xplan.display);
注意：Explain plan只生成執(zhí)行計劃，并不會真正執(zhí)行SQL語句，因此產(chǎn)生的執(zhí)行計劃有可能不準(zhǔn)，因為：

1）當(dāng)前的環(huán)境可能和執(zhí)行計劃生成時的環(huán)境不同；
2）不會考慮綁定變量的數(shù)據(jù)類型；
3）不進(jìn)行變量窺視。

2. 查詢內(nèi)存中緩存的執(zhí)行計劃 （dbms_xplan.display_cursor）
如果你想獲取正在執(zhí)行的或剛執(zhí)行結(jié)束的SQL語句真實的執(zhí)行計劃（即獲取library cache中的執(zhí)行計劃），可以到動態(tài)性能視圖里查詢。方法如下：

1）獲取SQL語句的游標(biāo)
游標(biāo)分為父游標(biāo)和子游標(biāo)，父游標(biāo)由sql_id(或聯(lián)合address和hash_value)字段表示，子游標(biāo)由child_number字段表示。

如果SQL語句正在運行，可以從v$session中獲得它的游標(biāo)信息，如：
select status, sql_id, sql_child_number from v$session where status='ACTIVE' and ....

如果知道SQL語句包含某些關(guān)鍵字，可以從v$sql視圖中獲得它的游標(biāo)信息，如：
select sql_id, child_number, sql_text from v$sql where sql_text like '%關(guān)鍵字%‘

2）獲取庫緩存中的執(zhí)行計劃
為了獲取緩存庫中的執(zhí)行計劃，可以直接查詢動態(tài)性能視圖v$sql_plan和v$sql_plan_statistics_all等，但更方便的方法是以sql_id和子游標(biāo)為參數(shù)，執(zhí)行如下語句：
select * from table(dbms_xplan.display_cursor('sql_id',child_number));

3）獲取前一次的執(zhí)行計劃：
set serveroutput off
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

3. 查詢歷史執(zhí)行計劃（dbms_xplan.display_awr）
AWR會定時把動態(tài)性能視圖中的執(zhí)行計劃保存到dba_hist_sql_plan視圖中，如果你想要查看歷史執(zhí)行計劃，可以采用如下方法查詢：
select * from table(dbms_xplan.display_awr('sql_id');

4. 在用sqlplus做SQL開發(fā)是（Autotrace）
set autotrace是sqlplus工具的一個功能，只能在通過sqlplus連接的session中使用，它非常適合在開發(fā)時測試SQL語句的性能，有以下幾種參數(shù)可供選擇：

SET AUTOTRACE OFF ---------------- 不顯示執(zhí)行計劃和統(tǒng)計信息，這是缺省模式
SET AUTOTRACE ON EXPLAIN ------ 只顯示優(yōu)化器執(zhí)行計劃
SET AUTOTRACE ON STATISTICS -- 只顯示統(tǒng)計信息
SET AUTOTRACE ON ----------------- 執(zhí)行計劃和統(tǒng)計信息同時顯示
SET AUTOTRACE TRACEONLY ------ 不真正執(zhí)行，只顯示預(yù)期的執(zhí)行計劃，同explain plan

5. 生成Trace文件查詢詳細(xì)的執(zhí)行計劃 （SQL_Trace, 10046）
SQL_TRACE作為初始化參數(shù)可以在實例級別啟用，也可以只在會話級別啟用，在實例級別啟用SQL_TRACE會導(dǎo)致所有進(jìn)程的活動被跟蹤，包括后臺進(jìn)程及所有用戶進(jìn)程，這通常會導(dǎo)致比較嚴(yán)重的性能問題，所以在一般情況下，我們使用sql_trace跟蹤當(dāng)前進(jìn)程，方法如下：

SQL>alter session set sql_trace=true;
...被跟蹤的SQL語句...
SQL>alter session set sql_trace=false;
如果要跟蹤其它進(jìn)程，可以通過Oracle提供的系統(tǒng)包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION來實現(xiàn)，例如：
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true) --開始跟蹤
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false) --結(jié)束跟蹤

生成trace文件后，再用tkprof 工具將sql trace 生成的跟蹤文件轉(zhuǎn)換成易讀的格式，語法如下：
tkprof inputfile outputfile
10046事件是SQL_TRACE的一個升級版，它也是追蹤會話，生成Trace文件，只是它里面的內(nèi)容更詳細(xì)，