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

1、Oracle中查看已执行sql的执行计划 2008-09-12 10:54:07 分类： Linux 有时候我们可能会希望查看一条已经执行过的sql的执行计划，常用的方式有两种：a,set autotrace后再重新执行一遍，不过重新执行可能会浪费时间，而且有些语句也不允许(例如修改操作的语句，或者查询v$sql_plan视图，但v$视图的可读性又不是那么好，这里提供一个新方式，通过dbms_xplan.display_cursor来获取执行过的sql的执行计划。 首先看看该函数的语法： DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT

2、 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_

3、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&

4、#39; 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 AGGREGA

5、TE | | 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:

6、- 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 sq

7、l_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 - SQ

8、L_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

9、| | 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 | 1

10、18K| 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 / CDSEL$1 4 - SEL$1 / CPSEL$1 Predicate Information (identified by operation id: - 2 - access("CP"."MEDICAL_ID"="CD"."ID&q

11、uot; 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. 查看执行计划（一） 2014-02-19 14:31:49 分类： Oracle 查看执行计划的几种方式： 1）explain plan 命令 语法是expla

12、in plan for + 目标sql select * from table(dbms_xplan.display ; SQL> explain plan for select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno; 已解释。 SQL> set linesize 200 SQL> select * from table(dbms_xplan.display; PLAN_TABLE_OUTPUT - Plan hash value: 844388907 -

13、 | Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time | - | 0 | SELECT STATEMENT | | 14 | 364 | 6 (17| 00:00:01 | | 1 | MERGE JOIN | | 14 | 364 | 6 (17| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0| 00:00:01 | |* 4 |

14、 SORT JOIN | | 14 | 182 | 4 (25| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0| 00:00:01 | PLAN_TABLE_OUTPUT - - Predicate Information (identified by operation id: - 4 - access("T11"."DEPTNO"="T12"."DEPTNO" filter("T11"."DEPTNO"

15、;="T12"."DEPTNO" 已选择18行。 这种方式和在PL/SQL DEVELOPER当中使用F5得到执行的执行计划一模一样。 2）DBMS_XPLAN包 SQL> select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno; EMPNO ENAME DNAME - - - 7782 CLARK ACCOUNTING 7839 KING ACCOUNTING 7934 MILLER ACCOUNTING 7566 JONES R

16、ESEARCH 7902 FORD RESEARCH 7876 ADAMS RESEARCH 7369 SMITH RESEARCH 7788 SCOTT RESEARCH 7521 WARD SALES 7844 TURNER SALES 7499 ALLEN SALES EMPNO ENAME DNAME - - - 7900 JAMES SALES 7698 BLAKE SALES 7654 MARTIN SALES 已选择14行。 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced&

17、#39; PLAN_TABLE_OUTPUT - SQL_ID fvp57hhd1vfmp, child number 0 - select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno Plan hash value: 844388907 - | Id | Operation | Name "SEL$1" ("DEPT"."DEPTNO" FULL("SEL$1" "T11"&qu

18、ot;SEL$1" LEADING("SEL$1" "T12""SEL$1" "T11""SEL$1" USE_MERGE("SEL$1" "T11""SEL$1" END_OUTLINE_DATA */ Predicate Information (identified by operation id: PLAN_TABLE_OUTPUT - - 4 - access("T11"."DEPTNO

19、"="T12"."DEPTNO" filter("T11"."DEPTNO"="T12"."DEPTNO" Column Projection Information (identified by operation id: - 1 - "DNAME"VARCHAR2,14, "EMPNO"NUMBER,22, "ENAME"VARCHAR2,10 2 - "T12"."DEP

20、TNO"NUMBER,22, "DNAME"VARCHAR2,14 3 - "T12".ROWIDROWID,10, "T12"."DEPTNO"NUMBER,22 PLAN_TABLE_OUTPUT - 4 - (#keys=1 "T11"."DEPTNO"NUMBER,22, "EMPNO"NUMBER,22, "ENAME"VARCHAR2,10 5 - "EMPNO"NUMBER,22, "

21、;ENAME"VARCHAR2,10, "T11"."DEPTNO"NUMBER,22 已选择59行。 4）select * from table(dbms_xplan.display_awr('sql_id'"-它用于查询指定SQL的所有历史执行计划。 -使用方法2和3能够得到sql执行计划的前提条件是该执行计划还在共享池中，而如果该SQL的执行计划已经被刷出共享池，那么只要该SQL的执行计划被ORACLE采集到AWR Repository中， 就可以用该方法来查看。 SQL> select sql_text,

22、sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(* from t1%' SQL_TEXT - SQL_ID VERSION_COUNT EXECUTIONS - - - select count(* from t1 5bc0v4my7dvr5 1 3 SQL> exec dbms_workload_repository.create_snapshot; PL/SQL 过程已成功完成。 SQL> alter system flush shared_pool; 系统已更改。 SQL> select *from table(dbms_xplan.displ