Oracle中擷取執行計畫的幾種方法分析

以下是對Oracle中擷取執行計畫的幾種方法進行了詳細的分析介紹，需要的朋友可以參考下 

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

1）當前的環境可能和執行計畫產生時的環境不同；
2）不會考慮綁定變數的資料類型；
3）不進行變數窺視。

2. 查詢記憶體中緩衝的執行計畫 （dbms_xplan.display_cursor）
如果你想擷取正在執行的或剛執行結束的SQL語句真實的執行計畫（即擷取library cache中的執行計畫），可以到動態效能檢視裡查詢。方法如下：

1）擷取SQL語句的遊標
遊標分為父遊標和子遊標，父遊標由sql_id(或聯合address和hash_value)欄位表示，子遊標由child_number欄位表示。

如果SQL語句正在運行，可以從v$session中獲得它的遊標資訊，如：
select status, sql_id, sql_child_number from v$session where status='ACTIVE' and ....

如果知道SQL語句包含某些關鍵字，可以從v$sql視圖中獲得它的遊標資訊，如：
select sql_id, child_number, sql_text from v$sql where sql_text like '%關鍵字%‘

2）擷取庫緩衝中的執行計畫
為了擷取緩衝庫中的執行計畫，可以直接查詢動態效能檢視v$sql_plan和v$sql_plan_statistics_all等，但更方便的方法是以sql_id和子遊標為參數，執行如下語句：
select * from table(dbms_xplan.display_cursor('sql_id',child_number));

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

3. 查詢曆史執行計畫（dbms_xplan.display_awr）
AWR會定時把動態效能檢視中的執行計畫儲存到dba_hist_sql_plan視圖中，如果你想要查看曆史執行計畫，可以採用如下方法查詢：
select * from table(dbms_xplan.display_awr('sql_id');

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

SET AUTOTRACE OFF ---------------- 不顯示執行計畫和統計資訊，這是預設模式
SET AUTOTRACE ON EXPLAIN ------ 只顯示最佳化器執行計畫
SET AUTOTRACE ON STATISTICS -- 只顯示統計資訊
SET AUTOTRACE ON ----------------- 執行計畫和統計資訊同時顯示
SET AUTOTRACE TRACEONLY ------ 不真正執行，只顯示預期的執行計畫，同explain plan

5. 產生Trace檔案查詢詳細的執行計畫 （SQL_Trace, 10046）
SQL_TRACE 作為初始化參數可以在執行個體層級啟用，也可以只在會話層級啟用，在執行個體層級啟用SQL_TRACE會導致所有進程的活動被跟蹤，包括後台進程及所有使用者進 程，這通常會導致比較嚴重的效能問題，所以在一般情況下，我們使用sql_trace跟蹤當前進程，方法如下：

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

產生trace檔案後，再用tkprof 工具將sql trace 產生的追蹤檔案轉換成易讀的格式，文法如下：
tkprof inputfile outputfile
10046事件是SQL_TRACE的一個升級版，它也是追蹤會話，產生Trace檔案，只是它裡面的內容更詳細，