獲取執(zhí)行計(jì)劃的6種方法

  一.獲取執(zhí)行計(jì)劃的6種方法(詳細(xì)步驟已經(jīng)在每個(gè)例子的開頭注釋部分說明了）：
    1. explain plan for獲??；
    2. set autotrace on ；   
    3. statistics_level=all;
    4. 通過dbms_xplan.display_cursor輸入sql_id參數(shù)直接獲取
    5. 10046 trace跟蹤
    6. awrsqrpt.sql

1. explain plan for獲取；(類似PLSQL DEVELOPE里的F5)

/*
  步驟1：explain plan for "你的SQL"
  步驟2：select * from table(dbms_xplan.display());
*/

set linesize 1000
set pagesize 2000
explain plan for
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     2 |  8138 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 |
|   3 |    INLIST ITERATOR             |          |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."N"=18 OR "T1"."N"=19)
   6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
   - dynamic sampling used for this statement (level=2)

已選擇24行。
/*
優(yōu)點(diǎn)：  1.無需真正執(zhí)行，快捷方便

缺陷：  1.沒有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（產(chǎn)生多少邏輯讀，多少次遞歸調(diào)用，多少次物理讀的情況）；
        2.無法判斷是處理了多少行；
        3.無法判斷表被訪問了多少次。
       
確實(shí)啊，這畢竟都沒有真正執(zhí)行又如何得知真實(shí)運(yùn)行產(chǎn)生的統(tǒng)計(jì)信息。
*/

 2. set autotrace on ；
/*
  步驟1：set autotrace on
  步驟2：在此處執(zhí)行你的SQL即可，后續(xù)自然會(huì)有結(jié)果輸出
 
另，有如下幾種方式：
                     set autotrace on                 (得到執(zhí)行計(jì)劃，輸出運(yùn)行結(jié)果）
                     set autotrace traceonly          (得到執(zhí)行計(jì)劃，不輸出運(yùn)行結(jié)果）
                     set autotrace traceonly explain  (得到執(zhí)行計(jì)劃，不輸出運(yùn)行結(jié)果和統(tǒng)計(jì)信息部分，僅展現(xiàn)執(zhí)行計(jì)劃部分）
                     set autotrace traceonl statistics(不輸出運(yùn)行結(jié)果和執(zhí)行計(jì)劃部分，僅展現(xiàn)統(tǒng)計(jì)信息部分）
*/
set autotrace on
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);

執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     2 |  8138 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 |
|   3 |    INLIST ITERATOR             |          |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."N"=18 OR "T1"."N"=19)
   6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
   - dynamic sampling used for this statement (level=2)
統(tǒng)計(jì)信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

/*
--優(yōu)點(diǎn)：1.可以輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（產(chǎn)生多少邏輯讀，多少次遞歸調(diào)用，多少次物理讀的情況）；
        2.雖然必須要等語句執(zhí)行完畢后才可以輸出執(zhí)行計(jì)劃，但是可以有traceonly開關(guān)來控制返回結(jié)果不打屏輸出。
                 
--缺陷：1.必須要等到語句真正執(zhí)行完畢后，才可以出結(jié)果；
        2.無法看到表被訪問了多少次。       
        
*/        
        
        
       
        
        
