ITPub博客

首页 > Linux操作系统 > Linux操作系统 > explain plan VS execution plan

explain plan VS execution plan

原创 Linux操作系统 作者:pingley 时间:2012-05-14 22:07:08 0 删除 编辑
explain plan VS execution plan
explain plan 翻译成中文应该称作解释计划比较恰当。是oracle
对指定的SQL语句给出的理论上的或者称作预期的执行计划。
execution plan 翻译成中文应该称作执行计划比较恰当。是oracle
对指定的SQL语句采取的实际的执行计划。很多时候我们并不做这种区分,统一把两者称作执行计划,并且把两者当成是相同的东西,在多数情况下可以认为explain plan 等于 execution plan。
oracle 数据库也是一个“言行不一的人”。那就是理论的执行计划不总是和实际的执行计划相同。下面创建一个测试表,并往其中填充测试数据。
SQL> edit
Wrote file afiedt.buf
  1  create table test_plan
  2  ( id varchar2(10) primary key,
  3*   object_name varchar2(40))
SQL> /
Table created.
SQL> insert into test_plan
  2  select object_id,object_name
  3  from dba_objects;
65558 rows created.
创建一个host variable ,供后续使用。为了后续的演示,这一步不可以省略。
SQL> variable obj_id number
SQL> exec :obj_id := 100;
PL/SQL procedure successfully completed.
SQL> print :obj_id
    OBJ_ID
----------
       100
收集对象的统计信息,以便CBO可以有足够的信息来给出“正确”的执行计划。
SQL> exec dbms_stats.gather_table_stats(user,'test_plan',cascade=>true);
PL/SQL procedure successfully completed.
使用autotrace 家族的命令来得到执行计划(实际上是解释计划,理论上的执行计划。)可以使用的是索引扫描,似乎合情合理。oracle是否真的这么做那就说不准了。后面的几个例子都是通过使用autotrace 家族的命令来的到的解释计划,解释计划的统计信息等,根据使用的autotrace 命令的不同,输出的结果也不同,但是无一例外这些都是解释计划相关的信息。
SQL> set autotrace on explain
SQL> select *
  2  from test_plan
  3  where id = :obj_id;
ID                   OBJECT_NAME
-------------------- ----------------------------------------------------------------------
100                  ORA$BASE
Execution Plan
----------------------------------------------------------
Plan hash value: 3457654460
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007392 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:OBJ_ID)
SQL> set autotrace traceonly
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3457654460
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Ti
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    31 |     2   (0)| 00
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN   |     1 |    31 |     2   (0)| 00
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007392 |     1 |       |     1   (0)| 00
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:OBJ_ID)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        330  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> set autotrace on statistics
SQL> /
ID                   OBJECT_NAME
-------------------- --------------------------------------
100                  ORA$BASE
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        330  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> set autotrace on
SQL> /
ID                   OBJECT_NAME
-------------------- ----------------------------------------------------------------------
100                  ORA$BASE
Execution Plan
----------------------------------------------------------
Plan hash value: 3457654460
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007392 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:OBJ_ID)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        330  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
使用explain plan 命令得到了也是解释计划。
SQL> explain plan for
  2   select *
  3   from test_plan
  4   where id = :obj_id;
Explained.
SQL> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 3457654460
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007392 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
   2 - access("ID"=:OBJ_ID)
到这边,我们可以知道autotrace 的一系列命令和explain plan 是做“高仿”的给出的并不是实际的执行计划。那实际的执行计划可以通过 dbms_xplan.display_cursor 来获得。
14 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  3qyycjg6g8058, child number 0
-------------------------------------
 select /*+ gather_plan_statistics*/ *  from test_plan  where id =:obj_id
Plan hash value: 289916773
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |      1 |00:00:00.01 |     330 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST_PLAN |      1 |      1 |      1 |00:00:00.01 |     330 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("ID")=:OBJ_ID)
从输出的对比我们可以知道实际上SQL语句:
 select *  from test_plan  where id =:obj_id;
在执行的时候,并不会使用索引访问,而是使用全表扫描。产生这个explain plan  和 execution plan不同的原因本质上是他们是在不同的环境下产生的,解释计划不会考虑绑定变量的数据类型和值,所以的绑定变量都当在varchar2,所以就有了我们上面在解释计划中看到的:
2 - access("ID"=:OBJ_ID)
而实际上在执行计划中是:
filter(TO_NUMBER("ID")=:OBJ_ID)
oracle在执行该SQL语句的时候会把id 列转换为 number 类型来和绑定变量的值相比较。
小结:明白explain plan (解释计划)和execution plan (执行计划)的区别的意义是正确标识出SQL语句执行问题的前提。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26110315/viewspace-723569/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 关于oracle commit
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    717598