通常，我們可以使用如下四種方法來得到目標(biāo)sql的執(zhí)行計(jì)劃：
創(chuàng)新互聯(lián)于2013年創(chuàng)立，先為湖口等服務(wù)建站，湖口等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為湖口企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。
（1）explain plan命令
（2）dbms_xplan包
（3）sqlplus中的autotrace開關(guān)
（4）10046事件
這其中除了第四種方法之外，其他三種方法得到的執(zhí)行計(jì)劃都可能是不準(zhǔn)確的。在oracle數(shù)據(jù)庫(kù)中判斷得到的執(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)用場(chǎ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語(yǔ)句，則該select語(yǔ)句并沒有被oracle實(shí)際執(zhí)行，但如果執(zhí)行的是DML語(yǔ)句，情況就不一樣了，此時(shí)的DML語(yǔ)句會(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 語(yǔ)句所對(duì)應(yīng)的EXECUTIONS為0，這說明oracle剛才確實(shí)只解析了該select句但并沒有實(shí)際執(zhí)行它們。證明上述觀點(diǎn)（當(dāng)使用set autot trace exp時(shí)，如果執(zhí)行的是select語(yǔ)句，則該select語(yǔ)句并沒有被oracle實(shí)際執(zhí)行）
接著，在當(dāng)前session中執(zhí)行如下DML語(yǔ)句：
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語(yǔ)句已經(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語(yǔ)句，該DML語(yǔ)句確實(shí)是會(huì)被oracle實(shí)際執(zhí)行的，所以在使用set autotrace on，set autotrace traceonly 和set autotrace traceonly explain來獲得DML語(yǔ)句的執(zhí)行計(jì)劃時(shí)要小心，因?yàn)檫@些DML語(yǔ)句實(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_ID9dhu3xk2zu531, 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)確的。
重慶分公司
重慶分公司
