一. 獲取oracle執(zhí)行計(jì)劃的方法有6種，各自的優(yōu)缺點(diǎn)如下，根據(jù)實(shí)際情況進(jìn)行選擇使用：

成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)介紹好的網(wǎng)站是理念、設(shè)計(jì)和技術(shù)的結(jié)合。創(chuàng)新互聯(lián)擁有的網(wǎng)站設(shè)計(jì)理念、多方位的設(shè)計(jì)風(fēng)格、經(jīng)驗(yàn)豐富的設(shè)計(jì)團(tuán)隊(duì)。提供PC端+手機(jī)端網(wǎng)站建設(shè)，用營(yíng)銷思維進(jìn)行網(wǎng)站設(shè)計(jì)、采用先進(jìn)技術(shù)開源代碼、注重用戶體驗(yàn)與SEO基礎(chǔ)，將技術(shù)與創(chuàng)意整合到網(wǎng)站之中，以契合客戶的方式做到創(chuàng)意性的視覺化效果。

1. explain plan for 方式

步驟1：explain plan for 后跟著SQL語(yǔ)句
步驟2：select * from table(dbms_xplan.display());

優(yōu)點(diǎn)： 1.不需要真正的去執(zhí)行語(yǔ)句，快捷方便
缺點(diǎn)： 1.雖然快捷但是因?yàn)闆]有真正去運(yùn)行，所以沒有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（邏輯讀，遞歸調(diào)用，物理讀）
2.不知道被處理的行數(shù)
3.不知道表被訪問的次數(shù)

2. set autotrace on 方式
   步驟1：set autotrace on
   步驟2：sql語(yǔ)句

