Oracle中如何得到真實的執行計劃

之前介紹過4種在Oracle資料庫裡檢視執行計劃的方法：

explain plan 指令

DBMS_XPLAN包

SQLPLUS中的AUTOTRACE開關

10046事件

其中除了第四種方法之外，其他三種方法得到的執行計劃都有可能是不準确的。在Oracle中判斷得到的執行計劃是否是準确，就是看目标SQL是否被真正執行，真正執行過的SQL所對應的執行計劃就是準确的，反之則有可能不準。但是這裡的判斷原則從嚴格意義上來說并不适用于AUTOTRACE開關，因為所有的AUTOTRACE開關所顯示的執行計劃都可能是不準的，即使對應的目标SQL實際上已經執行過。

下面我們就用上述原則來判斷除第4種以外的其他三種方法中哪些得到的執行計劃是準的，哪些方法得到的執行計劃有可能不準。

1、explain plan指令

對這種方法得到的執行計劃而言，因為此時的目标SQL并沒有被實際執行，是以該方法得到的執行計劃有可能是不準的，尤其是目标SQL包含綁定變量時。在預設開啟綁定變量窺探(Bind Peeking)的情況，對含綁定變量的目标SQL使用explain plan得到的執行計劃隻是一個半成品，Oracle在随後對該SQL的綁定變量進行窺探後就得到了這些綁定變量具體的值，此時Oralce可能會對上述半成品的執行計劃做調整，一量做了調整，使用explain plan指令得到的執行計劃就不準了。

2、DBMS_XPLAN包

對于這種方法而言，針對不同的應用場景，可以選擇如下四種方式中的一種：

select * from table(dbms_xplan.display);

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));

select * from table(dbms_xplan.display_awr('sql_id'));

顯然，執行select * from table(dbms_xplan.display)得到的執行計劃可能是不準的，因為它隻是用于檢視使用explain plan指令得到的目标SQL的執行計劃，目标SQL此時還沒有被真正執行，是以用它得到的執行計劃可能是不準的。使用剩下的三種方式得到的執行計劃都是準的，因為此時目标SQL都已經被實際執行過了。

3、AUTOTRACE開關

使用這種方法，可以選擇如下三種方式來開啟TRACE開關

SET AUTOTRACE ON

SET AUTOTRACE TRACEONLY

SET AUTOTRACE TRACEONLY EXPLAIN

上述三種方法中，當使用SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY時，目标SQL都已經被實際執行過了，正是因為被實際執行過，是以SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY的情況下我們能看到目标SQL的實際資源消耗情況。當使用SET AUTOTRACE TRACEONLY EXPLAIN時，如果執行的是SELECT語句，則該SELECT語句并沒有被Oracle實際執行，但如果執行的是DML語句，情況就不一樣了，此時的DML語句會被Oracle實際執行的。雖然使用部分SET AUTOTRACE指令後目标SQL實際上已經執行過了，但所得到的執行計劃有可能是不準的，因為使用SET AUTOTRACE指令所顯示的執行計劃都是來源于調用explain plan指令。

下面使用一個例子證明：

<code>scott@ORCL></code><code>create</code> <code>table</code> <code>t1 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>dba_objects;</code>

<code>Table</code> <code>created.</code>

<code>scott@ORCL></code><code>insert</code> <code>into</code> <code>t1 </code><code>select</code> <code>* </code><code>from</code> <code>t1;</code>

<code>86885 </code><code>rows</code> <code>created.</code>

<code>scott@ORCL></code><code>commit</code><code>;</code>

<code>Commit</code> <code>complete.</code>

<code>scott@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t1;</code>

<code> </code><code>COUNT</code><code>(*)</code>

<code>scott@ORCL></code><code>create</code> <code>index</code> <code>idx_t1 </code><code>on</code> <code>t1(object_id);</code>

<code>Index</code> <code>created.</code>

<code>scott@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname=></code><code>'SCOTT'</code><code>,tabname=></code><code>'T1'</code><code>,estimate_percent=>100,</code><code>cascade</code><code>=></code><code>true</code><code>);</code>

<code>PL/SQL </code><code>procedure</code> <code>successfully completed.</code>

<code>scott@ORCL>var x number;</code>

<code>scott@ORCL>var y number;</code>

<code>scott@ORCL></code><code>exec</code> <code>:x := 0;</code>

<code>scott@ORCL></code><code>exec</code> <code>:y := 100000;</code>

<code>scott@ORCL>explain plan </code><code>for</code> <code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t1 </code><code>where</code> <code>object_id </code><code>between</code> <code>:x </code><code>and</code> <code>:y;</code>

<code>Explained.</code>

<code>scott@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>table</code><code>(dbms_xplan.display);</code>

<code>PLAN_TABLE_OUTPUT</code>

<code>Plan hash value: 2351893609</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |</code>

<code>| 1 | SORT AGGREGATE | | 1 | 5 | | |</code>

<code>|* 3 | </code><code>INDEX</code> <code>RANGE SCAN| IDX_T1 | 434 | 2170 | 3 (0)| 00:00:01 |</code>

<code>Predicate Information (identified </code><code>by</code> <code>operation id):</code>

<code> </code><code>2 - filter(TO_NUMBER(:Y)>=TO_NUMBER(:X))</code>

<code> </code><code>3 - access(</code><code>"OBJECT_ID"</code><code>>=TO_NUMBER(:X) </code><code>AND</code> <code>"OBJECT_ID"</code><code><=TO_NUMBER(:Y))</code>

<code>16 </code><code>rows</code> <code>selected.</code>

<code>scott@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t1 </code><code>where</code> <code>object_id </code><code>between</code> <code>:x </code><code>and</code> <code>:y;</code>

<code>scott@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>table</code><code>(dbms_xplan.display_cursor(</code><code>null</code><code>,</code><code>null</code><code>,</code><code>'ADVANCED'</code><code>));</code>

<code>SQL_ID 9dhu3xk2zu531, child number 0</code>

<code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t1 </code><code>where</code> <code>object_id </code><code>between</code> <code>:x </code><code>and</code> <code>:y</code>

<code>Plan hash value: 1410530761</code>

<code>| 1 | SORT AGGREGATE | | 1 | 5 | | |</code>

<code>|* 3 | </code><code>INDEX</code> <code>FAST </code><code>FULL</code> <code>SCAN| IDX_T1 | 172K| 843K| 107 (1)| 00:00:02 |</code>

<code>......省略部分輸出</code>

<code>scott@ORCL></code><code>set</code> <code>autotrace traceonly</code>

<code>Execution Plan</code>

從上面顯示内容可以看到，使用SET AUTOTRACE ON得到的執行計劃和之前explain plan得到的執行計劃也是一模一樣的，即此時使用SET AUTOTRACE ON所得到的執行計劃也是不準的。

另外，如果目标SQL的執行計劃已經被age out出Shared Pool了，此時如何得到SQL的真實執行計劃呢？

如果是Oracle 10g 及其以上版本，該SQL的執行計劃已經被Oracle捕獲并存儲到了AWR Repository中，則可以使用AWR SQL報告來得到真實的曆史執行計劃。

如果是Oracle 9i，通常情況下已經沒有辦法再得到該SQL的執行計劃，除非額外部署了Statspack報告，并且采集Statspack報告的level值大于或等于6。

使用AWR SQL報告來得到真實的曆史執行計劃參考：http://hbxztc.blog.51cto.com/1587495/1897981

參考《基于Oracle的SQL優化》

本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1899175，如需轉載請自行聯系原作者

Oracle中如何得到真實的執行計劃

繼續閱讀

龍珠訓練營task04

Perl與網絡監控

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List