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 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.
查看执行计划（一） 2014-02-19 14:31:49
分类： Oracle
查看执行计划的几种方式：
1）explain plan 命令
语法是explain 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
----------------------------------------------------------------------------------------
| 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 | 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"="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 RESEARCH
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'));
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
| Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
| 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 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
2 - SEL$1 / T12@SEL$1
3 - SEL$1 / T12@SEL$1
5 - SEL$1 / T11@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T12"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "T11"@"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"="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"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
3 - "T12".ROWID[ROWID,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], "ENAME"[VARCHAR2,10], "T11"."DEPTNO"[NUMBER,22]
已选择59行。
--这种方法在SQLPLUS中查看刚刚执行过的SQL的执行计划。
--dbms_xplan.display_cursor传入的前两个参数的值均为null,第三个参数是"advanced"，第三个参数也
可以是"all",
”all“得到的显示结果，少了"Outline data"部分的内容。
3）select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced');
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select empno,ename%';
SQL_TEXT
-----------------------------------------------------------------------------------------------------------
SQL_ID HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select empno,ename%'
b43838yvpqmdh 3076214192 0
select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno
fvp57hhd1vfmp 438155893 0
SQL> select * from table(dbms_xplan.display_cursor('fvp57hhd1vfmp',0,'advanced'));
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 | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| 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 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
2 - SEL$1 / T12@SEL$1
3 - SEL$1 / T12@SEL$1
5 - SEL$1 / T11@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T12"@
"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "T11"@"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"="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"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
3 - "T12".ROWID[ROWID,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], "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,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.display_cursor('5bc0v4my7dvr5',0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 5bc0v4my7dvr5, child number: 0 cannot be found
SQL> select * from table(dbms_xplan.display_cursor('5bc0v4my7dvr5',1,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 5bc0v4my7dvr5, child number: 1 cannot be found
SQL> select * from table(dbms_xplan.display_awr('5bc0v4my7dvr5'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5
--------------------
select count(*) from t1
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (
%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| T1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择18行。