这篇文章将为大家详细讲解有关如何通过explain和dbms_xplan包分析执行计划，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
该工具需要访问一张特殊的表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 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 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
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包分析执行计划就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
