Oracle执行计划解析详解

Oracle执行计划解析详解

DBMS_XPLAN.DISPLAY函数说明

DBMS_XPLAN.DISPLAY 是Oracle提供的用于显示SQL执行计划的内置包函数。

基本语法和用法

-- 基本用法
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 指定特定的执行计划表
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', 'statement_id', 'format'));

执行计划各字段详解

主要字段说明：

字段	含义	说明
Id	操作ID	执行计划中每个操作的唯一标识符
Operation	操作类型	数据库执行的具体操作
Name	对象名称	涉及的表、索引等对象名称
Rows	预估行数	优化器预估的操作返回行数
Bytes	预估字节数	优化器预估的数据量大小
Cost	成本	优化器计算的操作成本
Time	预估时间	优化器预估的执行时间

执行计划操作类型详解

1. 表访问操作

-- 全表扫描
TABLE ACCESS FULL

-- 通过ROWID访问
TABLE ACCESS BY USER ROWID

-- 通过索引ROWID访问
TABLE ACCESS BY INDEX ROWID

2. 索引操作

-- 索引唯一扫描
INDEX UNIQUE SCAN

-- 索引范围扫描
INDEX RANGE SCAN

-- 索引全扫描
INDEX FULL SCAN

-- 索引快速全扫描
INDEX FAST FULL SCAN

3. 连接操作

-- 嵌套循环连接
NESTED LOOPS

-- 哈希连接
HASH JOIN

-- 排序合并连接
MERGE JOIN

4. 排序和聚合操作

-- 排序
SORT ORDER BY

-- 分组
SORT GROUP BY

-- 去重
SORT UNIQUE

执行计划示例分析

简单查询执行计划：

-- SQL语句
EXPLAIN PLAN FOR 
SELECT employee_id, first_name, last_name 
FROM employees 
WHERE department_id = 10;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

可能的输出结果：

Plan hash value: 1445482782

----------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | EMP_DEPT_IX |     1 |     1   (0)|
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPARTMENT_ID"=10)
   2 - access("DEPARTMENT_ID"=10)

连接查询执行计划：

-- SQL语句
EXPLAIN PLAN FOR
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

可能的输出结果：

Plan hash value: 1357425499

-----------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   106 |  4558 |     6   (0)|
|*  1 |  HASH JOIN         |             |   106 |  4558 |     6   (0)|
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   459 |     3   (0)|
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  2782 |     3   (0)|
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

执行计划格式选项

不同格式选项：

-- 基本格式（默认）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC'));

-- 典型格式（显示谓词信息）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'TYPICAL'));

-- 所有信息
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ALL'));

-- 自定义格式
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ALL -COST -BYTES'));

格式选项说明

格式选项	说明
BASIC	只显示基本的执行计划结构
TYPICAL	显示基本计划和谓词信息（默认）
ALL	显示所有可用信息
-COST	隐藏成本信息
-BYTES	隐藏字节信息
+PREDICATE	显示谓词信息
+PARTITION	显示分区信息

谓词信息详解

谓词类型：

-- Access Predicate（访问谓词）
access("DEPARTMENT_ID"=10)
-- 表示用于定位数据的条件

-- Filter Predicate（过滤谓词）
filter("SALARY">5000)
-- 表示对已获取数据的过滤条件

性能分析要点

1. 识别性能问题：

-- 注意全表扫描（TABLE ACCESS FULL）
-- 大量数据时可能影响性能

-- 注意高成本操作
-- Cost值过高可能表示需要优化

-- 注意笛卡尔积
-- CARTESIAN JOIN通常表示缺少连接条件

2. 优化建议：

-- 根据执行计划添加合适索引
-- 优化连接顺序
-- 重写SQL语句改善执行计划

实际使用示例

完整的执行计划分析流程：

-- 1. 生成执行计划
EXPLAIN PLAN FOR
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND e.salary > 5000;

-- 2. 查看详细执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ALL'));

-- 3. 分析关键信息
-- 检查是否有全表扫描
-- 查看预估行数是否合理
-- 分析连接方式是否最优
-- 检查谓词信息是否正确使用索引