DB / Oracle / 性能诊断

Oracle SQL Plan Management 详解

2026.09.06

Oracle SQL Plan Management 详解

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

SQL Plan Management (SPM) 稳定执行计划[1]：

详细见：Oracle SQL Plan Baseline 基线。

2. 组件

2.1 SQL Plan Baseline

- 计划基线
- 一组可接受计划
- 防止退化

2.2 SQL Management Base (SMB)

- 存储 Baseline
- SYSAUX 表空间
- 自动管理

3. 流程

3.1 捕获

- 自动捕获
- 或手动加载
- 存入 SMB

3.2 选择

- 优化器生成计划
- 与 Baseline 比较
- 优先 Baseline 计划

3.3 演化

- 新计划验证
- 性能更好则接受
- 加入 Baseline

4. 自动捕获

4.1 启用

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

4.2 重复 SQL

- SQL 执行 2 次
- 第二次自动捕获
- 存入 Baseline

4.3 关闭

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = FALSE;

5. 手动加载

5.1 游标缓存

DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'abc1234567890'
  );
END;
/

5.2 SQL Tuning Set

DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'my_sts',
    basic_filter => 'sql_id = ''abc1234567890'''
  );
END;
/

5.3 Stored Outline

DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.MIGRATE_STORED_OUTLINE(
    attribute_name => 'all'
  );
END;
/

6. 查看

6.1 视图

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM dba_sql_plan_baselines;

SELECT * FROM dba_sql_management_config;

6.2 详细

SELECT sql_handle, plan_name, sql_text, origin, last_modified
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%emp%';

7. 使用

7.1 启用

ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE;

7.2 选择逻辑

1. 优化器生成计划
2. 与 Baseline 比较
3. 优先 Baseline accepted 计划
4. Fixed > Accepted
5. 无匹配：新计划加入 unaccepted

8. 演化

8.1 自动

-- 自动演化任务
SELECT task_name, status 
FROM dba_advisor_executions 
WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK';

8.2 手动

DECLARE
  v_report CLOB;
BEGIN
  v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_xxx',
    plan_name => 'SQL_PLAN_yyy'
  );
END;
/

8.3 验证

- 性能更好
- 接受新计划
- 加入 Baseline

9. Fixed

9.1 固定

EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  sql_handle => 'SQL_xxx',
  plan_name => 'SQL_PLAN_yyy',
  attribute_name => 'FIXED',
  attribute_value => 'YES'
);

9.2 影响

- Fixed 计划优先
- 不演化新计划
- 稳定

10. 管理

10.1 启用/禁用

EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  sql_handle => 'SQL_xxx',
  attribute_name => 'ENABLED',
  attribute_value => 'NO'
);

10.2 删除

EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE(
  sql_handle => 'SQL_xxx',
  plan_name => 'SQL_PLAN_yyy'
);

10.3 配置

EXEC DBMS_SPM.CONFIGURE(
  parameter_name => 'SPACE_BUDGET_PERCENT',
  parameter_value => 10
);

11. 迁移

11.1 Stored Outlines

- 老 Stored Outline
- 迁移到 Baseline
- 现代化

11.2 升级

- 升级时保留
- 测试
- 演化

12. 应用场景

12.1 计划稳定

- 防止退化
- 稳定
- 生产

12.2 升级

- 升级前捕获
- 升级后验证
- 演化

12.3 应用变更

- SQL 变更
- Baseline 保护
- 测试

13. 监控

13.1 使用

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

13.2 性能

SELECT sql_id, plan_hash_value, elapsed_time
FROM v$sql
WHERE sql_id = '&sql_id';

14. 常见问题

14.1 不使用 Baseline

- enabled/accepted
- 检查
- force_match

14.2 新计划不接受

- 演化
- 手动接受
- 测试

14.3 空间

- SYSAUX
- 监控
- 配置

15. 最佳实践

  1. 捕获：生产
  2. 启用：使用
  3. 演化：定期
  4. Fixed：关键
  5. 监控：使用
  6. 测试：性能
  7. 迁移：Outline
  8. 升级：保护
  9. 文档：记录
  10. 演练：定期

16. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “SQL Plan Management” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/