Oracle 执行计划分析

生成执行计划

使用 EXPLAIN PLAN

sql

-- 生成执行计划
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 50;

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

-- 查看详细执行计划（包括谓词信息）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

-- 查看执行计划（包括成本和字节）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALLSTATS LAST'));

使用 SQL*Plus AUTOTRACE

sql

-- 启用 AUTOTRACE（只显示执行计划）
SET AUTOTRACE ON EXPLAIN;

-- 启用 AUTOTRACE（显示执行计划和统计信息）
SET AUTOTRACE ON;

-- 执行 SQL 语句
SELECT * FROM employees WHERE department_id = 50;

-- 禁用 AUTOTRACE
SET AUTOTRACE OFF;

使用 Enterprise Manager

登录 Oracle Enterprise Manager
导航到 "SQL 优化器"
点击 "SQL 语句分析"
输入 SQL 语句
点击 "查看执行计划"

使用 DBMS_XPLAN 包

sql

-- 生成执行计划并查看
EXPLAIN PLAN FOR
SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;

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

-- 查看详细执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ADVANCED'));

-- 查看执行计划（包括分区信息）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'PARTITION'));

-- 查看执行计划（包括并行执行信息）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'PARALLEL'));

执行计划结构

执行计划的组成部分

ID：操作的唯一标识符
PARENT_ID：父操作的 ID
OPERATION：执行的操作类型
OPTIONS：操作的详细选项
OBJECT_NAME：操作的对象名称
OBJECT_TYPE：对象类型（表、索引等）
COST：操作的估计成本
CARDINALITY：估计的行数
BYTES：估计的字节数
ACCESS PREDICATES：访问谓词（用于索引访问）
FILTER PREDICATES：过滤谓词（用于数据过滤）

执行计划的阅读顺序

自下而上：从最底层的操作开始，逐步向上
从右到左：在同一层次中，从右到左执行
缩进关系：缩进表示操作的层次关系

访问路径分析

全表扫描（TABLE ACCESS FULL）

适用场景：
- 表较小，全表扫描比索引扫描更快
- 需要访问表中大部分数据
- 没有合适的索引
- 索引列上使用了函数或表达式
性能影响：
- 优点：对于小表效率高
- 缺点：对于大表会产生大量 I/O，性能较差

索引唯一扫描（INDEX UNIQUE SCAN）

适用场景：
- 使用唯一索引列作为查询条件
- 保证只返回一行数据
性能影响：
- 优点：访问速度快，只需一次 I/O
- 缺点：仅适用于唯一索引

索引范围扫描（INDEX RANGE SCAN）

适用场景：
- 使用索引列作为查询条件，可能返回多行
- 使用 BETWEEN、>、<、>=、<= 等范围操作符
- 使用 LIKE 操作符（前缀匹配）
性能影响：
- 优点：比全表扫描快，特别是选择性高的索引
- 缺点：返回行数越多，性能越接近全表扫描

索引全扫描（INDEX FULL SCAN）

适用场景：
- 查询只需要索引列的数据
- 按索引顺序返回数据
- 索引比表小很多
性能影响：
- 优点：避免访问表数据，减少 I/O
- 缺点：需要扫描整个索引

索引快速全扫描（INDEX FAST FULL SCAN）

适用场景：
- 查询只需要索引列的数据
- 不需要按索引顺序返回数据
- 可以并行执行
性能影响：
- 优点：速度快，支持并行执行
- 缺点：结果不按索引顺序排序

索引跳跃扫描（INDEX SKIP SCAN）

适用场景：
- 复合索引的前导列没有在查询条件中使用
- 前导列的基数较低
性能影响：
- 优点：可以使用复合索引，即使前导列未指定
- 缺点：性能不如直接使用前导列

连接方法分析

嵌套循环连接（NESTED LOOPS）

适用场景：
- 小结果集与大表连接
- 内表有高效的访问路径
- 连接条件有索引
执行原理：
1. 驱动表（外表）返回结果集
2. 对结果集中的每一行，在内表中查找匹配的行
性能影响：
- 优点：对于小结果集效率高，响应速度快
- 缺点：对于大结果集性能较差

哈希连接（HASH JOIN）

