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

Oracle中查看已执行sql的执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 上一篇 / 下一篇 2008-09-12 10:54:07 / 个人分类:原创笔记 查看( 771 ) / 评论( 8 ) / 评分( 15 / 0 ) 有时候我们可能会希望查看一条已经执行过的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 /CD@SEL$1 4 - SEL$1 /CP@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.