獲取執行計劃的6種方法
一.獲取執行計劃的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. 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. 透過dbms_xplan.display_cursor輸入sql_id引數直接獲取(知道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. 10046 trace跟蹤
/*
步驟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 2 1.31 1.31
/*
--優點：1.可以看出SQL語句對應的等待事件
2.如果SQL語句中有函式呼叫，SQL中有SQL，將會都被列出，無處遁形。
3.可以方便的看出處理的行數，產生的物理邏輯讀。
4.可以方便的看出解析時間和執行時間。
5.可以跟蹤整個程式包
--缺陷: 1.步驟繁瑣，比較麻煩
2.無法判斷表被訪問了多少次。
3.執行計劃中的謂詞部分不能清晰的展現出來。
*/
6. awrsqrpt.sql
/*
步驟1：@?/rdbms/admin/awrsqrpt.sql
步驟2：選擇你要的斷點（begin snap 和end snap)
步驟3：輸入你的sql_id
*/
二.適用場合分析
1.如果某SQL執行非常長時間才會出結果，甚至慢到返回不了結果，這時候看執行計劃就只能用方法1；
2.跟蹤某條SQL最簡單的方法是方法1，其次就是方法2；
3.如果想觀察到某條SQL有多條執行計劃的情況，只能用方法4和方法6；
4.如果SQL中含有多函式，函式中套有SQL等多層遞迴呼叫，想準確分析，只能使用方法5；
5.要想確保看到真實的執行計劃，不能用方法1和方法2；
6.要想獲取表被訪問的次數，只能使用方法3；
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30496894/viewspace-1846438/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- Oracle 獲取執行計劃的幾種方法Oracle
- 獲取執行計劃的方法
- Oracle獲取執行計劃的方法Oracle
- 介紹幾種獲取SQL執行計劃的方法（上）SQL
- Oracle 獲取SQL執行計劃方法OracleSQL
- 執行計劃-1：獲取執行計劃
- 獲取SQL執行計劃SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- 獲取SQL執行計劃的方式：SQL
- 獲取執行計劃之Autotrace
- 執行計劃幾種方法
- 如何獲取真實的執行計劃
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- oracle dbms_xplan獲取執行計劃Oracle
- MySQL 5.7獲取指定執行緒正在執行SQL的執行計劃資訊MySql執行緒
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- 會話的跟蹤以及執行計劃的獲取會話
- Oracle10g如何獲取執行計劃Oracle
- 如何制定專案執行計劃的幾種方法
- 獲得目標SQL語句執行計劃的方法SQL
- 多種方法檢視Oracle SQL執行計劃OracleSQL
- 生成執行計劃的方法
- 檢視SQL執行計劃的幾種常用方法YQSQL
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- oracle explain plan for獲取執行計劃並不可靠.OracleAI
- VB6 獲取CPUID的幾種方法UI
- 【LanceZhang】ASP.NET獲取IP的6種方法ASP.NET
- 在本地執行 LLMs 的 6 種方法
- 執行計劃的閱讀方法
- 檢視執行計劃的方法
- 微課sql最佳化(9)、如何獲取執行計劃SQL
- 設定CURRENT_SCHEMA後獲取執行計劃報錯
- oracle 9i 獲取sql執行計劃(書寫長的sql)OracleSQL
- mysql的執行計劃快取問題MySql快取
- 使用 EXPLAIN PLAN 獲取SQL語句執行計劃 （R0.1）AISQL
- 檢視SQL的執行計劃方法SQL
- oracle檢視執行計劃的方法Oracle