Oracle中檢視已執行sql的執行計劃

有時候我們可能會希望檢視一條已經執行過的sql的執行計劃，常用的方式有兩種：a,set autotrace後再重新執行一遍，不過重新執行可能會浪費時間，而且有些語句也不允許(例如修改操作的語句)，或者查詢v$sql_plan檢視，但v$檢視的可讀性又不是那麼好，這裡提供一個新方式，透過dbms_xplan.display_cursor來獲取執行過的sql的執行計劃。

首先看看該函式的語法：
DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL,
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

由上可知，我們至少需要找到執行過sql的sql_id，該引數可以從v$sql檢視中找到。

下面，舉個例子吧，執行一個簡單查詢：
SQL> select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id;

  COUNT(0)
----------
    118908

如果我們想獲取該語句的實際執行計劃，透過下列步驟：

1、查詢v$sql檢視，找到該語句的sql_id(注意喲，必須要確保你要查詢的sql語句還在shared pool)：
SQL> select sql_id from v$sql where sql_text=
  2  'select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id';

SQL_ID
-------------
c9cxqvr3q4tjd

2、呼叫dbms_xplan包，檢視該語句執行時的實現執行計劃：
SQL> select * from table(dbms_xplan.display_cursor('c9cxqvr3q4tjd'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c9cxqvr3q4tjd, child number 0
-------------------------------------
select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id

Plan hash value: 2559475106

-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                          |       |       |       |   750 (100)|          |
|   1 |  SORT AGGREGATE        |                          |     1 |    50 |       |            |          |
|*  2 |   HASH JOIN            |                          |   118K|  5804K|  4096K|   750   (1)| 00:00:11 |
|   3 |    INDEX FAST FULL SCAN| PK_CAT_DRUG              |   112K|  2758K|       |   186   (1)| 00:00:03 |
|   4 |    INDEX FAST FULL SCAN| TU_CAT_PRODUCT_MED_CHECK |   118K|  2902K|       |   212   (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CP"."MEDICAL_ID"="CD"."ID")

事實上dbms_xplan.display_cursor也非常靈活，如果執行的統計資訊也被收集的話，還可以顯示出每一步實際的花費時間等資訊，例如：

SQL> select /*+gather_plan_statistics*/ count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id;

  COUNT(0)
----------
    118908

SQL> select sql_id from v$sql where sql_text=
  2  'select /*+gather_plan_statistics*/ count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id';

SQL_ID
-------------
91w1ug6vc9pxh

SQL> select * from table(dbms_xplan.display_cursor('91w1ug6vc9pxh',null,'all iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  91w1ug6vc9pxh, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id

Plan hash value: 2559475106

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |                          |      1 |      1 |    50 |       |            |          |      1 |00:00:00.18 | 595 |
|*  2 |   HASH JOIN            |                          |      1 |    118K|  5804K|  4096K|   750   (1)| 00:00:11 |    118K|00:00:00.33 | 595 |
|   3 |    INDEX FAST FULL SCAN| PK_CAT_DRUG              |      1 |    112K|  2758K|       |   186   (1)| 00:00:03 |    112K|00:00:00.01 | 278 |
|   4 |    INDEX FAST FULL SCAN| TU_CAT_PRODUCT_MED_CHECK |      1 |    118K|  2902K|       |   212   (1)| 00:00:03 |    118K|00:00:00.01 | 317 |
-----------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 /
   4 - SEL$1 /

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CP"."MEDICAL_ID"="CD"."ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=1)
   3 - "CD"."ID"[CHARACTER,24]
   4 - "CP"."MEDICAL_ID"[CHARACTER,24]

35 rows selected.