Oracle中查看已执行sql的执行计划

jiaoc

　　有时候我们可能会希望查看一条已经执行过的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
　　-----------------------------------------------------------------------------------------------------------

　　|>　　-----------------------------------------------------------------------------------------------------------
　　|   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>　　---------------------------------------------------
　　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
　　-----------------------------------------------------------------------------------------------------------------------------------------------------

　　|>　　-----------------------------------------------------------------------------------------------------------------------------------------------------
　　|   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>　　-------------------------------------------------------------
　　1 - SEL$1
　　3 - SEL$1 / CD@SEL$1
　　4 - SEL$1 / CP@SEL$1

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("CP"."MEDICAL_ID"="CD"."ID")

　　Column Projection Information (identified by operation>　　-----------------------------------------------------------
　　1 - (#keys=0) COUNT(*)[22]
　　2 - (#keys=1)
　　3 - "CD"."ID"[CHARACTER,24]
　　4 - "CP"."MEDICAL_ID"[CHARACTER,24]
　　
　　35 rows selected.