程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> [Oracle]獲取執行計劃的各方法總結

[Oracle]獲取執行計劃的各方法總結

編輯：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,或者方法4調用現成的；
2.跟蹤某條SQL最簡單的方法是方法1，其次就是方法2；
3.如果想觀察到某條SQL有多條執行計劃的情況，只能用方法4和方法6；
4.如果SQL中含有多函數，函數中套有SQL等多層遞歸調用，想准確分析，只能使用方法5；
5.要想確保看到真實的執行計劃，不能用方法1和方法2；

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

環境構造

--研究Nested Loops Join訪問次數前准備工作

DROP TABLE t1 CASCADE CONSTRAINTS PURGE; 
DROP TABLE t2 CASCADE CONSTRAINTS PURGE; 
CREATE TABLE t1 (
     id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ; 
CREATE TABLE t2 (
     id NUMBER NOT NULL,
     t1_id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ; 
execute dbms_random.seed(0); 
INSERT INTO t1
     SELECT  rownum,  rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 1000
      ORDER BY dbms_random.random; 
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random; 
COMMIT; 
CREATE INDEX t1_n ON t1 (n);
CREATE INDEX t2_t1_id ON t2(t1_id);

下面我們將會用多種方法來查看如下語句的執行計劃
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);

方法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行。

優點： 1.無需真正執行，快捷方便
缺陷： 1.沒有輸出運行時的相關統計信息（產生多少邏輯讀，多少次遞歸調用，多少次物理讀的情況）；
2.無法判斷是處理了多少行；
3.無法判斷表被訪問了多少次。
確實啊，這畢竟都沒有真正執行又如何得知真實運行產生的統計信息。

方法2(set autotrace on 方式)

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

另，有如下幾種方式：
set autotrace on (得到執行計劃，輸出運行結果）
set autotrace traceonly (得到執行計劃，不輸出運行結果）
set autotrace traceonly explain (得到執行計劃，不輸出運行結果和統計信息部分，僅展現執行計劃部分）
set autotrace traceonl statistics(不輸出運行結果和執行計劃部分，僅展現統計信息部分）

set autotrace on 
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               |          |     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)
統計信息
----------------------------------------------------------
          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

優點：1.可以輸出運行時的相關統計信息（產生多少邏輯讀，多少次遞歸調用，多少次物理讀的情況）；
2.雖然必須要等語句執行完畢後才可以輸出執行計劃，但是可以有traceonly開關來控制返回結果不打屏輸出。

缺陷：1.必須要等到語句真正執行完畢後，才可以出結果；
2.無法看到表被訪問了多少次。

方法3（statistics level=all的方式)

步驟1：alter session set statistics_level=all ;
步驟2：在此處執行你的SQL
步驟3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

另注：

1. 如果你用 /*+ gather_plan_statistics */的方法，可以省略步驟1，直接步驟2,3。
2. 關鍵字解讀（其中OMem、1Mem和User-Mem在後續的課程中會陸續見到）：
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耗的內存

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行。

優點：1.可以清晰的從STARTS得出表被訪問多少。
2.可以清晰的從E-ROWS和A-ROWS中得到預測的行數和真實的行數，從而可以准確判斷Oracle評估是否准確。
3.雖然沒有專門的輸出運行時的相關統計信息，但是執行計劃中的BUFFERS就是真實的邏輯讀的多少

缺陷：1.必須要等到語句真正執行完畢後，才可以出結果。
2.無法控制記錄輸屏打出，不像autotrace有 traceonly 可以控制不將結果打屏輸出。
3.看不出遞歸調用的次數，看不出物理讀的多少（不過邏輯讀才是重點）

方法4(知道sql_id後，直接帶入的方式，簡單，就步驟1）

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

注：
1. 還有一個方法，select * from table(dbms_xplan.display_awr('&sq_id'));（這是awr性能視圖裡獲取到的）
2. 如果有多執行計劃，可以用類似方法查出
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)

優點：1.知道sql_id立即可得到執行計劃，和explain plan for 一樣無需執行；
2.可以得到真實的執行計劃。（停，等等，啥真實的，剛才這幾個套路中，還有假的執行計劃的嗎？)

缺陷： 1.沒有輸出運行時的相關統計信息（產生多少邏輯讀，多少次遞歸調用，多少次物理讀的情況）；
2.無法判斷是處理了多少行；
3.無法判斷表被訪問了多少次。

方法5(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 （格式化命令）

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

優點：1.可以看出SQL語句對應的等待事件
2.如果SQL語句中有函數調用，SQL中有SQL，將會都被列出，無處遁形。
3.可以方便的看出處理的行數，產生的物理邏輯讀。
4.可以方便的看出解析時間和執行時間。
5.可以跟蹤整個程序包

缺陷: 1.步驟繁瑣，比較麻煩
2.無法判斷表被訪問了多少次。
3.執行計劃中的謂詞部分不能清晰的展現出來。