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包

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

1. select * from     table(dbms_xplan.display);

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

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

4. 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命令。

下面使用一個例子證明：

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

Table created.

scott@ORCL>insert into t1 select * from t1;

86885 rows created.

scott@ORCL>commit;

Commit complete.

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

  COUNT(*)
----------
    173770

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

Index created.

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

PL/SQL procedure successfully completed.

scott@ORCL>var x number;
scott@ORCL>var y number;
scott@ORCL>exec :x := 0;

PL/SQL procedure successfully completed.

scott@ORCL>exec :y := 100000;

PL/SQL procedure successfully completed.

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

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2351893609

-----------------------------------------------------------------------------
| Id  | Operation	   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	    |	  1 |	  5 |	  3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	    |	  1 |	  5 |		 |	    |
|*  2 |   FILTER	   |	    |	    |	    |		 |	    |
|*  3 |    INDEX RANGE SCAN| IDX_T1 |	434 |  2170 |	  3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:Y)>=TO_NUMBER(:X))
   3 - access("OBJECT_ID">=TO_NUMBER(:X) AND "OBJECT_ID"<=TO_NUMBER(:Y))

16 rows selected.

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

  COUNT(*)
----------
    173380

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	9dhu3xk2zu531, child number 0
-------------------------------------
select count(*) from t1 where object_id between :x and :y

Plan hash value: 1410530761

---------------------------------------------------------------------------------
| Id  | Operation	       | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	|	|	|   107 (100)|		|
|   1 |  SORT AGGREGATE        |	|     1 |     5 |	     |		|
|*  2 |   FILTER	       |	|	|	|	     |		|
|*  3 |    INDEX FAST FULL SCAN| IDX_T1 |   172K|   843K|   107   (1)| 00:00:02 |
---------------------------------------------------------------------------------

......省略部分輸出

scott@ORCL>set autotrace traceonly
scott@ORCL>select count(*) from t1 where object_id between :x and :y;


Execution Plan
----------------------------------------------------------
Plan hash value: 2351893609

-----------------------------------------------------------------------------
| Id  | Operation	   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	    |	  1 |	  5 |	  3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	    |	  1 |	  5 |		 |	    |
|*  2 |   FILTER	   |	    |	    |	    |		 |	    |
|*  3 |    INDEX RANGE SCAN| IDX_T1 |	434 |  2170 |	  3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

從上面顯示內容可以看到，使用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優化》