Oracle中使用DBMS_XPLAN处理执行计划详解
dbms_xplan是oracle提供的一个用于查看sql计划，包括执行计划和解释计划的包；在以前查看sql执行计划的时候，我都是使用set autotrace命令，不过现在看来，dbms_xplan包给出了更加简化的获取和显示计划的方式。
这5个函数分别对应不同的显示计划的方式，dbms_xplan包不仅可以获取解释计划，它还可以用来输出存储在awr，sql调试集，缓存的sql游标，以及sql基线中的语句计划，实现如上的功能，通常会用到一下5个方法：
1.display
2.display_awr
3.display_cursor
4.display_plan
5.display_sql_plan_baseline
6.display_sqlset
下面将重点讨论关于dbms_xplan包在解释计划和执行计划上的应用。
来看一个经常使用的查看某条语句的解释计划示例：
sql> explain plan for select * from scott.emp;
explained.
sql> select * from table(dbms_xplan.display);
plan_table_output
--------------------------------------------------------------------------------
plan hash value: 3956160932
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 14 | 532 | 3 (0)| 00:00:01 |
| 1 | table access full| emp | 14 | 532 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
-- 对应autotrace实现
sql> set autotrace traceonly explain
上例中使用了dbms_xplan.display方法来显示plan_table中保存的解释计划，如果想要显示执行计划，就需要使用到dmbs_xplan.display_cursor方法了，dmbs_xplan.display_cursor调用签名：
dbms_xplan.display_cursor(
sql_id in varchar2 default null,
child_number in number default null,
format in varchar2 default 'typical');
sql_id表示存储在cursor cache中的sql语句的id，child_number用于指示缓存sql语句计划的子id，format参数用于控制包含在输出中的信息类型，官档的参数如下：
1.basic: 显示最少的信息，只包括操作类型，id名称和选项。
2.typical: 默认值，显示相关信息以及某些附加的显示选项，如分区和并发使用等。
3.serial: 与typical类型相似，区别是它不包括并发的信息，即使是并行执行的计划。
4.all: 显示最多的信息，包含了typical的全部以及更多的附加信息,如别名和远程调用等。
除了以上的基本的四种输出格式外，format还有一些附加的选项可用于定制化输出行为，使用中可以通过逗号和空格分隔来声明多个关键字，同时可以使用”+”和”-”符号来包含或排除相应的显示元素，这些附加的选项在官档中也有记载：
1.rows – 显示被优化器估算的记录的行号
2.bytes – 显示优化器估算的字节数
3.cost – 显示优化器计算的成本信息
4.partition – 显示分区的分割信息
5.parallel – 显示并行执行信息
6.predicate – 显示谓语
7.projection – 显示列投影部分(每一行的那些列被传递给其父列已经这些列的大小)
8.alias – 显示查询块名称已经对象别名
9.remote – 显示分布式查询信息
10.note – 显示注释
11.iostats – 显示游标执行的io统计信息
12.memstats – 为内存密集运算如散列联结，排序，或一些类型的位图运算显示内存管理统计信息
13.allstats – 与'iostats memstats'等价
14.last – 显示最后执行的执行计划统计信息，默认显示为all类型，并且可以累积。
以上的参数同样适用于解释计划的display方法。
示例部分：
一、使用display_cursor方法查看最近一条语句的执行计划
sql> select /*+ gather_plan_statistics */ count(*) from scott.emp;
count(*)
----------
14
sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
plan_table_output
-------------------------------------------------------------------------------------------------------
sql_id f9qyz8s3c2c02, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from scott.emp
plan hash value: 2937609675
-------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers |
-------------------------------------------------------------------------------------
| 0 | select statement | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | sort aggregate | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | index full scan| pk_emp | 1 | 14 | 14 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------
14 rows selected.
使用dbms_xplan.display_cursor(null,null,'allstats last')时，将sql_id和child_number设置成null，表示获取上一条执行语句的执行计划；注意上面的例子中一定要指定gather_plan_statistics提示或者手动设置数据库statistics_level参数为all来使得其抓取行数据源的执行统计信息，这些信息包括行数，一直读取次数，物理读次数，物理写次数以及运算在一行数据上耗费的运行时间，如果没有指定该提示，就不会有a-rows,a-time,buffers这三列信息。
二、获取某条指定语句的执行计划
sql> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);
count(*)
----------
9
-- 通过v$sql视图查询到sql语句的sql_id和child_number
sql> select sql_id,child_number,sql_text from v$sql
2 where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';
sql_id child_number sql_text
------------- ------------ ----------------------------------------------------------------------------------------------------
5qxmkvh40yw0p 0 select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100)
bqjrnskvpv51n 0 select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statisti
cs */ count(*)%'
-- 获取对应的执行计划
sql> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'allstats last'));
plan_table_output
----------------------------------------------------------------------------------------------------
sql_id 5qxmkvh40yw0p, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.employees where
department_id in (90, 100)
plan hash value: 4167091351
--------------------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers |
--------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | sort aggregate | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | inlist iterator | | 1 | | 9 |00:00:00.01 | 2 |
|* 3 | index range scan| emp_department_ix | 2 | 9 | 9 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
3 - access(("department_id"=90 or "department_id"=100))
21 rows selected.
三、通过format参数定制执行计划输出信息
-- 使用all来显示解释计划的全部信息
sql> explain plan for
2 select * from emp e, dept d
3 where e.deptno = d.deptno
4 and e.ename = 'jones' ;
explained.
sql> select * from table(dbms_xplan.display(format=>'all'));
plan_table_output
----------------------------------------------------------------------------------------------------
plan hash value: 3625962092
----------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
----------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 58 | 4 (0)| 00:00:01 |
| 1 | nested loops | | | | | |
| 2 | nested loops | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | table access full | emp | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | index unique scan | pk_dept | 1 | | 0 (0)| 00:00:01 |
| 5 | table access by index rowid| dept | 1 | 20 | 1 (0)| 00:00:01 |
query block name / object alias (identified by operation id):
-------------------------------------------------------------
1 - sel$1
3 - sel$1 / e@sel$1
4 - sel$1 / d@sel$1
5 - sel$1 / d@sel$1
predicate information (identified by operation id):
3 - filter("e"."ename"='jones')
4 - access("e"."deptno"="d"."deptno")
column projection information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "e"."empno"[number,22], "e"."ename"[varchar2,10],
"e"."job"[varchar2,9], "e"."mgr"[number,22], "e"."hiredate"[date,7],
"e"."sal"[number,22], "e"."comm"[number,22], "e"."deptno"[number,22],
"d"."deptno"[number,22], "d"."dname"[varchar2,14], "d"."loc"[varchar2,13]
2 - (#keys=0) "e"."empno"[number,22], "e"."ename"[varchar2,10],
"e"."job"[varchar2,9], "e"."mgr"[number,22], "e"."hiredate"[date,7],
"e"."sal"[number,22], "e"."comm"[number,22], "e"."deptno"[number,22],
"d".rowid[rowid,10], "d"."deptno"[number,22]
3 - "e"."empno"[number,22], "e"."ename"[varchar2,10], "e"."job"[varchar2,9],
"e"."mgr"[number,22], "e"."hiredate"[date,7], "e"."sal"[number,22],
"e"."comm"[number,22], "e"."deptno"[number,22]
4 - "d".rowid[rowid,10], "d"."deptno"[number,22]
5 - "d"."dname"[varchar2,14], "d"."loc"[varchar2,13]
43 rows selected.
-- 去除执行计划上的字节数和成本统计信息
sql> select empno, ename from emp e, dept d
2 where e.deptno = d.deptno
3 and e.ename = 'jones' ;
empno ename
---------- ----------
7566 jones
sql> select * from table(dbms_xplan.display_cursor(null,null,format=>'allstats last -cost -bytes'));
plan_table_output
----------------------------------------------------------------------------------------------------
sql_id 3mypf7d6npa97, child number 1
-------------------------------------
select empno, ename from emp e, dept d where e.deptno = d.deptno and
e.ename = 'jones'
plan hash value: 3956160932
------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers |
------------------------------------------------------------------------------------
| 0 | select statement | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | table access full| emp | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(("e"."ename"='jones' and "e"."deptno" is not null))
19 rows selected.
-- 另一种选项，窥视绑定变量的值，非常方便！！
sql> variable v_empno number
sql> exec :v_empno := 7566 ;
pl/sql procedure successfully completed.
sql> select * from emp where empno = :v_empno ;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 jones manager 7839 02-apr-81 2975 20
sql> select * from table(dbms_xplan.display_cursor(null,null,format=>'+peeked_binds'));
plan_table_output
----------------------------------------------------------------------------------------------------
sql_id 9q17w9umt58m7, child number 0
-------------------------------------
select * from emp where empno = :v_empno
plan hash value: 2949544139
--------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------------------
| 0 | select statement | | | | 1 (100)| |
| 1 | table access by index rowid| emp | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | index unique scan | pk_emp | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
peeked binds (identified by position):
--------------------------------------
1 - :v_empno (number): 7566
predicate information (identified by operation id):
---------------------------------------------------
plan_table_output
----------------------------------------------------------------------------------------------------
2 - access("empno"=:v_empno)
24 rows selected.
-- 并行查询信息筛选
sql> select /*+ parallel(d, 4) parallel (e, 4) */
2 d.dname, avg(e.sal), max(e.sal)
3 from dept d, emp e
4 where d.deptno = e.deptno
5 group by d.dname
6 order by max(e.sal), avg(e.sal) desc;
dname avg(e.sal) max(e.sal)
-------------- ---------- ----------
sales 1566.66667 2850
research 2175 3000
accounting 2916.66667 5000
sql> select * from table(dbms_xplan.display_cursor(null,null,'typical -bytes -cost'));
plan_table_output
----------------------------------------------------------------------------------------------------
sql_id gahr597f78j0d, child number 0
-------------------------------------
select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
d.dname order by max(e.sal), avg(e.sal) desc
plan hash value: 3078011448
--------------------------------------------------------------------------------------------------
| id | operation | name | rows | time | tq |in-out| pq distrib |
--------------------------------------------------------------------------------------------------
plan_table_output
----------------------------------------------------------------------------------------------------
| 0 | select statement | | | | | | |
| 1 | px coordinator | | | | | | |
| 2 | px send qc (order) | :tq10004 | 4 | 00:00:01 | q1,04 | p->s | qc (order) |
| 3 | sort order by | | 4 | 00:00:01 | q1,04 | pcwp | |
| 4 | px receive | | 4 | 00:00:01 | q1,04 | pcwp | |
| 5 | px send range | :tq10003 | 4 | 00:00:01 | q1,03 | p->p | range |
| 6 | hash group by | | 4 | 00:00:01 | q1,03 | pcwp | |
| 7 | px receive | | 14 | 00:00:01 | q1,03 | pcwp | |
| 8 | px send hash | :tq10002 | 14 | 00:00:01 | q1,02 | p->p | hash |
|* 9 | hash join buffered | | 14 | 00:00:01 | q1,02 | pcwp | |
| 10 | px receive | | 4 | 00:00:01 | q1,02 | pcwp | |
| 11 | px send hash | :tq10000 | 4 | 00:00:01 | q1,00 | p->p | hash |
| 12 | px block iterator | | 4 | 00:00:01 | q1,00 | pcwc | |
|* 13 | table access full| dept | 4 | 00:00:01 | q1,00 | pcwp | |
| 14 | px receive | | 14 | 00:00:01 | q1,02 | pcwp | |
| 15 | px send hash | :tq10001 | 14 | 00:00:01 | q1,01 | p->p | hash |
| 16 | px block iterator | | 14 | 00:00:01 | q1,01 | pcwc | |
|* 17 | table access full| emp | 14 | 00:00:01 | q1,01 | pcwp | |
--------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
9 - access("d"."deptno"="e"."deptno")
13 - access(:z>=:z and :z<=:z) 17 - access(:z>=:z and :z<=:z)
38 rows selected.
下一篇： C#中的递归APS和CPS模式详解