适用场景：
- 大表与大表连接
- 等值连接
- 有足够的 PGA 内存
执行原理：
1. 构建阶段：将小表构建成哈希表
2. 探测阶段：扫描大表，使用哈希函数查找匹配
性能影响：
- 优点：对于大表连接效率高
- 缺点：需要较多内存，不支持非等值连接

排序合并连接（SORT MERGE JOIN）

适用场景：
- 非等值连接（>、<、>=、<=）
- 两个表都已经排序
- 不适合哈希连接的场景
执行原理：
1. 排序阶段：对两个表按连接列排序
2. 合并阶段：按顺序合并两个排序后的结果集
性能影响：
- 优点：支持非等值连接
- 缺点：需要排序操作，性能较差

笛卡尔积连接（CARTESIAN PRODUCT）

适用场景：
- 两个表之间没有指定连接条件
- 故意生成笛卡尔积
性能影响：
- 缺点：结果集大小为两个表行数的乘积，通常是意外情况，性能极差

排序操作分析

排序操作类型

SORT AGGREGATE：聚合函数排序（如 SUM、COUNT、AVG）
SORT ORDER BY：ORDER BY 子句排序
SORT GROUP BY：GROUP BY 子句排序
SORT UNIQUE：DISTINCT 关键字排序
SORT JOIN：排序合并连接中的排序

排序操作的影响

内存使用：排序操作需要 PGA 内存
临时表空间：内存不足时使用临时表空间
性能影响：大量排序会导致性能下降

排序优化策略

使用索引：如果排序顺序与索引顺序一致，可避免排序
减少排序数据量：只选择必要的列
调整 PGA 大小：增加 PGA_AGGREGATE_TARGET 参数
使用并行执行：对于大型排序操作

执行计划分析技巧

识别性能瓶颈

查看全表扫描：大表的全表扫描通常是性能瓶颈
分析索引使用：检查是否使用了合适的索引
检查连接方法：确认连接方法是否适合数据量
查看排序操作：大量排序操作会影响性能
检查谓词信息：确认谓词是否有效使用索引
分析成本和基数：估计值与实际值的差异

执行计划对比

sql

-- 生成原始 SQL 的执行计划
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 生成优化后 SQL 的执行计划
EXPLAIN PLAN FOR
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

实际执行计划与估计执行计划

估计执行计划：使用 EXPLAIN PLAN 生成，基于统计信息
实际执行计划：实际执行 SQL 后生成，包含实际执行统计

sql

-- 启用 SQL 跟踪
ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 执行 SQL 语句
SELECT * FROM employees WHERE department_id = 50;

-- 查看实际执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

SQL 优化策略

1. 索引优化

创建合适的索引：根据查询条件创建索引
复合索引顺序：将选择性高的列放在前面
避免索引失效：
- 不在索引列上使用函数
- 避免使用 !=、NOT IN 等操作符
- 避免使用 IS NULL 条件
- 避免类型转换

2. SQL 语句优化

只选择必要的列：避免使用 SELECT *
使用绑定变量：减少硬解析
优化 WHERE 子句：
- 将选择性高的条件放在前面
- 使用 EXISTS 替代 IN
- 使用 BETWEEN 替代 OR
避免复杂子查询：考虑使用 JOIN 替代
优化 GROUP BY 和 ORDER BY：使用索引排序

3. 连接优化

选择合适的连接方法：根据数据量选择
选择合适的驱动表：小结果集作为驱动表
使用提示（HINT）：在必要时指导优化器

4. 统计信息维护

收集统计信息：定期收集表和索引的统计信息
使用动态采样：对于临时表或小表

sql

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- 收集模式统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- 收集数据库统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS();

执行计划提示（HINT）

索引提示

sql

-- 强制使用特定索引
SELECT /*+ INDEX(employees emp_department_ix) */
  * FROM employees WHERE department_id = 50;

-- 强制全表扫描
SELECT /*+ FULL(employees) */
  * FROM employees WHERE department_id = 50;

连接方法提示

sql

-- 强制嵌套循环连接
SELECT /*+ USE_NL(e d) */
  * FROM employees e, departments d
  WHERE e.department_id = d.department_id;

-- 强制哈希连接
SELECT /*+ USE_HASH(e d) */
  * FROM employees e, departments d
  WHERE e.department_id = d.department_id;

-- 强制排序合并连接
SELECT /*+ USE_MERGE(e d) */
  * FROM employees e, departments d
  WHERE e.department_id = d.department_id;

