oracle select 并行_Oracle中使用DBMS_XPLAN处理执行计划详解

概述

DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包；在以前查看SQL执行计划的时候，我都是直接使用set autotrace命令，不过DBMS_XPLAN包也给出了更加简化的获取和显示计划的方式。

---

DBMS_XPLAN包

这几个函数分别对应不同的显示计划的方式，DBMS_XPLAN包不仅可以获取解释计划，它还可以用来输出存储在AWR，SQL调试集，缓存的SQL游标，以及SQL基线中的语句计划，实现如上的功能，通常会用到一下几个方法：

1.DISPLAY

2.DISPLAY_AWR

3.DISPLAY_CURSOR

4.DISPLAY_PLAN

5.DISPLAY_SQL_PLAN_BASELINE

6.DISPLAY_SQLSET

下面看一个经常使用的查看某条语句的解释计划示例：

SQL> explain plan for select * from scott.emp;SQL> select * from table(dbms_xplan.display);-- 对应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方法。

---

实例演示

1、使用display_cursor方法查看最近一条语句的执行计划

SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp; SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

使用dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')时，将sql_id和child_number设置成null，表示获取上一条执行语句的执行计划；注意上面的例子中一定要指定gather_plan_statistics提示或者手动设置数据库STATISTICS_LEVEL参数为ALL来使得其抓取行数据源的执行统计信息，这些信息包括行数，一直读取次数，物理读次数，物理写次数以及运算在一行数据上耗费的运行时间，如果没有指定该提示，就不会有A-Rows,A-Time,Buffers这三列信息。

2、获取某条指定语句的执行计划

SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp where sal in (1250, 1300);-- 通过v$sql视图查询到sql语句的SQL_ID和CHILD_NUMBERSQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';-- 获取对应的执行计划SQL> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'ALLSTATS LAST'));

3、通过format参数定制执行计划输出信息

-- 使用ALL来显示解释计划的全部信息SQL> explain plan for select * from emp e, dept d where e.deptno = d.deptno and e.ename = 'JONES' ;SQL> select * from table(dbms_xplan.display(format=>'ALL'));-- 去除执行计划上的字节数和成本统计信息SQL> select empno, ename from emp e, dept d where e.deptno = d.deptno and e.ename = 'JONES' ;SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -BYTES'));-- 另一种选项，窥视绑定变量的值，非常方便！！SQL> variable v_empno numberSQL> exec :v_empno := 7566 ;SQL> select * from emp where empno = :v_empno ;SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'+PEEKED_BINDS'));-- 并行查询信息筛选SQL> select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal), max(e.sal) from scott.dept d, scott.emp e where d.deptno = e.deptno group by d.dname order by max(e.sal), avg(e.sal) desc;SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL -BYTES -COST'));

显示解释计划的全部信息

并行查询信息筛选

---

查看执行计划有很多方式，大家看自己习惯重点掌握一两种就可以了，后面会分享更多DBA方面的内容，感兴趣的朋友可以关注一下~