Oracle如何查看执行计划

181次阅读

共计 6750 个字符，预计需要花费 17 分钟才能阅读完成。

一、如何查看执行计划

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

explain plan 命令
DBMS_XPLAN包
SQLPLUS中的 AUTOTRACE 开关
10046事件
10053事件
AWR报告或 Statspack 报告
一些现成的脚本 ( 如 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 命令对目标 SQL 做explain，再使用“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 ROWS 的GLOBALTEMPORARY TABLE，所以这里 Oracle 可以做到各个的 Session 只能看到自己执行的 SQL 所产生的执行计划，并且各个 Session 往PLAN_TABLE$写入执行计划的过程互不干扰。

1.2 DBMS_XPLAN包

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

select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,child_cursor_number,’advanced’));
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 所传入的第一个参数的值是指定 SQL 的SQL 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_id为 3yfu3wh150aqt，SQL HASH_VALUE 为38808281，对应的 ChildCursor Number 为0。

本质上 SQL ID 和SQL 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

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

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

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

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

使用方法 2、3 能够显示目标 SQL 执行计划的前提条件是该 SQL 的执行计划还在 Shared Pool 中，而如果该 SQL 的执行计划已经被 age out 出SharedPool，那么只要该 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_PREDICATES 和FILTER_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]

在 SQLPLUS 的当前 Session 中执行命令 SET AUTOTRACE ON，可以在当前Session 中将 AUTOTRACE 开关完全打开。这样，在这个 Session 随后执行的所有 SQL 除了显示 SQL 执行结果外，还会额外显示这些 SQL 所对应的执行计划和资源消耗情况。
在 SQLPLUS 的当前 Session 中执行命令 SET AUTOTRACE OFF，可以在当前Session 中将 AUTOTRACE 开关关闭，这样，在这个 Session 中随后执行的所有 SQL 都只会显示 SQL 执行结果，AUTOTRACE开关的默认值就是OFF。
在 SQLPLUS 的当前 Session 中执行命令 SET AUTOTRACE TRACEONLY，可以在当前Session 中将 AUTOTRACE 开关以不显示 SQL 执行结果的具体内容的方式完全打开。这种方式与 SET AUTOTRACE ON 的唯一区别在于 TRACEONLY 只显示 SQL 执行结果的数量，而不会显示执行结果的具体内容。适用于 SQL 执行结果的具体内容特别长，会连续刷屏的 SQL，这种情况下我们往往并��关心这些SQL 的执行结果的具体内容，而只是关心它们的执行计划和资源消耗量。
在 SQLPLUS 的当前 Session 中执行命令 SET AUTOTRACE TRACEONLY EXPLAIN，可以在当前Session 中将 AUTOTRACE 开关以只显示 SQL 执行计划的方式打开。这种方式与 TRACEONLY 的区别在于 TRACEONLY EXPLAIN 不会显示目标 SQL 的资源消耗量和执行结果，而只会显示目标 SQL 的执行计划。
在 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_ 当前 Session 的spid.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 文件。