并行执行提示

sql

-- 强制并行执行
SELECT /*+ PARALLEL(employees 4) */
  * FROM employees;

-- 强制并行度
SELECT /*+ PARALLEL(4) */
  * FROM employees;

其他提示

sql

-- 强制使用特定驱动表
SELECT /*+ LEADING(e) */
  * FROM employees e, departments d
  WHERE e.department_id = d.department_id;

-- 强制不使用绑定变量窥探
SELECT /*+ NO_BIND_AWARE */
  * FROM employees WHERE department_id = :dept_id;

-- 强制优化器模式
SELECT /*+ ALL_ROWS */
  * FROM employees WHERE department_id = 50;

SELECT /*+ FIRST_ROWS(10) */
  * FROM employees WHERE department_id = 50;

常见问题（FAQ）

Q1: 如何判断执行计划是否最优？

A1: 判断执行计划是否最优的标准包括：

成本是否最低
是否使用了合适的索引
连接方法是否适合数据量
排序操作是否必要
实际执行时间是否满足要求

Q2: 为什么执行计划会发生变化？

A2: 执行计划变化的原因包括：

统计信息变更：表数据量或分布发生变化
索引变更：创建或删除索引
参数变更：修改了优化器相关参数
绑定变量窥探：不同的绑定变量值导致不同的执行计划
系统负载：系统资源使用情况变化

Q3: 如何处理执行计划不稳定的问题？

A3: 处理执行计划不稳定的方法包括：

使用绑定变量
收集准确的统计信息
使用 SQL 计划管理（SPM）固定执行计划
使用提示（HINT）指导优化器
调整优化器参数

Q4: 如何分析复杂 SQL 的执行计划？

A4: 分析复杂 SQL 执行计划的步骤：

分解复杂 SQL 为简单部分
从最内层的操作开始分析
关注全表扫描、排序操作和连接方法
对比估计值与实际值
逐步优化各个部分

Q5: 什么是执行计划中的 "Predicate Information"？

A5: "Predicate Information" 是执行计划中的谓词信息部分，显示了 SQL 语句中的过滤条件如何应用到数据访问中。它包括：

Access Predicates：访问谓词，用于确定如何访问数据（如索引访问条件）
Filter Predicates：过滤谓词，用于在访问数据后进一步过滤行

分析谓词信息可以帮助理解索引是否被有效使用，以及过滤条件的执行顺序。

Oracle 执行计划分析 ​

生成执行计划 ​

使用 EXPLAIN PLAN ​

使用 SQL*Plus AUTOTRACE ​

使用 Enterprise Manager ​

使用 DBMS_XPLAN 包 ​

执行计划结构 ​

执行计划的组成部分 ​

执行计划的阅读顺序 ​

访问路径分析 ​

全表扫描（TABLE ACCESS FULL） ​

索引唯一扫描（INDEX UNIQUE SCAN） ​

索引范围扫描（INDEX RANGE SCAN） ​

索引全扫描（INDEX FULL SCAN） ​

索引快速全扫描（INDEX FAST FULL SCAN） ​

索引跳跃扫描（INDEX SKIP SCAN） ​

连接方法分析 ​

嵌套循环连接（NESTED LOOPS） ​

哈希连接（HASH JOIN） ​

排序合并连接（SORT MERGE JOIN） ​

笛卡尔积连接（CARTESIAN PRODUCT） ​

排序操作分析 ​

排序操作类型 ​

排序操作的影响 ​

排序优化策略 ​

执行计划分析技巧 ​

识别性能瓶颈 ​

执行计划对比 ​

实际执行计划与估计执行计划 ​

SQL 优化策略 ​

1. 索引优化 ​

2. SQL 语句优化 ​

3. 连接优化 ​

4. 统计信息维护 ​

执行计划提示（HINT） ​

索引提示 ​

连接方法提示 ​

并行执行提示 ​

其他提示 ​

常见问题（FAQ） ​

Q1: 如何判断执行计划是否最优？ ​

Q2: 为什么执行计划会发生变化？ ​

Q3: 如何处理执行计划不稳定的问题？ ​

Q4: 如何分析复杂 SQL 的执行计划？ ​

Q5: 什么是执行计划中的 "Predicate Information"？ ​