set autotrace有四種不同的模式

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ì)劃部分，如果是select查詢則不會(huì)執(zhí)行語(yǔ)句）
set autotrace traceonl statistics(不輸出運(yùn)行結(jié)果和執(zhí)行計(jì)劃部分，僅展現(xiàn)統(tǒng)計(jì)信息部分）

優(yōu)點(diǎn)：1.可以輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（產(chǎn)生多少邏輯讀，多少次遞歸調(diào)用，多少次物理讀的情況），這是方法1不具備的；
2.雖然必須要等語(yǔ)句執(zhí)行完畢后才可以輸出執(zhí)行計(jì)劃，但是可以有traceonly開關(guān)來控制返回結(jié)果不打屏輸出。

缺陷：1.必須要等到語(yǔ)句執(zhí)行完畢后，才能出結(jié)果；
2.無法看到表被訪問的次數(shù)。

3. statistics level=all 方式

步驟1：alter session set statistics_level=all（如果之前有將 set autotrace on，需要先將set autotrace off） ;
步驟2：執(zhí)行SQL語(yǔ)句（如果在sql語(yǔ)句中加hint /+ gather_plan_statistics /，可以不用設(shè)置步驟一）
步驟3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Starts 是語(yǔ)句實(shí)際執(zhí)行次數(shù)，E-Rows為執(zhí)行計(jì)劃預(yù)計(jì)行數(shù)，A-Rows為實(shí)際返回行數(shù)，A-Time為每一步實(shí)際執(zhí)行的時(shí)間，Buffers為每一步實(shí)際執(zhí)行的邏輯讀，這幾個(gè)目前比較有用

優(yōu)點(diǎn)：1.可以清晰的從STARTS得出實(shí)際執(zhí)行次數(shù)，表被訪問多少。
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.必須要等到語(yǔ)句真正執(zhí)行完畢后，才可以出結(jié)果。
2.記錄必須得輸屏打出，不像autotrace有 traceonly 可以控制不將結(jié)果打屏輸出。
3.看不出遞歸調(diào)用的次數(shù)，看不出物理讀的多少

4. 帶入sql_id 方式

步驟1： select from table(dbms_xplan.display_cursor('&sql_id')); （該方法是從共享池里得到，這個(gè)語(yǔ)句必須得跑過一次）
另一方法： select from table(dbms_xplan.display_awr('&sq_id'));（這是awr性能視圖里獲取到的）

如果有多執(zhí)行計(jì)劃，可以用類似方法查出
select from table(dbms_xplan.display_cursor('sql_id',0));
select from table(dbms_xplan.display_cursor('sql_id',1));

優(yōu)點(diǎn)：1.知道sql_id立即可得到執(zhí)行計(jì)劃，和explain plan for 一樣無需執(zhí)行；
2.可以得到真實(shí)的執(zhí)行計(jì)劃。而方法1與2的則是預(yù)估

缺陷 1.沒有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（邏輯讀，遞歸調(diào)用，物理讀）；
2.無法判斷是處理的行數(shù)；
3.無法判斷表被訪問的次數(shù)。

5. 10046TRACE 方式

步驟1：alter session set events '10046 trace name context forever,level 12';
步驟2：執(zhí)行sql語(yǔ)句
步驟3：alter session set events '10046 trace name context off';
步驟4：找到跟蹤后產(chǎn)生的文件

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

步驟5：tkprof trc文件路徑 目標(biāo)txt文件 sys=no sort=prsela,exeela,fchela

優(yōu)點(diǎn)：1.可以看出SQL語(yǔ)句對(duì)應(yīng)的等待事件
2.如果SQL語(yǔ)句中有函數(shù)調(diào)用，SQL中有SQL，將會(huì)都被列出。
3.可以方便的看出處理的行數(shù)，產(chǎn)生的物理邏輯讀。
4.可以方便的看出解析時(shí)間和執(zhí)行時(shí)間。
5.可以跟蹤整個(gè)程序包

缺陷: 1.步驟繁瑣，比較麻煩
2.無法判斷表被訪問了多少次。
3.執(zhí)行計(jì)劃中的條件語(yǔ)句不能清晰的展現(xiàn)出來。

6. awrsqrpt.sql 方式

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

優(yōu)點(diǎn)：1.同樣也可以獲取到多條執(zhí)行計(jì)劃，并可在報(bào)表輸出
2.知道sql_id立即可得到執(zhí)行計(jì)劃，和explain plan for 一樣無需執(zhí)行；
3.可以得到真實(shí)的執(zhí)行計(jì)劃。而方法1與2的則是預(yù)估

缺陷: 1.步驟繁瑣，比較麻煩，還需要查snap的時(shí)間
2.沒有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（邏輯讀，遞歸調(diào)用，物理讀）；
3.無法判斷是處理的行數(shù)；
4.無法判斷表被訪問的次數(shù)。

適用情況總結(jié)：

1.如果某SQL執(zhí)行非常長(zhǎng)時(shí)間才會(huì)出結(jié)果，甚至慢到返回不了結(jié)果，這時(shí)候看執(zhí)行計(jì)劃就用explain plan for或者set autotrace traceonly statistics（前提是select）；
2.跟蹤某條SQL最簡(jiǎn)單的方法是explain plan for，其次就是set autotrace on（traceonly）；
3.如果想觀察到某條SQL有多條執(zhí)行計(jì)劃的情況，只能通過dbms_xplan.display_cursor輸入sql_id參數(shù)直接獲取和查看awrsqrpt.sql；
4.如果SQL中含有多函數(shù)，函數(shù)中套有SQL等多層遞歸調(diào)用，想準(zhǔn)確分析，只能用10046 trace跟蹤；
5.要想確?？吹秸鎸?shí)的執(zhí)行計(jì)劃，不能用explain plan for和set autotrace on；
6.要想獲取表被訪問的次數(shù)，只能使用statistics_level=all的方法；

二. 如何辨別低效的SQL：

有7個(gè)可以注意的地方

1.真實(shí)返回值與產(chǎn)生邏輯讀的比例
一般而言，每獲取一行開銷5個(gè)以下的邏輯讀是ok的。
如果用statitics_level=all獲取執(zhí)行計(jì)劃，查看BUFFERS（邏輯讀）/A-ROWS（獲取行數(shù)）的比值，如果用autotrace，則查看consistent gets（邏輯讀）/rows processed（獲取行數(shù)）的比值

