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語句執行問題的前提。

explain plan VS execution plan

相關文章