轉:Oracle中檢視已執行sql的執行計劃---dbms_xplan.display_cursor

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

[@more@]

首先看看該函式的語法：
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.