oracle各種執行計劃優缺點

一. 獲取oracle執行計劃的方法有6種，各自的優缺點如下，根據實際情況進行選擇使用：

1. explain plan for 方式

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

優點： 1.不需要真正的去執行語句，快捷方便
缺點： 1.雖然快捷但是因為沒有真正去運行，所以沒有輸出運行時的相關統計信息（邏輯讀，遞歸調用，物理讀）
2.不知道被處理的行數
3.不知道表被訪問的次數

2. set autotrace on 方式
   步驟1：set autotrace on
   步驟2：sql語句

set autotrace有四種不同的模式

set autotrace on (得到執行計劃，輸出運行結果）
set autotrace traceonly (得到執行計劃，不輸出運行結果）
set autotrace traceonly explain (得到執行計劃，不輸出運行結果和統計信息部分，僅展現執行計劃部分，如果是select查詢則不會執行語句）
set autotrace traceonl statistics(不輸出運行結果和執行計劃部分，僅展現統計信息部分）

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

缺陷：1.必須要等到語句執行完畢后，才能出結果；
2.無法看到表被訪問的次數。

3. statistics level=all 方式

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

Starts 是語句實際執行次數，E-Rows為執行計劃預計行數，A-Rows為實際返回行數，A-Time為每一步實際執行的時間，Buffers為每一步實際執行的邏輯讀，這幾個目前比較有用

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

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

4. 帶入sql_id 方式

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

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

優點：1.知道sql_id立即可得到執行計劃，和explain plan for 一樣無需執行；
2.可以得到真實的執行計劃。而方法1與2的則是預估

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

5. 10046TRACE 方式

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

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文件路徑 目標txt文件 sys=no sort=prsela,exeela,fchela

優點：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.同樣也可以獲取到多條執行計劃，并可在報表輸出
2.知道sql_id立即可得到執行計劃，和explain plan for 一樣無需執行；
3.可以得到真實的執行計劃。而方法1與2的則是預估

缺陷: 1.步驟繁瑣，比較麻煩，還需要查snap的時間
2.沒有輸出運行時的相關統計信息（邏輯讀，遞歸調用，物理讀）；
3.無法判斷是處理的行數；
4.無法判斷表被訪問的次數。

適用情況總結：

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

二. 如何辨別低效的SQL：

有7個可以注意的地方

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

2.執行計劃中評估準確的重要性
這很重要，錯誤的評估往往意味著低效的執行計劃，此種必須要用statistics_level=all查看。
因此要查看預估執行行數E-Rows與實際執行行數A-Rows的比值，偏差較大時，很可能是收集直方圖不準確導致的，需要重新再收集一次直方圖。

3.發生類型轉換需要進行關注查看
關注執行計劃中的Predicate Information (identified by operation id)部分其中是否出現類型轉換。
因為往往出現類型轉換后，無法調用到索引，造成效率低下，在不清楚字段類型就給予取值時容易出現這種問題。

4.遞歸調用次數的查看
6種方法里只有autotrace可以查看遞歸調用次數，如果某一語句的遞歸調用次數非常大，比如一個幾萬行的表就出現了幾萬次的遞歸調用，那一定是有問題的。
詳細問題就要進一步通過10046 trace來繼續跟蹤查看具體原因，個人經驗來說，一般是表連接出現問題導致。

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

6.注意表真實訪問的行數
這個往往是由于查詢條件上未能優化所導致的，真實訪問次數太大的時候可以仔細查看一下sql語句是否可以有優化的空間。
這點和第一點有異曲同工之處，查看下rows processed，如果計劃中的A-Rows在開始階段太高，很可能有優化的空間。
看下面的例子非常好：
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;
讓我對于取范圍值的優化上有了一個新的思路，適當情況下可以在子查詢中放入范圍查詢的部分條件，這樣訪問的真實行數會大幅度下降。
即執行計劃中的（COUNT STOPKEY）這個關鍵字，體現了局部訪問的算法。

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

三. 如何讀懂輸出執行計劃，下面用聯合型+單獨型的方式，畫出執行計劃的訪問草圖供參考：

| 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]