一、如何查看执行计划

Oracle数据库里,我们通常可以使用如下方法(包括但不限于)得到目标SQL的执行计划:

  1. explain plan 命令

  2. DBMS_XPLAN

  3. SQLPLUS中的AUTOTRACE开关

  4. 10046事件

  5. 10053事件

  6. AWR报告或Statspack报告

  7. 一些现成的脚本(     display_cursor_9i.sql)

前四种方法使用得比较普遍

1.1 explain plan 命令

      习惯使用PL/SQL Developer的人都知道,按下快捷键F5后就可以显示目标SQL的执行计划,实际上,PL/SQL Developer就调用了explain plan命令,快捷键F5只不过是在explain plan命令上的一层封装而已。

      explain plan命令的语法是依次执行如下两条命令:

  • explain plan for +     目标SQL

  • select * from     table(dbms_xplan.display)

      先使用explain plan命令对目标SQLexplain,再使用“select * from table(dbms_xplan.display)”查看上述使用explain plan命令后得到的执行计划。

explain plan for select empno,ename,dname fromscott.emp,scott.dept where emp.deptno=dept.deptno;

select * from table(dbms_xplan.display);

      explain plan 命令到底做了什么事情呢?在Oracle 10g 及其以上的版本里,如果我们对目标SQL执行explain plan 命令,则Oracle就将解析目标SQL所产生的执行计划的具体执行步骤写入PLAN_TABLE$,随后执行“select * from table(dbms_xplan.display)”只是从PLAN_TABLE$中将这些具体执行步骤以格式化的方式显示出来。PLAN_TABLES$是一个ON COMMIT PRESERVE ROWSGLOBALTEMPORARY TABLE,所以这里Oracle可以做到各个的Session只能看到自己执行的SQL所产生的执行计划,并且各个SessionPLAN_TABLE$写入执行计划的过程互不干扰。

1.2 DBMS_XPLAN

使用DBMS_XPLAN包中的方法是在Oracle数据库中得到目标SQL的执行计划的第二种方法。针对不同的应用场景,你可以选择如下四种方法中的一种:

  1. select * from     table(dbms_xplan.display);

  2. select * from     table(dbms_xplan.display_cursor(null,null,'advanced'));

  3. select * from     table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));

  4. select * from     table(dbms_xplan.display_awr('sql_id'));

方法1是需要与explain plan命令配合使用,上节已经介绍过。

方法2用于在SQLPLUS中查看刚刚执行过的SQL的执行计划。这里针对方法DBMS_XPLAN.DISPLAY_CURSOR所传入的第一个和第二个参数的值均为null,第三个参数值是“advanced”,第三个输入参数的值也可以是“all”,只不过用“advanced”后的显示结果会比“all”显示的结果更详细一些。

set linesize 800 pagesize 900

col plan_table_output for a200

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

select * fromtable(dbms_xplan.display_cursor(null,null,'advanced'));

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

select * fromtable(dbms_xplan.display_cursor(null,null,'all'));

“all”得到的结果与“advanced”的显示结果相比,少了“Outline Data”部分的内容。

方法3用于查看指定SQL的执行计划。这里针对方法DBMS_XPLAN.DISPLAY_CURSOR所传入的第一个参数的值是指定SQLSQL ID或者SQL HASH VALUE,第二个参数的值是要查看的执行计划所在的Chile Cursor Number,第三个参数已经在介绍方法2时已经提到过,这个参数值一般都用“advanced”

selectsql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'selectempno,ename%';

SQL_TEXT                                                                                                                                       SQL_ID             HASH_VALUE CHILD_NUMBER

-------------------------------------------------------------------------------  ------------- ---------- -------------

selectempno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno     3yfu3wh150aqt    38808281          0

sql_id3yfu3wh150aqtSQL HASH_VALUE38808281,对应的ChildCursor Number0

