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,'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,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statisti
cs */ count(*)%'

-- 获取对应的执行计划
SQL> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p','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):
-----------------------------------------------------------