外观
Oracle 执行计划分析
执行计划是Oracle数据库执行SQL语句的详细步骤和策略，是SQL优化的核心。了解和分析执行计划对于DBA优化SQL语句、提高数据库性能至关重要。本文将详细介绍Oracle 19c和21c环境下执行计划的生成、解读和优化方法，结合实际案例和最佳实践，帮助DBA掌握执行计划分析的核心技能。
执行计划基础
执行计划的定义与作用
执行计划是Oracle优化器（Optimizer）根据SQL语句、数据库统计信息和系统资源状况生成的最优执行策略，它决定了：
- 表的访问方式（全表扫描、索引扫描等）
- 表的连接方式（嵌套循环、哈希连接、排序合并连接等）
- 表的连接顺序
- 排序操作的位置和方式
- 过滤条件的应用时机
- 并行执行的策略和并行度
执行计划的核心作用：
- 帮助DBA理解SQL语句的执行过程
- 识别SQL语句中的性能瓶颈
- 验证SQL优化的效果
- 确保执行计划的稳定性
优化器类型与工作原理
1. 优化器类型
| 优化器类型 | 描述 | 适用场景 |
|---|---|---|
| CBO（Cost-Based Optimizer） | 基于成本的优化器，根据统计信息计算执行计划的成本，选择成本最低的执行计划 | 所有生产环境，Oracle 10g及以上版本默认使用 |
| RBO（Rule-Based Optimizer） | 基于规则的优化器，根据固定规则生成执行计划，不考虑数据分布 | 已过时，Oracle 10g起不再推荐使用 |
2. CBO工作原理
CBO的工作过程包括：
- 解析（Parsing）：检查SQL语法和语义，生成解析树
- 查询转换（Query Transformation）：将解析树转换为等价的查询树，包括视图合并、子查询展开等
- 计划生成（Plan Generation）：生成多个可能的执行计划
- 成本计算（Cost Calculation）：计算每个执行计划的成本，包括I/O成本、CPU成本和内存成本
- 计划选择（Plan Selection）：选择成本最低的执行计划
执行计划生成方法
1. EXPLAIN PLAN命令
EXPLAIN PLAN是最常用的生成执行计划的方法，适用于分析未执行的SQL语句。
基本用法：
sql
-- 生成执行计划
EXPLAIN PLAN FOR
SELECT /*+ gather_plan_statistics */ *
FROM employees WHERE department_id = 100;
-- 查看基本执行计划
SELECT * FROM TABLE(dbms_xplan.display());
-- 查看包含谓词和统计信息的执行计划
SELECT * FROM TABLE(dbms_xplan.display(format => 'ALL'));
-- 查看包含成本和投影信息的执行计划
SELECT * FROM TABLE(dbms_xplan.display(format => 'ALL_COSTS +PROJECTION'));
-- 查看包含Outline信息的执行计划
SELECT * FROM TABLE(dbms_xplan.display(format => 'ALL +OUTLINE'));2. DBMS_XPLAN.DISPLAY_CURSOR
DISPLAY_CURSOR用于查看已经执行过的SQL语句的实际执行计划，包含实际执行统计信息。
基本用法：
sql
-- 查找SQL_ID
SELECT sql_id, sql_text, executions,
elapsed_time/1000000 avg_elapsed_seconds
FROM v$sql
WHERE sql_text LIKE '%SELECT * FROM employees WHERE department_id =%'
ORDER BY last_active_time DESC;
-- 查看实际执行计划
SELECT * FROM TABLE(dbms_xplan.display_cursor(
sql_id => '&sql_id',
cursor_child_no => 0,
format => 'ALLSTATS LAST +ADAPTIVE +PEEKED_BINDS +OUTLINE +PROJECTION'
));3. SQL Monitor
SQL Monitor用于实时监控长时间运行的SQL语句，并提供详细的执行计划和统计信息。
基本用法：
sql
-- 生成SQL Monitor报告（文本格式）
SELECT dbms_sql_monitor.report_sql_monitor(
sql_id => '&sql_id',
type => 'TEXT',
report_level => 'ALL'
) FROM dual;
-- 生成HTML格式的SQL Monitor报告
SELECT dbms_sql_monitor.report_sql_monitor(
sql_id => '&sql_id',
type => 'HTML',
report_level => 'ALL'
) FROM dual;
-- 在SQL*Plus中生成HTML报告并保存到文件
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET PAGESIZE 0
SET LINESIZE 1000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SPOOL sql_monitor_report.html
SELECT dbms_sql_monitor.report_sql_monitor(sql_id => '&sql_id', type => 'HTML') FROM dual;
SPOOL OFF4. AUTOTRACE命令
AUTOTRACE是SQL*Plus的内置命令，用于在执行SQL语句的同时显示执行计划和统计信息。
基本用法：
sql
-- 启用AUTOTRACE，显示执行计划和统计信息
SET autotrace ON;
-- 只显示执行计划，不执行SQL语句
SET autotrace EXPLAIN;
-- 只显示统计信息，不显示执行计划
SET autotrace STAT;
-- 禁用AUTOTRACE
SET autotrace OFF;
-- 执行SQL并查看执行计划和统计信息
SELECT * FROM employees WHERE department_id = 100;执行计划详细解读
1. 执行计划基本结构
一个典型的执行计划包含以下信息：
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 690 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 6 | 690 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=100)关键字段解读：
- Id：操作的唯一标识符，按执行顺序排列
- Operation：操作类型（如TABLE ACCESS FULL、INDEX RANGE SCAN等）
- Name：操作的对象名称（表名或索引名）
- Rows：估计的返回行数（基数）
- Bytes：估计的返回字节数
- Cost (%CPU)：操作的成本估算（CPU成本占比）
- Time：估计的执行时间
- Predicate Information：谓词信息，包括访问谓词（Access Predicates）和过滤谓词（Filter Predicates）
2. 常见操作类型分析
表访问操作
| 操作类型 | 描述 | 性能影响 | 优化建议 |
|---|---|---|---|
| TABLE ACCESS FULL | 全表扫描，读取表中的所有行 | 高I/O消耗，性能差 | 考虑添加索引、分区或优化WHERE条件 |
| TABLE ACCESS BY INDEX ROWID | 通过索引ROWID访问表 | 性能较好 | 检查索引覆盖性，考虑复合索引 |
| TABLE ACCESS BY INDEX ROWID BATCHED | 批量通过索引ROWID访问表，减少I/O次数 | 性能优于普通ROWID访问 | 适合大量索引行的访问 |
| TABLE ACCESS CLUSTER | 簇表访问 | 适合经常关联查询的表 | 确保簇表设计合理 |
| TABLE ACCESS HASH | 哈希表访问 | 适合大表的快速访问 | 确保哈希分布均匀 |
索引访问操作
| 操作类型 | 描述 | 适用场景 | 优化建议 |
|---|---|---|---|
| INDEX UNIQUE SCAN | 索引唯一扫描，返回单行 | 主键或唯一索引的精确查询 | 理想情况，无需优化 |
| INDEX RANGE SCAN | 索引范围扫描，返回多行 | 范围查询（如BETWEEN、>、<等） | 确保索引选择性高，避免大范围扫描 |
| INDEX FULL SCAN | 索引全扫描，读取整个索引 | 适合小索引，或需要排序的查询 | 考虑索引覆盖，避免回表 |
| INDEX FAST FULL SCAN | 索引快速全扫描，使用多块读 | 适合大索引，不需要排序的查询 | 确保索引包含所有需要的列 |
| INDEX SKIP SCAN | 索引跳跃扫描，适合复合索引前缀列选择性低的情况 | 复合索引前缀列选择性低，后缀列选择性高的查询 | 考虑调整复合索引顺序或添加新索引 |
| INDEX DESCENDING SCAN | 索引降序扫描 | ORDER BY DESC查询 | 确保索引顺序与ORDER BY子句匹配 |
连接操作
| 操作类型 | 描述 | 适用场景 | 性能特点 |
|---|---|---|---|
| NESTED LOOPS | 外层循环的每一行与内层循环进行匹配 | 小结果集连接，内层表有高效索引 | 低内存消耗，适合OLTP系统 |
| HASH JOIN | 将较小的表构建哈希表，然后与较大的表进行匹配 | 大结果集连接，无合适索引的情况 | 高CPU消耗，适合数据仓库 |
| SORT MERGE JOIN | 先对两个表进行排序，然后合并结果 | 有序数据连接，或需要排序的查询 | 高I/O和CPU消耗，适合有序数据 |
| CARTESIAN PRODUCT | 两个表的所有行进行匹配 | 应避免使用 | 性能极差，会产生大量结果集 |
3. 执行计划问题识别
常见执行计划问题：
全表扫描代替索引扫描
- 原因：缺少合适索引、索引失效、统计信息过时
- 识别：执行计划中出现不必要的TABLE ACCESS FULL
- 解决：添加合适索引、更新统计信息、优化WHERE子句
低效的连接方式
- 原因：表连接顺序不合理、缺少连接索引
- 识别：大表连接使用NESTED LOOPS，或不必要的SORT MERGE JOIN
- 解决：优化连接顺序、添加连接索引、调整优化器参数
不必要的排序操作
- 原因：ORDER BY/GROUP BY子句缺少对应索引
- 识别：执行计划中出现不必要的SORT操作
- 解决：添加排序索引、优化排序字段
执行计划不稳定
- 原因：绑定变量窥探、统计信息波动、数据分布不均匀
- 识别：同一SQL出现不同的plan_hash_value
- 解决：使用执行计划基线、SQL Profile、调整绑定变量使用方式
估计行数与实际行数差异大
- 原因：统计信息过时或不准确
- 识别：执行计划中的Rows值与实际返回行数差异超过10倍
- 解决：更新统计信息，考虑使用直方图
Oracle 19c与21c执行计划新特性
Oracle 19c增强特性
自适应执行计划增强
- 支持动态调整执行计划
- 实时收集执行统计信息
- 自动调整表连接方式和顺序
执行计划管理增强
- 自动捕获重复执行的SQL
- 自动演进执行计划基线
- 支持并行查询的执行计划管理
SQL Monitor增强
- 支持实时查看SQL执行进度
- 提供更详细的操作统计信息
- 支持ADDM SQL分析
自动索引
- 自动识别缺少的索引
- 自动创建和测试索引
- 自动监控索引使用情况
- 自动禁用或删除未使用的索引
Oracle 21c增强特性
实时执行计划
- 支持查看SQL执行过程中的实时执行计划
- 提供更详细的操作级统计信息
- 支持实时调整执行计划
机器学习优化
- 机器学习驱动的执行计划选择
- 自适应统计信息收集
- 智能索引建议
- 预测性执行计划优化
增强的SQL Profile
- 支持更复杂的执行计划调整
- 提供更详细的优化建议
- 支持自动创建和管理
执行计划可视化增强
- 更直观的执行计划图形化展示
- 支持交互式执行计划分析
- 提供更详细的性能指标
执行计划优化实战案例
案例1：全表扫描优化
问题现象：
sql
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2023-01-01';
-- 执行时间：5.2秒执行计划：
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 1.5M | 120 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ORDERS | 5000 | 1.5M | 120 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUSTOMER_ID"=12345 AND "ORDER_DATE">TO_DATE('2023-01-01','yyyy-mm-dd'))分析过程：
- orders表有100万行数据，customer_id和order_date列单独有索引
- WHERE子句同时使用了customer_id和order_date，但没有复合索引
- customer_id的选择性高，order_date的选择性中等
优化方案：
sql
-- 创建复合索引
CREATE INDEX orders_cust_date_idx ON orders(customer_id, order_date);优化后执行计划：
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 1.5M | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS | 5000 | 1.5M | 8 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ORDERS_CUST_DATE_IDX| 5000 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMER_ID"=12345 AND "ORDER_DATE">TO_DATE('2023-01-01','yyyy-mm-dd'))优化效果：执行时间从5.2秒降低到0.02秒，成本从120降低到8。
案例2：执行计划不稳定优化
问题现象： 同一SQL在不同时间执行，执行时间从0.1秒到10秒不等。
分析过程：
- 查看AWR历史，发现有多个不同的plan_hash_value
- 分析执行计划：
- 高效计划：使用INDEX RANGE SCAN，成本10
- 低效计划：使用TABLE ACCESS FULL，成本1000
- 检查统计信息：表统计信息最近更新，但数据分布不均匀
- 发现问题：SQL使用了绑定变量，导致绑定变量窥探（Bind Variable Peeking）
优化方案：
sql
-- 使用SQL计划基线锁定高效执行计划
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '&sql_id',
plan_hash_value => &good_plan_hash_value
);
-- 或使用SQL Profile
EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(
sql_id => '&sql_id',
plan_hash_value => &good_plan_hash_value
);
-- 或禁用绑定变量窥探
ALTER SYSTEM SET optimizer_use_skew_only_hints = TRUE;优化效果：执行计划稳定，始终使用高效的索引扫描，执行时间稳定在0.1秒左右。
案例3：统计信息过时优化
问题现象：
sql
SELECT * FROM large_table WHERE status = 'ACTIVE';
-- 执行时间：8.5秒执行计划：
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 200K| 20 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LARGE_TABLE | 1000 | 200K| 20 (0)| 00:00:01 |
---------------------------------------------------------------------------分析过程：
- 执行计划估计返回1000行，但实际返回100万行
- 检查统计信息：最后收集时间为3个月前
- 表数据量从100万增长到1000万行，统计信息严重过时
优化方案：
sql
-- 收集表和索引统计信息，包括直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCHEMA_NAME',
tabname => 'LARGE_TABLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 8,
cascade => TRUE
);优化后执行计划：
sql
-- 优化后，优化器选择使用索引而不是全表扫描
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K | 200M| 500 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LARGE_TABLE | 1000K | 200M| 500 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LARGE_TABLE_STATUS_IDX| 1000K | | 100 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------优化效果：执行时间从8.5秒降低到0.8秒，虽然成本从20增加到500，但实际执行时间大幅降低，因为优化器现在有了准确的统计信息。
执行计划优化最佳实践
1. 建立执行计划管理体系
- 执行计划基线管理：为关键SQL建立执行计划基线，确保执行计划稳定
- 定期审查执行计划：每周审查Top SQL的执行计划，确保没有退化
- 监控执行计划变化：使用AWR跟踪plan_hash_value变化，及时发现执行计划退化
- 建立执行计划仓库：保存关键SQL的历史执行计划，便于分析变化趋势
2. 优化器统计信息管理
- 定期更新统计信息：根据数据变化频率，设置合适的统计信息收集周期
- 使用直方图：对于数据分布不均匀的列，创建直方图
- 考虑增量统计信息：对于大分区表，使用增量统计信息减少收集时间
- 使用动态采样：对于临时表或统计信息缺失的表，启用动态采样
3. 索引设计最佳实践
- 遵循索引设计原则：选择高选择性列、考虑查询模式
- 复合索引顺序：将选择性高的列放在前面，匹配查询条件顺序
- 避免过度索引：索引数量不宜超过表列数的20%
- 考虑索引覆盖：包含查询所需的所有列，避免回表
- 定期维护索引：重建碎片化索引，收集统计信息
4. SQL设计最佳实践
- **避免SELECT ***：只选择需要的列
- 使用绑定变量：减少硬解析，提高执行计划复用
- 优化WHERE子句：将高选择性条件放在前面，避免在WHERE子句中使用函数
- 合理使用JOIN：限制JOIN表数量，优化JOIN顺序
- 避免使用NOT IN和OR：考虑使用NOT EXISTS或UNION
5. 执行计划分析工具使用
- 结合多种工具：使用EXPLAIN PLAN、DISPLAY_CURSOR、SQL Monitor等多种工具综合分析
- 关注实际执行统计：优先查看包含实际执行统计的执行计划
- 分析谓词信息：重点关注Access Predicates和Filter Predicates
- 使用SQL Tuning Advisor：获取自动优化建议
- 利用Oracle Enterprise Manager：使用OEM的SQL优化功能，如SQL Tuning Sets
常见问题（FAQ）
Q1: 如何获取真实的执行计划？
A1: 获取真实执行计划的方法：
- 使用
/*+ gather_plan_statistics */提示，然后查看TABLE(dbms_xplan.display_cursor(format => 'ALLSTATS LAST')) - 使用SQL Monitor报告，它包含真实的执行统计信息
- 对于已执行的SQL，查看
v$sql_plan和v$sql_plan_statistics_all视图
Q2: 执行计划中的Cost值代表什么？
A2: Cost值是优化器根据统计信息计算的相对成本，包括I/O成本、CPU成本和内存成本。Cost值越低，执行计划越高效。但Cost值只是优化器的估计，实际执行时间还受系统资源状况影响。
Q3: 如何判断执行计划是否最优？
A3: 判断执行计划是否最优的方法：
- 比较不同执行计划的Cost值，选择Cost最低的计划
- 检查是否使用了合适的索引和连接方式
- 验证实际执行时间是否符合预期
- 确保执行计划稳定，没有频繁变化
Q4: 如何处理执行计划退化？
A4: 处理执行计划退化的方法：
- 检查统计信息是否准确，必要时更新统计信息
- 检查索引是否失效或被删除
- 使用执行计划基线锁定高效执行计划
- 使用SQL Profile微调执行计划
- 考虑重写SQL语句
Q5: 如何优化复杂SQL的执行计划？
A5: 优化复杂SQL执行计划的方法：
- 分解复杂SQL为多个简单SQL
- 使用临时表或物化视图
- 考虑使用提示（Hints）引导优化器
- 使用SQL Tuning Advisor获取优化建议
- 考虑重构数据模型
Q6: 如何监控执行计划的稳定性？
A6: 监控执行计划稳定性的方法：
- 定期生成AWR报告，比较不同时间段的Top SQL执行计划
- 使用
dba_hist_sqlstat视图跟踪同一SQL的plan_hash_value变化 - 配置OEM的执行计划变化告警
- 建立执行计划基线，监控基线的使用情况
Q7: 如何使用提示（Hints）优化执行计划？
A7: 使用提示优化执行计划的建议：
- 尽量避免使用提示，优先通过索引和统计信息优化
- 仅在必要时使用提示，如执行计划严重退化时
- 使用具体的提示，如
INDEX、HASH_JOIN、LEADING等 - 定期审查提示的有效性，避免因数据变化导致提示失效
Q8: Oracle 21c的自动索引功能值得使用吗？
A8: Oracle 21c的自动索引功能对于大多数环境是值得使用的，它可以：
- 自动识别缺少的索引
- 自动创建和测试索引
- 自动监控索引使用情况
- 自动禁用或删除未使用的索引
但在使用前需要：
- 评估系统资源状况，自动索引会消耗一定的CPU和I/O资源
- 监控自动索引的创建和使用情况
- 对关键业务SQL，仍建议手动设计索引
总结
执行计划分析是Oracle数据库性能优化的核心技能，需要DBA掌握多种工具和方法，结合实际生产环境进行综合分析。Oracle 19c和21c提供了丰富的执行计划分析工具和新特性，DBA应充分利用这些特性，建立完善的执行计划管理体系。
在实际运维中，DBA应：
- 定期监控和分析执行计划
- 为关键SQL建立执行计划基线
- 优化器统计信息管理
- 合理设计和维护索引
- 优化SQL语句设计
- 关注Oracle新版本的执行计划特性
通过持续的学习和实践，DBA可以不断提高执行计划分析能力，优化数据库性能，为业务提供高效、稳定的数据库服务。