作者：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_timeFROM dba_hist_sqlstat a, dba_hist_snapshot bWHERE a.sql_id = 'xxxxxxxxxx'AND a.snap_id = b.snap_idORDER 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进行删除。
