如何得到真實(shí)的執(zhí)行計(jì)劃

    通常，我們可以使用如下四種方法來得到目標(biāo)sql的執(zhí)行計(jì)劃：

（1）explain plan命令

（2）dbms_xplan包

（3）sqlplus中的autotrace開關(guān)

（4）10046事件

    這其中除了第四種方法之外，其他三種方法得到的執(zhí)行計(jì)劃都可能是不準(zhǔn)確的。在oracle數(shù)據(jù)庫中判斷得到的執(zhí)行計(jì)劃是否準(zhǔn)確，就是看目標(biāo)sql是否被真正執(zhí)行，真正執(zhí)行過的sql所對(duì)應(yīng)的執(zhí)行計(jì)劃就是準(zhǔn)確的，反之則可能不準(zhǔn)。注意，這里判斷原則從嚴(yán)格意義上來說并不適用于autotrace開關(guān)，因?yàn)樗惺褂胊utotrace開關(guān)所顯示的執(zhí)行計(jì)劃都可能是不準(zhǔn)的，即使對(duì)應(yīng)的目標(biāo)sql實(shí)際上上已經(jīng)執(zhí)行過。

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

    對(duì)使用第一種方法（explain plan）得到的執(zhí)行計(jì)劃而言，因?yàn)榇藭r(shí)目標(biāo)sql并沒有被實(shí)際執(zhí)行，所以用該方法得到的執(zhí)行計(jì)劃有可能是不準(zhǔn)的，尤其在目標(biāo)sql包含綁定變量的時(shí)候。在默認(rèn)開啟綁定變量窺探（bind peeking）的情況下，對(duì)含綁定變量的目標(biāo)sql使用explain plan得到執(zhí)行計(jì)劃只是一個(gè)半成品，oracle在隨后對(duì)該sql的綁定變量進(jìn)行窺探后就得到了這些綁定變量具體的值，此時(shí)oracle很可能會(huì)隨上述半成品的執(zhí)行計(jì)劃做調(diào)整，一旦做了調(diào)整，使用explain plan命令得到的執(zhí)行計(jì)劃就不準(zhǔn)了。

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

    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'));

    顯然，執(zhí)行 select * from table(dbms_xplan.display)所得到的執(zhí)行計(jì)劃可能是不準(zhǔn)確的，因?yàn)樗皇菗碛胁榭词褂胑xplain plan命令得到的目標(biāo)sql的執(zhí)行計(jì)劃，目標(biāo)sql此時(shí)還沒有被真正執(zhí)行，所以用它得到的執(zhí)行計(jì)劃可能是不準(zhǔn)的。使用剩下的三種方式所得到的執(zhí)行計(jì)劃都是準(zhǔn)的，因?yàn)榇藭r(shí)目標(biāo)sql都已經(jīng)被實(shí)際執(zhí)行過了。

    對(duì)于使用第三種方法（sqlplus中的autotrace開關(guān)）而言，你可以選擇執(zhí)行如下三種方式中一種來開啟autotrace開關(guān)

    set autotrace on（set antot on）

    set autotrace traceonly（set autot trace）

    set autotrace traceonly explain（set autot trace exp）

    上述三種方式中，當(dāng)使用set autotrace on和set autotrace traceonly時(shí)，目標(biāo)sql都已經(jīng)被實(shí)際執(zhí)行過了，正是因?yàn)楸粚?shí)際執(zhí)行過了，所以set autotrace on和set autotrace traceonly的情況下我們能看到目標(biāo)sql的實(shí)際資源消耗情況。當(dāng)使用set autotrace traceonly explain是，如果執(zhí)行時(shí)select語句，則該select語句并沒有被oracle實(shí)際執(zhí)行，但如果執(zhí)行的是DML語句，情況就不一樣了，此時(shí)的DML語句會(huì)被實(shí)際oracle實(shí)際執(zhí)行的。

    我們現(xiàn)在來證明上述關(guān)于set autotrace traceonly explain的觀點(diǎn)。先正常執(zhí)行一次如下sql：

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

      COUNT(*)

    ---------- 1

    從如下查詢結(jié)果中可以看到上述sql所對(duì)應(yīng)的executions的值為1，這說明oracle剛才確實(shí)執(zhí)行了一次上述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

    現(xiàn)在清空shared pool

    SQL> alter system flush shared_pool;

    System altered.

    從如下查詢結(jié)果中可以看到上述sql所對(duì)應(yīng)的shared cursor現(xiàn)在已經(jīng)不在shared pool里了

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

