一.獲取執(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
創(chuàng)新互聯(lián)專注于企業(yè)網(wǎng)絡(luò)營銷推廣、網(wǎng)站重做改版、奉節(jié)網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5場景定制、商城網(wǎng)站建設(shè)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站建設(shè)、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為奉節(jié)等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
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)存評估值，0Mem為最優(yōu)執(zhí)行模式所需內(nèi)存的評估值，1Mem為one-pass模式所需內(nèi)存的評估值。
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ù)測的行數(shù)和真實(shí)的行數(shù)，從而可以準(zhǔn)確判斷Oracle評估是否準(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后，直接帶入的方式，簡單，就步驟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語句對應(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
*/
二.適用場合分析
1.如果某SQL執(zhí)行非常長時(shí)間才會(huì)出結(jié)果，甚至慢到返回不了結(jié)果，這時(shí)候看執(zhí)行計(jì)劃就只能用方法1；
2.跟蹤某條SQL最簡單的方法是方法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；
重慶分公司
重慶分公司
