#Oracle #执行计划 #EXPLAIN PLAN
一、引言
在Oracle数据库性能优化中，执行计划（Execution Plan）是理解SQL语句如何被数据库处理的关键工具。EXPLAIN PLAN是Oracle提供的一种静态分析SQL执行路径的方法，它通过生成逻辑执行计划帮助开发者和DBA预测SQL的性能表现，而无需实际执行SQL语句。本文将深入探讨EXPLAIN PLAN的原理、使用场景、操作步骤及最佳实践，并结合DBA_OBJECTS表提供完整案例。
二、EXPLAIN PLAN 的核心原理
- 静态分析：EXPLAIN PLAN不会实际执行SQL，而是将优化器生成的执行计划写入PLAN_TABLE（系统临时表）。
- 无副作用：适合测试敏感操作（如DDL、DML）的执行计划，避免对生产数据产生影响。
- 依赖优化器统计信息：执行计划的准确性高度依赖表和索引的统计信息（如行数、块数、直方图等）。
三、使用 EXPLAIN PLAN 的步骤
1. 准备环境
1.1 确认 PLAN_TABLE 存在
默认情况下，Oracle会通过脚本UTLXPLAN.SQL创建PLAN_TABLE。若表不存在，需以DBA身份执行：
@?/rdbms/admin/utlxplan.sql1.2 权限要求
用户需具备以下权限：
- SELECT ANY TABLE（访问目标表如DBA_OBJECTS）
- CREATE SESSION（登录数据库）
2. 基本语法
EXPLAIN PLAN FOR [SQL语句];SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);关于DBMS_XPLAN.DISPLAY包的使用，会在专题文章中详解说明。
四、实战案例
1. 案例背景
假设需要分析以下SQL的执行计划，查询TEST1表中所有类型为TABLE的对象：
create table test1 as select * from dba_objects;select count(1) from test1;COUNT(1)
----------862592. 生成执行计划
2.1 使用 EXPLAIN PLAN
SQL> explain plan for SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE FROM TEST1 WHERE OBJECT_TYPE = 'TABLE';Explained.-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC'));2.2 输出解析
示例输出：
SQL>
set linesize 1000
set pages 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4122059633---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2013 | 176K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST1 | 2013 | 176K| 336 (1)| 00:00:05 |
---------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1 - SEL$1 / TEST1@SEL$1Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("OBJECT_TYPE"='TABLE')Column Projection Information (identified by operation id):
-----------------------------------------------------------1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22],"OBJECT_TYPE"[VARCHAR2,19]Note
------ dynamic sampling used for this statement (level=2)28 rows selected.
关键字段说明：
- Id：步骤编号，根节点为0。
- Operation：操作类型（如全表扫描TABLE ACCESS FULL）。
- Name：涉及的索引或表名
- Rows：优化器估算的返回行数。
- Cost：总成本。
五、关于explian plan底层原理
在Oracle 10g及更高版本中，当对目标SQL执行explain plan命令时 ，Oracle会把解析该SQL所生成执行计划的具体执行步骤，写入PLAN_TABLE$表。PLAN_TABLE$是一个提交时保留行的全局临时表（ON COMMIT PRESERVE ROWS GLOBAL TEMPORARY TABLE）。这就使得各个会话（Session）只能看到自己执行SQL产生的执行计划，而且不同会话往PLAN_TABLE$写入执行计划的操作互不干扰。后续执行select * from table(dbms_xplan.display) ，只是将PLAN_TABLE$的执行步骤以格式化形式展示出来。
1、PLAN_TABLE$
查看表定义
SQL> set long 90000
set heading off
set serveroutput on size 1000000
select dbms_metadata.get_ddl('TABLE', 'PLAN_TABLE$', 'SYS') from dual;CREATE GLOBAL TEMPORARY TABLE "SYS"."PLAN_TABLE$"( "STATEMENT_ID" VARCHAR2(30),"PLAN_ID" NUMBER,"TIMESTAMP" DATE,"REMARKS" VARCHAR2(4000),"OPERATION" VARCHAR2(30),"OPTIONS" VARCHAR2(255),"OBJECT_NODE" VARCHAR2(128),"OBJECT_OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(30),"OBJECT_ALIAS" VARCHAR2(65),"OBJECT_INSTANCE" NUMBER(*,0),"OBJECT_TYPE" VARCHAR2(30),"OPTIMIZER" VARCHAR2(255),"SEARCH_COLUMNS" NUMBER,"ID" NUMBER(*,0),"PARENT_ID" NUMBER(*,0),"DEPTH" NUMBER(*,0),"POSITION" NUMBER(*,0),"COST" NUMBER(*,0),"CARDINALITY" NUMBER(*,0),"BYTES" NUMBER(*,0),"OTHER_TAG" VARCHAR2(255),"PARTITION_START" VARCHAR2(255),"PARTITION_STOP" VARCHAR2(255),"PARTITION_ID" NUMBER(*,0),"OTHER" LONG,"OTHER_XML" CLOB,"DISTRIBUTION" VARCHAR2(30),"CPU_COST" NUMBER(*,0),"IO_COST" NUMBER(*,0),"TEMP_SPACE" NUMBER(*,0),"ACCESS_PREDICATES" VARCHAR2(4000),"FILTER_PREDICATES" VARCHAR2(4000),"PROJECTION" VARCHAR2(4000),"TIME" NUMBER(*,0),"QBLOCK_NAME" VARCHAR2(30)) ON COMMIT PRESERVE ROWS2、测试explain plan与PLAN_TABLE$
2.1 使用explain plan对目标SQL解析
SQL>
select sid from v$mystat where rownum<2;SID
----------23explain plan for SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE FROM TEST1 WHERE OBJECT_TYPE = 'TABLE';Explained.2.2 查看PLAN_TABLE$信息
SQL>
set linesize 1000
col OPERATION for a20
col OPTIONS for a20
col OBJECT_NAME for a20
select operation,options,object_name,id,cardinality,cost from sys.plan_table$;OPERATION OPTIONS OBJECT_NAME ID CARDINALITY COST
-------------------- -------------------- -------------------- ---------- ----------- ----------
SELECT STATEMENT 0 2013 336
TABLE ACCESS FULL TEST1 1 2013 3362.3 查看session、事务和对象关系
- 当前session产生的事务
SQL> select saddr from v$session where sid=23;SADDR
----------------
00000000924403A0select count(1) from v$transaction where SES_ADDR='00000000924403A0';COUNT(1)
----------1- 查看产生事务的对象
select object_id from v$locked_object where session_id=23;OBJECT_ID
----------5187select owner,object_name from dba_objects where object_id=5187;OWNER OBJECT_NAME
------------------------------ --------------------
SYS PLAN_TABLE$- 对比其它事务
SQL> select sid from v$mystat where rownum<2;SID
----------35SQL> SQL> select count(1) from sys.plan_table$;COUNT(1)
----------0其它事务中没有任何执行计划相关信息。
从上面的实验可能看出：在Oracle 10g及以上版本中，explain plan命令执行后会将解析目标SQL所产生的执行计划具体步骤写入 PLAN_TABLE$表，而 select * from table(dbms_xplan.display) 则是从 PLAN_TABLE$ 表中以格式化方式展示这些执行步骤，帮助用户了解SQL执行计划，以便优化SQL。
五、EXPLAIN PLAN 的优缺点
1. 优点
- 零风险：不执行SQL，避免对生产环境造成影响。
- 轻量级：适合快速验证复杂查询（如多表关联、子查询）的计划。
- 兼容性：适用于所有Oracle版本。
2. 缺点
- 依赖统计信息：若统计信息过期，生成的计划可能不准确。
- 不执行实际语句：无法捕获运行时问题（如锁等待、死锁）。
六、总结
EXPLAIN PLAN是Oracle性能调优的基础工具，通过静态分析帮助开发者理解优化器的决策逻辑。结合DBA_OBJECTS表的案例实践，可快速掌握其核心用法。尽管它无法替代动态监控工具（如AWR、SQL Trace），但在SQL开发阶段和敏感操作测试中具有不可替代的作用。实际应用中，需结合统计信息维护和多工具交叉验证，才能全面解决性能问题。
)