Oracle擷取執行計畫方法

標籤：explain   autotrace   statistcis level   dbms_xplan   10046trace   

擷取執行計畫的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

本文出自 “90SirDB” 部落格，請務必保留此出處http://90sirdb.blog.51cto.com/8713279/1793451

Oracle擷取執行計畫方法