3. statistics_level=all;
/*
  步驟1：alter session set statistics_level=all ;
  步驟2：在此處執(zhí)行你的SQL
  步驟3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
 另注：
 
  1. 如果你用 /*+ gather_plan_statistics */的方法，可以省略步驟1，直接步驟2,3。
  2. 關(guān)鍵字解讀（其中OMem、1Mem和User-Mem在后續(xù)的課程中會(huì)陸續(xù)見到）：
    Starts為該sql執(zhí)行的次數(shù)。
    E-Rows為執(zhí)行計(jì)劃預(yù)計(jì)的行數(shù)。
    A-Rows為實(shí)際返回的行數(shù)。A-Rows跟E-Rows做比較，就可以確定哪一步執(zhí)行計(jì)劃出了問題。
    A-Time為每一步實(shí)際執(zhí)行的時(shí)間（HH：MM：SS.FF），根據(jù)這一行可以知道該sql耗時(shí)在了哪個(gè)地方。
    Buffers為每一步實(shí)際執(zhí)行的邏輯讀或一致性讀。
    Reads為物理讀。
    OMem:當(dāng)前操作完成所有內(nèi)存工作區(qū)(Work Aera)操作所總共使用私有內(nèi)存(PGA)中工作區(qū)的大小，
         這個(gè)數(shù)據(jù)是由優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)以及前一次執(zhí)行的性能數(shù)據(jù)估算得出的
    1Mem:當(dāng)工作區(qū)大小無法滿足操作所需的大小時(shí)，需要將部分?jǐn)?shù)據(jù)寫入臨時(shí)磁盤空間中(如果僅需要寫入一次就可以完成操作，
         就稱一次通過，One-Pass;否則為多次通過，Multi_Pass).該列數(shù)據(jù)為語句最后一次執(zhí)行中，單次寫磁盤所需要的內(nèi)存
         大小，這個(gè)由優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)以及前一次執(zhí)行的性能數(shù)據(jù)估算得出的
    User-Mem:語句最后一次執(zhí)行中，當(dāng)前操作所使用的內(nèi)存工作區(qū)大小，括號(hào)里面為(發(fā)生磁盤交換的次數(shù),1次即為One-Pass,
           大于1次則為Multi_Pass,如果沒有使用磁盤，則顯示OPTIMAL)
    OMem、1Mem為執(zhí)行所需的內(nèi)存評(píng)估值，0Mem為最優(yōu)執(zhí)行模式所需內(nèi)存的評(píng)估值，1Mem為one-pass模式所需內(nèi)存的評(píng)估值。
    0/1/M 為最優(yōu)/one-pass/multipass執(zhí)行的次數(shù)。Used-Mem耗的內(nèi)存
 
*/      
set autotrace off         
alter session set statistics_level=all ;
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  1a914ws3ggfsn, child number 0
-------------------------------------
SELECT  * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)

Plan hash value: 3532430033
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |      2 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                  |          |      1 |        |      2 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                 |          |      1 |      2 |      2 |00:00:00.01 |      10 |
|   3 |    INLIST ITERATOR             |          |      1 |        |      2 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |      2 |      2 |      2 |00:00:00.01 |       5 |
|*  5 |      INDEX RANGE SCAN          | T1_N     |      2 |      1 |      2 |00:00:00.01 |       3 |
|*  6 |    INDEX RANGE SCAN            | T2_T1_ID |      2 |      1 |      2 |00:00:00.01 |       5 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |      2 |      1 |      2 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access(("T1"."N"=18 OR "T1"."N"=19))
   6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
   - dynamic sampling used for this statement (level=2)

已選擇29行。
/*
--優(yōu)點(diǎn)：1.可以清晰的從STARTS得出表被訪問多少。
        2.可以清晰的從E-ROWS和A-ROWS中得到預(yù)測(cè)的行數(shù)和真實(shí)的行數(shù)，從而可以準(zhǔn)確判斷Oracle評(píng)估是否準(zhǔn)確。
        3.雖然沒有專門的輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息，但是執(zhí)行計(jì)劃中的BUFFERS就是真實(shí)的邏輯讀的多少
                
--缺陷：1.必須要等到語句真正執(zhí)行完畢后，才可以出結(jié)果。
        2.無法控制記錄輸屏打出，不像autotrace有 traceonly 可以控制不將結(jié)果打屏輸出。
        3.看不出遞歸調(diào)用的次數(shù)，看不出物理讀的多少（不過邏輯讀才是重點(diǎn)）
*/

