Oracle獲取執行計劃方法

獲取執行計劃的6種方法

   1. explain plan for獲取；

   2. set autotrace on ；   

   3. statistics_level=all;

   4. 通過dbms_xplan.display_cursor輸入sql_id參數直接獲取

   5. 10046 trace跟蹤

6. awrsqrpt.sql

適用場合分析

   1.如果某SQL執行非常長時間才會出結果，甚至慢到返回不了結果，這時候看執行計劃就只能用方法1；

   2.跟蹤某條SQL最簡單的方法是方法1，其次就是方法2，方法2要執行；

   3.如果想觀察到某條SQL有多條執行計劃的情況，只能用方法4和方法6；

   4.如果SQL中含有多函數，函數中套有SQL等多層遞歸調用，想準確分析，只能使用方法5；

   5.要想確保看到真實的執行計劃，不能用方法1和方法2；

6.要想獲取表被訪問的次數，只能使用方法3；

explain plan for

  步驟1：explain plan for "你的SQL"

  步驟2：select * from table(dbms_xplan.display());

SQL> explain plan for

 2  SELECT  *

 3  FROM t1, t2

 4  WHERE t1.id = t2.t1_id

 5  AND t1.n in(18,19);

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

Explained.

優點

1. 無需真正執行，快捷方便

缺點

1.沒有輸出運行時的相關統計信息（產生多少邏輯讀，多少次遞歸調用，多少次物理讀的情況）；

2.無法判斷是處理了多少行；

3.無法判斷表被訪問了多少次。

set autotarce on

步驟1：set autotrace on

步驟2：在此處執行你的SQL即可，后續自然會有結果輸出

SQL> set autotrace on

SQL> SELECT  *

 2  FROM t1, t2

 3  WHERE t1.id = t2.t1_id

 4  AND t1.n in(18,19);

