如何得到真实的执行计划
通常,我们可以使用如下四种方法来得到目标sql的执行计划:
创新互联于2013年创立,先为湖口等服务建站,湖口等地企业,进行企业商务咨询服务。为湖口企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
(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_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.
从上述显示内容可以看到,现在目标sql的执行计划实际上走的是索引IDX_T1的索引快速全扫描,这才是目标sql真实的执行计划,几刚才使用explain plan命令得到的执行计划不是准确的。
同样方法可以得到用set autotrace on方法得到的执行计划也不是准确的。
本文名称:如何得到真实的执行计划
本文链接:http://scyanting.com/article/poidsc.html