作者：Digital Observer（施嘉伟）
Oracle ACE Pro: Database
PostgreSQL ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师
公众号/墨天轮：Digital Observer；CSDN/PGfans：施嘉伟；ITPUB：sjw1933

Oracle SQL 执行计划分析与优化指南

在 Oracle 数据库中，SQL 语句的执行计划可能会因统计信息、优化器参数等因素发生变化，从而影响查询性能。本指南介绍如何查看 AWR 中的多个执行计划、分析执行计划变化、获取 SQL 报告，以及如何使用 SQL Profile 强制绑定执行计划以提升查询稳定性。

1. 查看 SQL 在数据库中的多个执行计划

当一个 SQL 语句可能存在多个执行计划时，可以使用 dbms_xplan.display_awr 来查看其历史执行计划：

SELECT * FROM TABLE(dbms_xplan.display_awr('xxxxxxxxxx'));
复制

2. 通过 AWR 数据分析执行计划变化

执行计划的波动可能会导致 SQL 语句的性能不稳定。通过以下 SQL 语句，可以查看 SQL 在不同快照时间点的执行计划变化情况：

SELECT a.INSTANCE_NUMBER, a.snap_id, a.sql_id, a.plan_hash_value, b.begin_interval_time
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.sql_id = 'xxxxxxxxxx'
AND a.snap_id = b.snap_id
ORDER BY instance_number, begin_interval_time DESC;
复制

3. 获取 SQL 性能报告

Oracle AWR 提供了 awrsqrpt.sql 脚本，可用于生成 SQL 报告，分析 SQL 语句的执行效率：

@?/rdbms/admin/awrsqrpt.sql
复制

执行后，根据提示输入相应 SQL ID，即可获取 SQL 性能报告。

4. 影响 SQL 性能的常见因素

SQL 语句的性能下降可能与以下因素相关：

• 4.1 绑定变量窥视（Bind Peeking）：优化器可能会基于首次执行时的绑定变量值生成执行计划，导致后续 SQL 运行时执行计划不匹配。
• 4.2 直方图（Histograms）的影响：如果列存在数据倾斜，直方图可能影响优化器的选择，导致执行计划变化。
• 4.3 统计信息异常：缺失或过时的统计信息可能导致优化器选择低效的执行计划。
• 4.4 cursor_sharing 等优化器参数的影响：某些优化参数的调整可能影响执行计划稳定性。
• 4.5 Rolling Cursor Invalidations 特性：游标失效可能导致执行计划变化。

5. 应急措施：强制绑定执行计划

如果 SQL 执行计划出现频繁变更，导致性能波动，可以使用 SQL Profile 强制绑定执行计划。具体步骤如下：

5.1 获取 Cursor 信息

执行 coe_xfr_sql_profile.sql 脚本，以获取 SQL ID 和对应的执行计划哈希值：

@coe_xfr_sql_profile.sql xxxxxxxx
复制

示例输出：

SQL_ID         PLAN_HASH_VALUE AVG_ET_SECS
--------------- ------------- -----------
cdwjdd67x27mh   2979024279    0.011
cdwjdd67x27mh   647855111     5.164
复制

输入希望固定的 PLAN_HASH_VALUE：

Enter value for 2: 2979024279
复制

此时，会生成 SQL Profile 绑定脚本，名称格式如下：

coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
复制

5.2 固定执行计划

在目标数据库中执行该脚本，以绑定特定执行计划：

SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
复制

这样，该 SQL 语句后续的执行都会采用 PLAN_HASH_VALUE=2979024279 的执行计划。

6. 删除 SQL Profile

如果需要取消固定的执行计划，可以使用以下命令删除 SQL Profile：

EXEC dbms_sqltune.drop_sql_profile('coe_f4sgavkagjb1q_2593387201');
复制

或者：

EXEC dbms_sqltune.drop_sql_profile('coe_7qaqzc2fah98p_2260363341');
复制

可以查询当前存在的 SQL Profile：

SELECT name, SQL_TEXT, status FROM dba_sql_profiles;
复制

7. 更新统计信息

如果因统计信息异常导致执行计划变化，可以手动更新表的统计信息：

EXEC dbms_stats.set_table_stats('TEST1', 'T1', numrows => 49953);
复制

总结

• 通过 dbms_xplan.display_awr 查询 SQL 语句的历史执行计划。
• 结合 AWR 快照 (dba_hist_sqlstat 和 dba_hist_snapshot) 分析执行计划变化趋势。
• 使用 awrsqrpt.sql 获取 SQL 性能报告。
• 识别 SQL 性能下降的常见原因，如绑定变量窥视、直方图、统计信息异常等。
• 当 SQL 计划频繁变化时，可使用 SQL Profile 固定执行计划，提升查询稳定性。
• 若 SQL Profile 不再适用，可使用 dbms_sqltune.drop_sql_profile 进行删除。