4. 通過dbms_xplan.display_cursor輸入sql_id參數(shù)直接獲取(知道sql_id后，直接帶入的方式，簡(jiǎn)單，就步驟1）

 
/* 

步驟1： select  * from table(dbms_xplan.display_cursor('&sq_id')); （該方法是從共享池里得到）

注：
  1. 還有一個(gè)方法，select  * from table(dbms_xplan.display_awr('&sq_id'));（這是awr性能視圖里獲取到的）
  2. 如果有多執(zhí)行計(jì)劃，可以用類似方法查出
    select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
    select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));

*/

select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  1a914ws3ggfsn, child number 0
-------------------------------------
SELECT  * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)

Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS                  |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 |
|   3 |    INLIST ITERATOR             |          |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(("T1"."N"=18 OR "T1"."N"=19))
   6 - access("T1"."ID"="T2"."T1_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)
  
/*  
--優(yōu)點(diǎn)：1.知道sql_id立即可得到執(zhí)行計(jì)劃，和explain plan for 一樣無需執(zhí)行；
        2.可以得到真實(shí)的執(zhí)行計(jì)劃。（停，等等，啥真實(shí)的，剛才這幾個(gè)套路中，還有假的執(zhí)行計(jì)劃的嗎？)
       
                
--缺陷  1.沒有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（產(chǎn)生多少邏輯讀，多少次遞歸調(diào)用，多少次物理讀的情況）；
        2.無法判斷是處理了多少行； 
        3.無法判斷表被訪問了多少次。
       
*/       

5. 10046 trace跟蹤
/*

  步驟1：alter session set events '10046 trace name context  forever,level 12'; （開啟跟蹤）
  步驟2：執(zhí)行你的語句
  步驟3：alter session set events '10046 trace name context off';   （關(guān)閉跟蹤）
  步驟4：找到跟蹤后產(chǎn)生的文件
  步驟5：tkprof  trc文件  目標(biāo)文件  sys=no sort=prsela,exeela,fchela  （格式化命令）    

*/
set autotace off
alter session set statistics_level=typical;    
alter session set events '10046 trace name context  forever,level 12';

SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);  
  
alter session set events '10046 trace name context off';  
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 OR t.thread#=to_number(v.value))) i,
       (select value
       from v$parameter
       where name='user_dump_dest') d;

exit     
 
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc    d:\10046.txt  sys=no sort=prsela,exeela,fchela      

SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         12          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         12          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 94 

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  NESTED LOOPS  (cr=12 pr=0 pw=0 time=0 us)
      2   NESTED LOOPS  (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2)
      2    INLIST ITERATOR  (cr=5 pr=0 pw=0 time=16 us)
      2     TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2)
      2      INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621)
      2    INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622)
      2   TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        1.31          1.31
 
/*
--優(yōu)點(diǎn)：1.可以看出SQL語句對(duì)應(yīng)的等待事件
        2.如果SQL語句中有函數(shù)調(diào)用，SQL中有SQL，將會(huì)都被列出，無處遁形。
        3.可以方便的看出處理的行數(shù)，產(chǎn)生的物理邏輯讀。
        4.可以方便的看出解析時(shí)間和執(zhí)行時(shí)間。
        5.可以跟蹤整個(gè)程序包
       
       
                
--缺陷: 1.步驟繁瑣，比較麻煩
        2.無法判斷表被訪問了多少次。
        3.執(zhí)行計(jì)劃中的謂詞部分不能清晰的展現(xiàn)出來。
       
*/  
       
       
       
6. awrsqrpt.sql  

/*
  步驟1：@?/rdbms/admin/awrsqrpt.sql
  步驟2：選擇你要的斷點(diǎn)（begin snap 和end snap)
  步驟3：輸入你的sql_id    
*/
  

 二.適用場(chǎng)合分析
 
    1.如果某SQL執(zhí)行非常長時(shí)間才會(huì)出結(jié)果，甚至慢到返回不了結(jié)果，這時(shí)候看執(zhí)行計(jì)劃就只能用方法1；
    2.跟蹤某條SQL最簡(jiǎn)單的方法是方法1，其次就是方法2；
    3.如果想觀察到某條SQL有多條執(zhí)行計(jì)劃的情況，只能用方法4和方法6；
    4.如果SQL中含有多函數(shù)，函數(shù)中套有SQL等多層遞歸調(diào)用，想準(zhǔn)確分析，只能使用方法5；
    5.要想確保看到真實(shí)的執(zhí)行計(jì)劃，不能用方法1和方法2；
    6.要想獲取表被訪問的次數(shù)，只能使用方法3；