时间：2021-03-19 09:45:24 | 栏目：Oracle | 点击：次
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包在解释计划和执行计划上的应用。
来看一个经常使用的查看某条语句的解释计划示例：
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调用签名：
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 ?C 显示被优化器估算的记录的行号
2.BYTES ?C 显示优化器估算的字节数
3.COST ?C 显示优化器计算的成本信息
4.PARTITION ?C 显示分区的分割信息
5.PARALLEL ?C 显示并行执行信息
6.PREDICATE ?C 显示谓语
7.PROJECTION ?C 显示列投影部分(每一行的那些列被传递给其父列已经这些列的大小)
8.ALIAS ?C 显示查询块名称已经对象别名
9.REMOTE ?C 显示分布式查询信息
10.NOTE ?C 显示注释
11.IOSTATS ?C 显示游标执行的IO统计信息
12.MEMSTATS ?C 为内存密集运算如散列联结，排序，或一些类型的位图运算显示内存管理统计信息
13.ALLSTATS ?C 与'IOSTATS MEMSTATS'等价
14.LAST ?C 显示最后执行的执行计划统计信息，默认显示为ALL类型，并且可以累积。
以上的参数同样适用于解释计划的display方法。
示例部分：
一、使用display_cursor方法查看最近一条语句的执行计划
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这三列信息。
二、获取某条指定语句的执行计划
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参数定制执行计划输出信息
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.