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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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语句执行问题的前提。