no rows selected

    在當(dāng)前session中已traceonly  explain方式打開autotrace后執(zhí)行上述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')

 我們?cè)俅尾樵僾$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

    從上述查詢結(jié)果中可以看到該select 語句所對(duì)應(yīng)的EXECUTIONS為0，這說明oracle剛才確實(shí)只解析了該select句但并沒有實(shí)際執(zhí)行它們。證明上述觀點(diǎn)（當(dāng)使用set autot trace exp時(shí)，如果執(zhí)行的是select語句，則該select語句并沒有被oracle實(shí)際執(zhí)行）

   接著，在當(dāng)前session中執(zhí)行如下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')

 從查詢結(jié)果可以看到，上述DML語句已經(jīng)被真正執(zhí)行了：

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

 從上述實(shí)例中我們可以看出使用set autotrace traceonly explain后執(zhí)行DML語句，該DML語句確實(shí)是會(huì)被oracle實(shí)際執(zhí)行的，所以在使用set autotrace on，set autotrace traceonly 和set autotrace traceonly explain來獲得DML語句的執(zhí)行計(jì)劃時(shí)要小心，因?yàn)檫@些DML語句實(shí)際上已經(jīng)被執(zhí)行了。

    這里需要特別說明的是，雖然使用set autot 命令后目標(biāo)sql實(shí)際上已經(jīng)執(zhí)行過了，但所有使用set autotrace命令（包括 set autotrace on，set autotrace traceonly，set autotrace traceonly explain）所得到的執(zhí)行計(jì)劃都可能是不準(zhǔn)的，因?yàn)槭褂胹et autotrace命令所顯示的執(zhí)行計(jì)劃都是來源于調(diào)用explain plan命令。

    我們來看一個(gè)使用explain plan命令和set autotrace命令后得到的執(zhí)行計(jì)劃并不是目標(biāo)sql真實(shí)執(zhí)行計(jì)劃的實(shí)例。創(chuàng)建一個(gè)測(cè)試表T1并插入一些數(shù)據(jù)：

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.

現(xiàn)在表T1的數(shù)據(jù)量是17萬多條

SQL> select count(*) from t1;

  COUNT(*)

----------

    174410

在表T1的列object_id上創(chuàng)建一個(gè)單鍵值的B樹索引IDX_T1

SQL> create index idx_t1 on t1(object_id);

Index created.

對(duì)表T1收集一個(gè)統(tǒng)計(jì)信息

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

PL/SQL procedure successfully completed.

創(chuàng)建兩個(gè)綁定變量x和y，分別對(duì)他們賦值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產(chǎn)生以下sql的執(zhí)行計(jì)劃：

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.

從上述結(jié)果可以看出，使用explain plan命令得到的執(zhí)行計(jì)劃顯示目標(biāo)sql走的是對(duì)索引IDX_T1索引范圍掃描。

但是實(shí)際情況時(shí)怎樣的？我們實(shí)際執(zhí)行該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.

 從上述顯示內(nèi)容可以看到，現(xiàn)在目標(biāo)sql的執(zhí)行計(jì)劃實(shí)際上走的是索引IDX_T1的索引快速全掃描，這才是目標(biāo)sql真實(shí)的執(zhí)行計(jì)劃，幾剛才使用explain plan命令得到的執(zhí)行計(jì)劃不是準(zhǔn)確的。

    同樣方法可以得到用set autotrace on方法得到的執(zhí)行計(jì)劃也不是準(zhǔn)確的。