转:通过dbms_xplan.display_cursor来获取执行过的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.