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. 分析关键信息
-- 检查是否有全表扫描
-- 查看预估行数是否合理
-- 分析连接方式是否最优
-- 检查谓词信息是否正确使用索引
posted @ 2025-08-25 21:19  一刹流云散  阅读(91)  评论(0)    收藏  举报