Oracle如何檢視真實執行計劃（一）

大家可能之前都瞭解過執行計劃的檢視方式，比如explain plan for，set autot ...

但是其實這些方式檢視的執行計劃並非真實執行計劃，而是oracle根據統計資訊等資訊透過CBO計算出來的執行計劃，

都來自於PLAN_TABLE，這是一個會話級的臨時表，其儲存的執行計劃往往與真實執行計劃相差甚遠。

本文介紹如何檢視執行SQL的真實執行計劃。

方法一：使用statistics_level = all的方法檢視

1、更改系統統計收集引數為all，session級別即可，

注意：all是一個全面收集,包括 OS以及sql執行路徑方面的一些統計資訊,相對來說比較耗費資源，因此千萬不能設定全域性為all，會話級別使用即可。

SQL> alter session set statistics_level = all;
Session altered.

2、執行待檢視執行計劃的SQL語句

SQL> select a.email from hr.employees a where rownum<=5;
EMAIL
-------------------------
ABANDA
ABULL
ACABRIO
AERRAZUR
AFRIPP

3、執行如下命令檢視真實執行計劃

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aw1d4an00b4za, child number 0
-------------------------------------
select a.email from hr.employees a where rownum<=5
Plan hash value: 2484301839
-------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |      1 |        |      5 |00:00:00.01 |       2 |
|*  1 |  COUNT STOPKEY   |              |      1 |        |      5 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |      1 |      5 |      5 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
19 rows selected.

方法二：新增hint提示符方式檢視

1、在執行SQL中新增加/*+ gather_plan_statistics */ hint，例如：

SQL> select  /*+ gather_plan_statistics */  a.email from hr.employees a where rownum<=5;
EMAIL
-------------------------
ABANDA
ABULL
ACABRIO
AERRAZUR
AFRIPP

2、使用如下命令檢視SQL語句真實執行計劃

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6qu4asp22hx3p, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */  a.email from hr.employees a
where rownum<=5
Plan hash value: 2484301839
-------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |      1 |        |      5 |00:00:00.01 |       2 |
|*  1 |  COUNT STOPKEY   |              |      1 |        |      5 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |      1 |      5 |      5 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
20 rows selected.