共计 7145 个字符，预计需要花费 18 分钟才能阅读完成。
这篇文章将为大家详细讲解有关如何通过 explain 和 dbms_xplan 包分析执行计划，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
该工具需要访问一张特殊的表 plan_table，该表用于存储执行计划，在 Oracle 10g 之前需要用脚本 utlxplan.sql 创建：
建表：
@?\rdbms\admin\utlxplan.sql
建同义词：
create public synonym plan_table for plan_table;
授权：
grant all on plan_table to public;
Oracle 10g 之后不再需要创建表 plan_table，而是增加了数据字典表 plan_table$，然后基于 plan_table$ 创建了公共同义词供用户使用。
explain 基本语法：
explain plan [set statement_id = stmt_id] for sql_statement;
explain 指令的执行结果存储于表 plan_table 中，有几种方法获取执行计划的详细信息：
1、直接查询 plan_table 表
解释计划
explain plan for select count(*) from scott.emp;
查询结果
col id for 999
col operation for a50
col options for a20
col object_name for a20
select id,
lpad(, 2 * depth) || operation || || options ||
decode(id, 0, substr(optimizer, 1, 6) || Cost = || to_char(cost)) operation,
options,
object_name,
position
from plan_table
where plan_id = (select max(plan_id) from plan_table)
order by id;
ID OPERATION OPTIONS OBJECT_NAME POSITION
—- ————————————————– ——————– ——————– ———-
0 SELECT STATEMENT ALL_RO Cost = 1 1
1 SORT AGGREGATE AGGREGATE 1
2 INDEX FULL SCAN FULL SCAN PK_EMP 1
或者用以下查询，包含了执行计划树的 level 层次关系
col Execute Plan for a100
select id || || parent_id || || lpad(, 2 * level – 1) ||
operation || || options || || object_name || (Cost= || cost ||) as Execute Plan
from plan_table
start with id = 0
connect by prior id = parent_id;
Execute Plan
—————————————————————————————————-
0 SELECT STATEMENT (Cost=1)
1 0 SORT AGGREGATE (Cost=)
2 1 INDEX FULL SCAN PK_EMP (Cost=1)
2、通过程序包 dbms_xplan 获得执行计划
1) 获得最近一次 explain 的执行计划
col PLAN_TABLE_OUTPUT for a100
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 2937609675
——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
——————————————————————-
2) 通过指定的语句 ID 来查询
select * from table(dbms_xplan.display( plan_table , stmt_id
3) 通过 SQL_ID 和子游标来查询，该函数并不要求先做 explain，显示的信息也较详细，另外还有一个 format 参数可以做更详细的定制。
select * from table(dbms_xplan.display_cursor( sql_id , child_number , format
sql_id 为 null 时显示最近一次执行的 SQL 的执行计划，但注意要保持 set serveroutput off，否则最后一句 SQL 将不是你运行的 SQL，child number 为 null，则返回所有子游标的执行计划。
可以通过在 SQL 语句中加入注释，方便的获取 SQL_ID 和 CHILD_NUMBER 信息，如以下 SQL，先执行一次
select /*12345*/ m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2
from cmes.c_material_t m
where (regexp_like( 641234HNGA080001A , m.validaterule, c) or
regexp_like(641234HNGA080001A , m.validaterule2, c))
and m.deleted_flag = 0
and rownum = 1;
查出它的 ID：
select sql_id, child_number from v$sql where sql_text like %12345%
SQL_ID CHILD_NUMBER
————- ————
9jk2r7a64s470 0
cc274s1r7ab6w 0
因为以上包含 12345 注释的语句被执行了两条，所以因取先执行的第一条为实际的 ID。
查看执行计划：
col PLAN_TABLE_OUTPUT for a100;
select * from table(dbms_xplan.display_cursor( 9jk2r7a64s470 , 0));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID 9jk2r7a64s470, child number 0
————————————-
select /*12345*/ m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2
from cmes.c_material_t m
where (regexp_like( 641234HNGA080001A , m.validaterule, c) or
regexp_like(641234HNGA080001A , m.validaterule2, c))
and m.deleted_flag = 0
and rownum = 1;
Plan hash value: 1524529232
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS FULL| C_MATERIAL_T | 2 | 116 | 2 (0)| 00:00:01 |
———————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(ROWNUM=1)
2 – filter((M . DELETED_FLAG = 0 AND ( REGEXP_LIKE
(641234HNGA080001A , M . VALIDATERULE , c , not feasible)
查看更详细的执行计划信息，需要先设置统计级别为 ALL，否则没有 A -ROWS 等信息
alter session set statistics_level = all;
或者在语句级别使用 HINT
select /*+gather_plan_statistics*/
select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2
from cmes.c_material_t m
where (regexp_like( 641234HNGA080001A , m.validaterule, c) or
regexp_like(641234HNGA080001A , m.validaterule2, c))
and m.deleted_flag = 0
and rownum = 1;
select * from table(dbms_xplan.display_cursor(null, null, ALLSTATS
PLAN_TABLE_OUTPUT
—————————————————————————————————
SQL_ID fmu73t3umxm1r, child number 0
————————————-
select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule,
m.validaterule2 from cmes.c_material_t m where (regexp_like( 641234HNGA080001A ,
m.validaterule, c ) or regexp_like(641234HNGA080001A , m.validaterule2,
c )) and m.deleted_flag = 0 and rownum = 1
Plan hash value: 1524529232
———————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————
|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | 16 |
|* 2 | TABLE ACCESS FULL| C_MATERIAL_T | 1 | 2 | 0 |00:00:00.01 | 16 |
———————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(ROWNUM=1)
2 – filter(((REGEXP_LIKE ( 641234HNGA080001A , M . VALIDATERULE , c ,???)
这里 E -Rows 是预估的返回行数，A-Rows 是实际的返回行数。
format 的高级应用可以显示更多的信息，特别是可以显示绑定变量的具体值，这个非常有用。以下是推荐的使用格式
select * from table(dbms_xplan.display_cursor(null,null, ADVANCED ALLSTATS LAST PEEKED_BINDS
allstats：iostats + memstats，iostats 显示该游标累计执行的 io 统计信息 (buffers, reads)，memstats 显示累计执行的 pga 使用信息 (omem 1mem used-mem)
last：仅显示最后一次执行的统计信息
advanced：显示 outline、query block name、column projection 等信息
peeked_binds：打印解析时使用的绑定变量
如以下操作可以显示非常详细的执行计划和绑定变量等信息
alter session set statistics_level = all;
var a varchar2(20);
exec :a := EMP
select object_id from dba_objects where object_name = :a;
select * from table(dbms_xplan.display_cursor(null,null, ADVANCED ALLSTATS LAST PEEKED_BINDS
dbms_xplan 程序包还有一个函数 display_awr 可以获取 AWR 报告中指定 SQL_ID 的执行计划
col PLAN_TABLE_OUTPUT for a100;
select * from table(dbms_xplan.display_awr( 9jk2r7a64s470
该函数获取的执行计划来自 dba_hist_sql_plan 视图，通过历史数据记录，甚至一些被老化的 SQL 执行计划仍然可以被查询到。
3、通过脚本 utlxpls.sql 或 utlxplp.sql 获得执行计划
@?\rdbms\admin\utlxpls.sql
或
@?\rdbms\admin\utlxplp.sql
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 2937609675
——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
——————————————————————-
看一下这两个脚本的内容，该方法实际是调用了方法 2 中的程序包
get ?\rdbms\admin\utlxpls.sql
……
41* select plan_table_output from table(dbms_xplan.display( plan_table ,null, serial
42
get ?\rdbms\admin\utlxplp.sql
……
40* select * from table(dbms_xplan.display());
关于如何通过 explain 和 dbms_xplan 包分析执行计划就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