set autotrace on                 (得到執行計劃，輸出運行結果）

set autotrace traceonly          (得到執行計劃，不輸出運行結果）

set autotrace traceonly explain  (得到執行計劃，不輸出運行結果和統計信息部分，僅展現執行計劃部分）

set autotrace traceonl statistics(不輸出運行結果和執行計劃部分，僅展現統計信息部分）

優點

1.可以輸出運行時的相關統計信息（產生多少邏輯讀，多少次遞歸調用，多少次物理讀的情況）；

2.雖然必須要等語句執行完畢后才可以輸出執行計劃，但是可以有traceonly開關來控制返回結果不打屏輸出。

缺點

1.必須要等到語句真正執行完畢后，才可以出結果；

2.無法看到表被訪問了多少次。

statistics level=all

步驟1：alter session set statistics_level=all ;

步驟2：在此處執行你的SQL

步驟3：select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

1. 如果你用 /*+gather_plan_statistics */的方法，可以省略步驟1，直接步驟2,3。

2. 關鍵字解讀：

   Starts為該sql執行的次數。

   E-Rows為執行計劃預計的行數。

   A-Rows為實際返回的行數。A-Rows跟E-Rows做比較，就可以確定哪一步執行計劃出了問題。

   A-Time為每一步實際執行的時間（HH：MM：SS.FF），根據這一行可以知道該sql耗時在了哪個地方。

   Buffers為每一步實際執行的邏輯讀或一致性讀。

   Reads為物理讀。

   OMem:當前操作完成所有內存工作區(Work Aera)操作所總共使用私有內存(PGA)中工作區的大小，這個數據是由優化器統計數據以及前一次執行的性能數據估算得出的

   1Mem:當工作區大小無法滿足操作所需的大小時，需要將部分數據寫入臨時磁盤空間中(如果僅需要寫入一次就可以完成操作，就稱一次通過，One-Pass;否則為多次通過，Multi_Pass).該列數據為語句最后一次執行中，單次寫磁盤所需要的內存大小，這個由優化器統計數據以及前一次執行的性能數據估算得出的

   User-Mem:語句最后一次執行中，當前操作所使用的內存工作區大小，括號里面為(發生磁盤交換的次數,1次即為One-Pass,大于1次則為Multi_Pass,如果沒有使用磁盤，則顯示OPTIMAL)

   OMem、1Mem為執行所需的內存評估值，0Mem為最優執行模式所需內存的評估值，1Mem為one-pass模式所需內存的評估值。

0/1/M 為最優/one-pass/multipass執行的次數。Used-Mem耗的內存

SQL> set autotrace off         

SQL> alter session setstatistics_level=all ;

Session altered.

SQL> SELECT  *

 2  FROM t1, t2

 3  WHERE t1.id = t2.t1_id

 4  AND t1.n in(18,19);

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

優點

1.可以清晰的從STARTS得出表被訪問多少。

2.可以清晰的從E-ROWS和A-ROWS中得到預測的行數和真實的行數，從而可以準確判斷Oracle評估是否準確。

3.雖然沒有專門的輸出運行時的相關統計信息，但是執行計劃中的BUFFERS就是真實的邏輯讀的多少

缺點

1.必須要等到語句真正執行完畢后，才可以出結果。

2.無法控制記錄輸屏打出，不像autotrace有 traceonly 可以控制不將結果打屏輸出。

3.看不出遞歸調用的次數，看不出物理讀的多少（不過邏輯讀才是重點）

dbms_xplan.display_cursor

步驟1： select  * fromtable(dbms_xplan.display_cursor('&sq_id'));

sql_id獲取方式多種，如相關的性能視圖中、AWR等報告中。

注：如果有多個執行計劃

select * fromtable(dbms_xplan.display_cursor('cyzznbykb509s',0));

select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));

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

優點

1.知道sql_id立即可得到執行計劃，和explainplan for 一樣無需執行；

2.可以得到真實的執行計劃。

缺點

1.沒有輸出運行時的相關統計信息（產生多少邏輯讀，多少次遞歸調用，多少次物理讀的情況）；

2.無法判斷是處理了多少行； 

3.無法判斷表被訪問了多少次。

10046trace

步驟1：alter session set events '10046 trace name context  forever,level 12'; （開啟跟蹤）

步驟2：執行你的語句

步驟3：alter session set events '10046 trace name context off';   （關閉跟蹤）

步驟4：找到跟蹤后產生的文件

步驟5：tkprof  trc文件  目標文件  sys=no sort=prsela,exeela,fchela  （格式化命令）

SQL> alter session setstatistics_level=typical;    

Session altered.

SQL> alter session set events '10046trace name context  forever,level 12';

Session altered.

SQL> set autotrace off

SQL> SELECT  *

 2  FROM t1, t2

 3  WHERE t1.id = t2.t1_id

 4  AND t1.n in(18,19);

SQL> alter session set events '10046trace name context off';

Session altered.

select d.value

|| '/'

|| LOWER(RTRIM(i.INSTANCE, CHR(0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

from (select p.spid

      from v$mystat m,v$session s, v$process p

      where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,

      (select t.INSTANCE

       FROM v$thread t,v$parameter v

       WHERE v.name='thread'

       AND(v.VALUE=0 ORt.thread#=to_number(v.value))) i,

       (select value

       from v$parameter

       where name='user_dump_dest') d;

/u01/oracle/diag/rdbms/ocp11g/ocp11g/trace/ocp11g_ora_22508.trc

tkprof/u01/oracle/diag/rdbms/ocp11g/ocp11g/trace/ocp11g_ora_22508.trc 10046test.txtsys=no sort=prsela,exeela,fchela

查看10046test.txt文本即可

優點

1.可以看出SQL語句對應的等待事件

2.如果SQL語句中有函數調用，SQL中有SQL，將會都被列出，無處遁形。

3.可以方便的看出處理的行數，產生的物理邏輯讀。

4.可以方便的看出解析時間和執行時間。

5.可以跟蹤整個程序包

缺點

1.步驟繁瑣，比較麻煩

2.無法判斷表被訪問了多少次。

3.執行計劃中的謂詞部分不能清晰的展現出來。

awrsqrpt.sql  

  步驟1：@?/rdbms/admin/awrsqrpt.sql

  步驟2：選擇你要的斷點（beginsnap 和end snap)

  步驟3：輸入你的sql_id