Question

查看ORACLE执行计划的几种常用方法

Answer 1

SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤，作为调优，只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向。



执行计划的定义：执行目标SQL的所有步骤的组合。



我们首先列出查看执行计划的一些常用方法：

1.explain plan命令

PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。

explain plan使用方法：

(1) 执行explain plan for + SQL

(2) 执行select * from table(dbms_xplan.display);

实验表准备：

SQL> desc test1;Name NullType----------------------------------------- -------- ----------------------------T1IDNOT NULL NUMBER(38)T1VVARCHAR2(10)

SQL> desc test2;Name NullType----------------------------------------- -------- ----------------------------T2IDNOT NULL NUMBER(38)T2VVARCHAR2(10)

实验：

SQL> set linesize 100

SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;

Explained.



第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。

这里test2作为驱动表，进行了全表扫描，test1作为被驱动表，由于其包含主键，所以用的是索引全扫描。左侧ID带*号的第四步操作，表示有谓词条件，这里可以看到既使用了主键索引(access)，又使用了过滤条件(filter)。



2.DBMS_XPLAN包

(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‘));



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

主要用于SQLPLUS中查看刚执行过SQL的执行计划。首先第三个参数可以选择‘advanced‘：









接下来，第三个参数使用‘all‘：







可以看出‘advanced‘记录的信息要比‘all’多，主要就是多一个Outline Data。Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。



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

其中第一个参数可以输入SQL的sql_id或hash value，方法就是如果执行的SQL仍在库缓存中，则可以使用V$SQL查询：



其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：



隐藏问题1：

这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。

使用：

SQL>select * from table(dbms_xplan.display_cursor(‘1p2fk2v00c865‘, 0, ‘advanced‘));

或

select * from table(dbms_xplan.display_cursor(‘3221627077‘, 0, ‘advanced‘));

就可以查出对应这条SQL的执行计划，内容同(2)中的‘advanced‘，这就不展示了。

注意这还有第二个参数child_cursor_number，指的是子游标编号，如果未生成新的子游标，则此处写的是0。

(2)和(3)的结论相近，区别就是(2)只是针对最近一次执行SQL查看执行计划，(3)可以针对仍在库缓存中的任意一次SQL查看执行计划。



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

(1)是使用explain plan for +SQL作为前提，(2)和(3)的前提则是SQL的执行计划还在共享池中，具体讲是在库缓存中。如果已经被age out交换出共享池，则不能用这两种方法了。若该SQL的执行计划被采集到AWR库中，则可以用(4)来查询历史执行计划。

隐藏问题2：

实验这部分内容发现使用select * from table(dbms_xplan.display_awr(‘sql_id‘));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，有可能测试用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后执行的手工采集快照，还是未被AWR抓到，比较奇怪的问题，这个也会在另一篇博文中仔细说明。
查看ORACLE执行计划的几种常用方法
标签：