本质上SQL IDSQL HASH_VALUE是一回事,它们是可以互相转换的,这也是方法DBMS_XPLAN.DISPLAY_CURSOR所传入的第一个参数的值可以是SQL ID,也可以是SQLHASH_VALUE的原因。

selectlower(trim('3yfu3wh150aqt')) sql_id,

trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',

substr(lower(trim('3yfu3wh150aqt')),level,1))-1)*power(32,length(trim('3yfu3wh150aqt'))-level)),

power(2,32)))hash_value

fromdual

connectby level<=length(trim('3yfu3wh150aqt'));

 

SQL_ID                HASH_VALUE

-------------------------

3yfu3wh150aqt          38808281

从上述结果可以看到,目标SQLSQL ID(3yfu3wh150aqt)经过运算后得到的值就是该SQLSQL HASH VALUE(38808281)

只要目标SQL的执行计划所在的Child Cursor还没有被age outShard Pool,就可以使用方法3查看该SQL的执行计划

select* from table(dbms_xplan.display_cursor('3yfu3wh150aqt',0,'advanced'));

方法4 用于查看指定SQL的所有历史执行计划。

      使用方法23能够显示目标SQL执行计划的前提条件是该SQL的执行计划还在Shared Pool中,而如果该SQL的执行计划已经被age outSharedPool,那么只要该SQL的执行计划被Oracle采集到AWR Repository中,就可以使用方法4来查看该SQL的所有历史执行计划。

      DBMS_XPLAN.DISPLAY_AWR和之前用DBMS_XPLAN.DISPLAY_CURSOR显示的执行计划相比,有一个非常不好的地方——就是用DISPLAY_AWR显示的执行计划中看不到执行步骤对应的谓词条件。根本的原因是Oracle在把执行计划的采样数据从V$SQL_PLAN挪到AWR Repository的基表WRH$_SQL_PLAN中时没有保留V$SQL_PLAN中记录谓词条件(包括驱动查询条件和过滤查询条件)的列ACCESS_PREDICATESFILTER_PREDICATES的值,所以不是DBMS_XPLAN.DISPLAY_CURSOR不想显示谓词条件,而是根本就同有谓词条件可供显示。

1.3 AUTOTRACE开关

      SQLPLUS中将AUTOTRACE开关打开也能得到目标SQL的执行计划,而且,除此之外还能得到目标SQL在执行时的资源消耗量,即通过设置AUTOTRACE开关我们可以额外观察到目标SQL执行时所耗费的物理读、逻辑读、产生redo的数量以及排序的数量等。

      SQLPLUS中设置AUTOTRACE开关的语法如下:

SET AUTOTRACE{OFF|ON|TRACE[ONLY]}

[EXPLAIN][STATISTICS]

  1. SQLPLUS的当前Session中执行命令SET AUTOTRACE ON,可以在当前Session中将AUTOTRACE开关完全打开。这样,在这个Session随后执行的所有SQL除了显示SQL执行结果外,还会额外显示这些SQL所对应的执行计划和资源消耗情况。

  2. SQLPLUS的当前Session中执行命令SET AUTOTRACE OFF,可以在当前Session中将AUTOTRACE开关关闭,这样,在这个Session中随后执行的所有SQL都只会显示SQL执行结果,AUTOTRACE开关的默认值就是OFF

  3. SQLPLUS的当前Session中执行命令SET AUTOTRACE     TRACEONLY,可以在当前Session中将AUTOTRACE开关以不显示SQL执行结果的具体内容的方式完全打开。这种方式与SET AUTOTRACE ON的唯一区别在于TRACEONLY只显示SQL执行结果的数量,而不会显示执行结果的具体内容。适用于SQL执行结果的具体内容特别长,会连续刷屏的SQL,这种情况下我们往往并不关心这些SQL的执行结果的具体内容,而只是关心它们的执行计划和资源消耗量。

  4. SQLPLUS的当前Session中执行命令SET AUTOTRACE     TRACEONLY EXPLAIN,可以在当前Session中将AUTOTRACE开关以只显示SQL执行计划的方式打开。这种方式与TRACEONLY的区别在于TRACEONLY EXPLAIN不会显示目标SQL的资源消耗量和执行结果,而只会显示目标SQL的执行计划。

  5. SQLPLUS的当前Session中执行命令SET AUTOTRACE     TRACEONLY STATISTICS,可以在当前Session中将AUTOTRACE开关以只显示SQL的资源消耗量的方式打开,与TRACEONLY的唯一区别在于TRACEONLY STATISTICS不显示目标SQL的执行计划,而只会显示目标SQL执行结果的数据和资源消耗量。

      设置AUTOTRACE开关的相关命令也没用了Oracle一贯的可以使用简写的惯例:

  • 关键字AUTOTRACE可以用简写AUTOT来代替

  • 关键字TRACEONLY可以用简写TRACE来代替

  • 关键字EXPLAIN可以用简写EXP来代替

  • 关键字STATISTICS可以用简写STAT来代替

