DB / Oracle / ACE 精选

Oracle Jonathan Lewis 执行计划稳定性

2026.09.06

Oracle Jonathan Lewis 执行计划稳定性

来源：Jonathan Lewis / jonathanlewis.wordpress.com 适用版本：Oracle Database 10g+ 文档版本：v1.0 / 2026-07-22

1. 概述

执行计划稳定性是 Jonathan Lewis 的重要话题[1]。

详细见：Oracle SQL Plan Baseline 详解。

2. 执行计划不稳定

2.1 原因

- 统计信息变化
- 数据分布变化
- 参数变化
- 绑定变量窥探
- 软件升级

2.2 影响

- 性能突变
- 业务影响
- 难诊断

2.3 Jonathan 观点

- 执行计划突变常见
- 需要稳定机制
- 监控

3. 稳定方法

3.1 Stored Outlines

- 8i+
- 旧机制
- 逐渐弃用

3.2 SQL Profile

- 10g+
- 自动调优
- 辅助信息

3.3 SQL Plan Baseline

- 11g+
- 推荐
- 防止回归

3.4 SQL Patch

- 11g+
- 注入 HINT
- 临时

3.5 Jonathan 推荐

- Baseline 优先
- Profile 次之
- Outlines 弃用

4. SQL Profile

4.1 创建

-- 自动
DECLARE
  v_sql_id VARCHAR2(13) := '&sql_id';
BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name => 'task_' || v_sql_id,
    name => 'profile_' || v_sql_id,
    FORCE_MATCH => TRUE);
END;
/

4.2 查看

SELECT name, type, status, force_matching 
FROM dba_sql_profiles;

4.3 删除

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('profile_abc');

4.4 Jonathan 分析

- Profile 辅助 CBO
- 不强制
- 灵活

5. SQL Plan Baseline

5.1 捕获

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE;

5.2 使用

ALTER SYSTEM SET optimizer_use_sql_plan_baselines=TRUE;

5.3 查看

SELECT sql_handle, plan_name, enabled, accepted 
FROM dba_sql_plan_baselines;

5.4 演化

-- 自动
SELECT * FROM dba_sql_plan_baselines WHERE accepted='NO';

-- 手动
DECLARE
  v_report CLOB;
BEGIN
  v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => '&sql_handle');
  DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

5.5 固定

-- 固定计划
DECLARE
  v_plans PLS_INTEGER;
BEGIN
  v_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => '&sql_handle',
    plan_name => '&plan_name',
    attribute_name => 'fixed',
    attribute_value => 'YES');
END;
/

5.6 Jonathan 建议

- 捕获历史
- 防止回归
- 演化新计划

6. SQL Patch

6.1 创建

DECLARE
  v_patch_name VARCHAR2(30);
BEGIN
  v_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id => '&sql_id',
    hint_text => 'INDEX(emp idx_emp)',
    name => 'patch_abc');
END;
/

6.2 查看

SELECT name, status, force_matching 
FROM dba_sql_patches;

6.3 删除

EXEC DBMS_SQLDIAG.DROP_SQL_PATCH('patch_abc');

6.4 Jonathan 用途

- 注入 HINT
- 临时
- 不改代码

7. 统计信息管理

7.1 锁定

-- 锁定表统计
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT','EMP');

-- 解锁
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT','EMP');

7.2 备份

-- 创建备份表
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT','STAT_BACKUP');

-- 导出
EXEC DBMS_STATS.EXPORT_TABLE_STATS('SCOTT','EMP','STAT_BACKUP');

7.3 恢复

EXEC DBMS_STATS.IMPORT_TABLE_STATS('SCOTT','EMP','STAT_BACKUP');

7.4 Jonathan 建议

- 关键表锁定
- 备份统计
- 可恢复

8. 监控

8.1 计划变化

SELECT sql_id, plan_hash_value, count(*) 
FROM dba_hist_sqlstat 
WHERE sql_id = '&sql_id'
GROUP BY sql_id, plan_hash_value;

8.2 Baseline 使用

SELECT sql_handle, plan_name, enabled, accepted, executions
FROM dba_sql_plan_baselines;

8.3 性能回归

SELECT snap_id, plan_hash_value, elapsed_time 
FROM dba_hist_sqlstat 
WHERE sql_id = '&sql_id'
ORDER BY snap_id;

9. 升级稳定

9.1 升级前

- 收集所有 SQL
- 导出执行计划
- Baseline

9.2 升级后

- 启用 Baseline
- 监控
- 演化

9.3 Jonathan 强调

- 升级风险高
- Baseline 保护
- 渐进

10. 案例：性能突变

10.1 现象

- SQL 性能突变
- 执行计划变化

10.2 Jonathan 诊断

1. 查看历史计划
2. 查看统计信息变化
3. 找出根因
4. Baseline 固定

10.3 修复

- 恢复旧统计
- 或 Baseline 固定
- 验证

11. Jonathan 方法论

11.1 预防

- Baseline 捕获
- 监控
- 文档

11.2 响应

- 查找根因
- 恢复
- 优化

11.3 改进

- 演化新计划
- 评估
- 接受

12. 常见问题

12.1 Baseline 不生效

- 参数
- enabled/accepted
- 检查

12.2 Profile 失效

- 统计信息变化大
- 重新调优

12.3 升级回归

- Baseline 保护
- 评估
- 演化

13. 最佳实践

  1. Baseline：捕获
  2. 统计信息：准确
  3. 监控：计划变化
  4. Profile：调优
  5. Patch：临时
  6. 锁定：关键表
  7. 备份：统计
  8. 演化：新计划
  9. 升级：保护
  10. 文档：记录

14. 参考资料

[1] Jonathan Lewis, https://jonathanlewis.wordpress.com [2] Oracle Database SQL Tuning Guide 19c, “SQL Plan Management”