如何得到真實的執行計劃

    通常，我們可以使用如下四種方法來得到目標sql的執行計劃：

（1）explain plan命令

（2）dbms_xplan包

（3）sqlplus中的autotrace開關

（4）10046事件

    這其中除了第四種方法之外，其他三種方法得到的執行計劃都可能是不準確的。在oracle數據庫中判斷得到的執行計劃是否準確，就是看目標sql是否被真正執行，真正執行過的sql所對應的執行計劃就是準確的，反之則可能不準。注意，這里判斷原則從嚴格意義上來說并不適用于autotrace開關，因為所有使用autotrace開關所顯示的執行計劃都可能是不準的，即使對應的目標sql實際上上已經執行過。

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

    對使用第一種方法（explain plan）得到的執行計劃而言，因為此時目標sql并沒有被實際執行，所以用該方法得到的執行計劃有可能是不準的，尤其在目標sql包含綁定變量的時候。在默認開啟綁定變量窺探（bind peeking）的情況下，對含綁定變量的目標sql使用explain plan得到執行計劃只是一個半成品，oracle在隨后對該sql的綁定變量進行窺探后就得到了這些綁定變量具體的值，此時oracle很可能會隨上述半成品的執行計劃做調整，一旦做了調整，使用explain plan命令得到的執行計劃就不準了。

    對于使用第二種方法，針對不同的應用場景，你可以選擇如下四種方式中的一種：

    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都已經被實際執行過了。

    對于使用第三種方法（sqlplus中的autotrace開關）而言，你可以選擇執行如下三種方式中一種來開啟autotrace開關

    set autotrace on（set antot on）

    set autotrace traceonly（set autot trace）

    set autotrace traceonly explain（set autot trace exp）

    上述三種方式中，當使用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 traceonly explain的觀點。先正常執行一次如下sql：

    SQL> select count(*) from emp where ename='JAMES';

      COUNT(*)

    ---------- 1

    從如下查詢結果中可以看到上述sql所對應的executions的值為1，這說明oracle剛才確實執行了一次上述sql

    SQL> select sql_text,executions from v$sqlarea where sql_text like 'select count(*)     from emp%';

     SQL_TEXT EXECUTIONS

-------------------------------------------------------------------------------- ----------

    select count(*) from emp where ename='JAMES'  1

    現在清空shared pool

    SQL> alter system flush shared_pool;

    System altered.

    從如下查詢結果中可以看到上述sql所對應的shared cursor現在已經不在shared pool里了

    SQL> select sql_text,executions from v$sqlarea where sql_text like 'select count(*) from emp%';

no rows selected

    在當前session中已traceonly  explain方式打開autotrace后執行上述sql

SQL> set autotrace traceonly explain;

SQL> select count(*) from scott.emp where ename='JAMES'

  2  ;

Execution Plan

----------------------------------------------------------

Plan hash value: 2083865914

---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |  | 1 | 6 | 3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |  | 1 | 6 |       |  |

|*  2 |   TABLE ACCESS FULL| EMP  | 1 | 6 | 3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("ENAME"='JAMES')

 我們再次查詢v$sqlare

SQL> select sql_text,executions from v$sqlarea where sql_text like 'select count(*) from scott.emp%';

SQL_TEXT EXECUTIONS

-------------------------------------------------------------------------------- ----------

select count(*) from scott.emp where ename='JAMES'  0

    從上述查詢結果中可以看到該select 語句所對應的EXECUTIONS為0，這說明oracle剛才確實只解析了該select句但并沒有實際執行它們。證明上述觀點（當使用set autot trace exp時，如果執行的是select語句，則該select語句并沒有被oracle實際執行）

   接著，在當前session中執行如下DML語句：

SQL> delete from scott.emp where ename='JAMES';

1 row deleted.

Execution Plan

----------------------------------------------------------

Plan hash value: 161811703

---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------

|   0 | DELETE STATEMENT   |  | 1 |    13 | 3   (0)| 00:00:01 |

|   1 |  DELETE   | EMP  |  |  |       |  |

|*  2 |   TABLE ACCESS FULL| EMP  | 1 |    13 | 3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("ENAME"='JAMES')

 從查詢結果可以看到，上述DML語句已經被真正執行了：

SQL> select count(*) from scott.emp where ename='JAMES';

  COUNT(*)

----------

0

SQL> select sql_text,executions from v$sqlarea where sql_text like 'delete from scott.emp%';

SQL_TEXT EXECUTIONS

-------------------------------------------------------------------------------- ----------

delete from scott.emp where ename='JAMES'  1

 從上述實例中我們可以看出使用set autotrace traceonly explain后執行DML語句，該DML語句確實是會被oracle實際執行的，所以在使用set autotrace on，set autotrace traceonly 和set autotrace traceonly explain來獲得DML語句的執行計劃時要小心，因為這些DML語句實際上已經被執行了。

    這里需要特別說明的是，雖然使用set autot 命令后目標sql實際上已經執行過了，但所有使用set autotrace命令（包括 set autotrace on，set autotrace traceonly，set autotrace traceonly explain）所得到的執行計劃都可能是不準的，因為使用set autotrace命令所顯示的執行計劃都是來源于調用explain plan命令。

    我們來看一個使用explain plan命令和set autotrace命令后得到的執行計劃并不是目標sql真實執行計劃的實例。創建一個測試表T1并插入一些數據：

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

87205 rows created.

SQL> commit;

Commit complete.

現在表T1的數據量是17萬多條

SQL> select count(*) from t1;

  COUNT(*)

----------

    174410

在表T1的列object_id上創建一個單鍵值的B樹索引IDX_T1

SQL> create index idx_t1 on t1(object_id);

Index created.

對表T1收集一個統計信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

創建兩個綁定變量x和y，分別對他們賦值0和100000

SQL> var x number;

SQL> var y number;

SQL> exec :x=0;

SQL> exec :x:=0;

PL/SQL procedure successfully completed.

SQL> exec :y:=100000;

PL/SQL procedure successfully completed.

用explain plan產生以下sql的執行計劃：

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

Explained.

SQL> 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 | 436 |  2180 |  3   (0)| 00:00:01 |

-----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

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.

從上述結果可以看出，使用explain plan命令得到的執行計劃顯示目標sql走的是對索引IDX_T1索引范圍掃描。

但是實際情況時怎樣的？我們實際執行該sql：

SQL> exec :x:=0;

PL/SQL procedure successfully completed.

SQL> exec :y:=10000;

PL/SQL procedure successfully completed.

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

  COUNT(*)

----------

     19610

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

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

|*  2 |   FILTER       | | | |     | |

|*  3 |    INDEX FAST FULL SCAN| IDX_T1 |   174K|   851K|   107   (1)| 00:00:01 |

---------------------------------------------------------------------------------

52 rows selected.

 從上述顯示內容可以看到，現在目標sql的執行計劃實際上走的是索引IDX_T1的索引快速全掃描，這才是目標sql真實的執行計劃，幾剛才使用explain plan命令得到的執行計劃不是準確的。

    同樣方法可以得到用set autotrace on方法得到的執行計劃也不是準確的。