2.執(zhí)行計(jì)劃中評(píng)估準(zhǔn)確的重要性
這很重要，錯(cuò)誤的評(píng)估往往意味著低效的執(zhí)行計(jì)劃，此種必須要用statistics_level=all查看。
因此要查看預(yù)估執(zhí)行行數(shù)E-Rows與實(shí)際執(zhí)行行數(shù)A-Rows的比值，偏差較大時(shí)，很可能是收集直方圖不準(zhǔn)確導(dǎo)致的，需要重新再收集一次直方圖。

3.發(fā)生類型轉(zhuǎn)換需要進(jìn)行關(guān)注查看
關(guān)注執(zhí)行計(jì)劃中的Predicate Information (identified by operation id)部分其中是否出現(xiàn)類型轉(zhuǎn)換。
因?yàn)橥霈F(xiàn)類型轉(zhuǎn)換后，無法調(diào)用到索引，造成效率低下，在不清楚字段類型就給予取值時(shí)容易出現(xiàn)這種問題。

4.遞歸調(diào)用次數(shù)的查看
6種方法里只有autotrace可以查看遞歸調(diào)用次數(shù)，如果某一語(yǔ)句的遞歸調(diào)用次數(shù)非常大，比如一個(gè)幾萬行的表就出現(xiàn)了幾萬次的遞歸調(diào)用，那一定是有問題的。
詳細(xì)問題就要進(jìn)一步通過10046 trace來繼續(xù)跟蹤查看具體原因，個(gè)人經(jīng)驗(yàn)來說，一般是表連接出現(xiàn)問題導(dǎo)致。

5.表訪問次數(shù)的查看
6種方法里只有 statisitcs_level=all 的方式可以看出表訪問次數(shù)（STARTS)
如果一個(gè)表被訪問次數(shù)很多，很可能有問題，這時(shí)要看表是否存在連接，以及連接的類型，如果表訪問這么多次一般是哈希或者排序連接，如果是NL連接那么一定是有問題的。

6.注意表真實(shí)訪問的行數(shù)
這個(gè)往往是由于查詢條件上未能優(yōu)化所導(dǎo)致的，真實(shí)訪問次數(shù)太大的時(shí)候可以仔細(xì)查看一下sql語(yǔ)句是否可以有優(yōu)化的空間。
這點(diǎn)和第一點(diǎn)有異曲同工之處，查看下rows processed，如果計(jì)劃中的A-Rows在開始階段太高，很可能有優(yōu)化的空間。
看下面的例子非常好：
select
from (select t1., rownum as rn from t1, t2 where t1.object_id = t2.id1) a
where a.rn >= 1
and a.rn <= 10;
與
select
from (select t1., rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=10) a
where a.rn >= 1;
讓我對(duì)于取范圍值的優(yōu)化上有了一個(gè)新的思路，適當(dāng)情況下可以在子查詢中放入范圍查詢的部分條件，這樣訪問的真實(shí)行數(shù)會(huì)大幅度下降。
即執(zhí)行計(jì)劃中的（COUNT STOPKEY）這個(gè)關(guān)鍵字，體現(xiàn)了局部訪問的算法。

7.謹(jǐn)慎觀察是否發(fā)生排序
查看執(zhí)行計(jì)劃里是否存在SORT ORDER BY，以及統(tǒng)計(jì)信息中的sorts，特別是sorts（disk）
如果用statistics_level=all的方法查看，要看Used-Mem這項(xiàng)，xxxxK后的（0）與（1）分別代表沒交換到磁盤與已交換到磁盤。
如存在是否必須，是否可以用走索引來避免。

三. 如何讀懂輸出執(zhí)行計(jì)劃，下面用聯(lián)合型+單獨(dú)型的方式，畫出執(zhí)行計(jì)劃的訪問草圖供參考：

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 15 | | | |
| 1 | CONNECT BY WITH FILTERING | | 1 | | 14 |00:00:00.01 | 15 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 3 | NESTED LOOPS | | 4 | 2 | 13 |00:00:00.01 | 8 | | | |
| 4 | CONNECT BY PUMP | | 4 | | 14 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 2 | 13 |00:00:00.01 | 8 | | | |
|* 6 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 2 | 13 |00:00:00.01 | 5 | | | |

訪問草圖如下：

     ---[5]---[6]