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