SET AUTOTRACE ON

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

SET AUTOTRACE TRACEONLY

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

SET AUTOTRACE OFF

select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;

1.4 10046事件与tkprof命令

      使用10046事件是在Oracle数据库中查看目标SQL的执行计划的另一种方法。这种方法与使用explain plan命令、DBMS_XPLAN包和AUTOTRACE开关的不同之处在于,所得到的执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。这种细粒度的明细显示在我们诊断复杂SQL的性能问题时尤为有用,而且这也是其他三种方法所不能提供的(实际上,用GATHER_PLAN_STATISTICS Hint配合DBMS_XPLN包一起使用可以达到类似10046事件这种细粒度的明细显示效果)

      10046事件得到目标SQL的执行计划是很容易的,只需要依次执行如下三个步骤:

  • 首先在当前Session中激活10046事件;

  • 接着在此Session中执行目标SQL

  • 最后在此Session中关闭10046事件。

      当执行完上述步骤后,Oracle就会将目标SQL的执行计划和明细资源消耗写入此Session所对应的trace文件中,查看这个trace文件就能知道目标SQL的执行计划和资源消耗明细了。Oracle会在参数USER_DUMP_DEST所代表的目标下生成这个trace文件,其命名格式为“实例名_ora_当前Sessionspid.trc”,例如orcl_ora_86541.trc

      通常可以使用如下两种方法在当前Session中激活10046事件:

  • 在当前Session中执行alter session set     events '10046 trace name context forever ,level 12'

  • 在当前Session中执行oradebug event     10046 trace name context forever,level 12

      上述命令中的关键字“level”后的数字是表示设置的10046事件的level值。这个值是可以修改的,我们通常使用的值为12,表示产生的trace文件中除了目标SQL的执行计划和资源消耗明细之外,还会包含目标SQL所使用的绑定变量的值以及该Session所经历的等待事件。除了上述level值之外,其他部分是固定语法,我们无法修改。使用第2种方法,在激活10046事件后执行命令oradebugtracefile_name来得到当前Session所对应的trace文件的具体路径和名称。

      对应的,在当前Session中关闭10046事件的两种方法:

  • 在当前Session中执行alter session set     events '10046 trace name context off'

  • 在当前Session中执行oradebug event     10046 trace name context off

      需要注意的是10046事件所产生的原始trace文件习惯称之为裸trace文件(raw trace)Oracle记录在裸trace文件中的内容一眼看上去并不是那么观,也不是那么容易看懂。为了祼trace文件能够以一种更直观、更容易懂的方式展现出来,Oracle提供了tkprof命令,这个命令是Oracle自带的,可以用它来翻译祼trace文件。

     关于10046事件可以参考之前的博客:

http://hbxztc.blog.51cto.com/1587495/1898624

http://hbxztc.blog.51cto.com/1587495/1898753

参考:《基于Oracle的SQL优化》