Oracle SQL Performance Analyzer 详解

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

1. 概述

SQL Performance Analyzer (SPA) 评估变更对 SQL 性能影响[1]：

2. 应用场景

2.1 升级

- 数据库升级
- 优化器变更
- 参数变更

2.2 配置变更

- 参数修改
- 索引变更
- 统计信息

2.3 应用变更

- SQL 修改
- Schema 变更
- 评估影响

3. 流程

3.1 概述

1. 收集 SQL 工作负载（STS）
2. 执行变更前（Before）
3. 应用变更
4. 执行变更后（After）
5. 对比分析
6. 报告

3.2 步骤

-- 1. 创建分析任务
DECLARE
  v_task VARCHAR2(30);
BEGIN
  v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
    sqlset_name => 'my_sts',
    task_name => 'spa_task'
  );
END;
/

-- 2. 执行变更前
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => 'spa_task',
  execution_type => 'TEST EXECUTE',
  execution_name => 'before'
);

-- 3. 应用变更（升级/参数等）

-- 4. 执行变更后
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => 'spa_task',
  execution_type => 'TEST EXECUTE',
  execution_name => 'after'
);

-- 5. 对比
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => 'spa_task',
  execution_type => 'COMPARE',
  execution_name => 'compare'
);

-- 6. 报告
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('spa_task') FROM dual;

4. STS

4.1 创建

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET('my_sts');
END;
/

4.2 加载

-- 从 AWR
DECLARE
  v_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN v_cur FOR 
    SELECT VALUE(p) FROM TABLE(
      DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
        begin_snap => 1, end_snap => 100
      )
    ) p;
  
  DBMS_SQLTUNE.LOAD_SQLSET('my_sts', v_cur);
END;
/

-- 从游标缓存
DECLARE
  v_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN v_cur FOR 
    SELECT VALUE(p) FROM TABLE(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
        basic_filter => 'parsing_schema_name = ''SCOTT'''
      )
    ) p;
  
  DBMS_SQLTUNE.LOAD_SQLSET('my_sts', v_cur);
END;
/

5. 报告

5.1 类型

-- 文本
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  task_name => 'spa_task',
  type => 'TEXT'
) FROM dual;

-- HTML
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  task_name => 'spa_task',
  type => 'HTML'
) FROM dual;

-- XML
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  task_name => 'spa_task',
  type => 'XML'
) FROM dual;

5.2 级别

- BASIC
- TYPICAL
- ALL

5.3 内容

- 改善的 SQL
- 退化的 SQL
- 不变的 SQL
- 错误的 SQL

6. 对比指标

6.1 选项

- ELAPSED_TIME
- CPU_TIME
- BUFFER_GETS
- DISK_READS
- DIRECT_WRITES
- OPTIMIZER_COST

6.2 配置

EXEC DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
  task_name => 'spa_task',
  parameter => 'comparison_metric',
  value => 'BUFFER_GETS'
);

7. 防止退化

7.1 SQL Plan Baseline

- 退化 SQL
- 创建 Baseline
- 固定计划

7.2 流程

-- 退化 SQL 创建 Baseline
DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id'
  );
END;
/

详细见：Oracle SQL Plan Baseline 基线。

8. Database Replay

8.1 对比

- SPA：SQL 级
- Database Replay：系统级
- 互补

8.2 配合

- SPA 分析 SQL
- Database Replay 整体
- 综合评估

9. 应用场景

9.1 升级评估

- 升级前
- SPA 测试
- 评估影响
- 准备 Baseline

9.2 参数变更

- optimizer_mode
- 内存参数
- 评估

9.3 索引变更

- 新建/删除索引
- SPA 评估
- 决策

9.4 应用发布

- SQL 修改
- SPA 测试
- 验证

10. 监控

10.1 任务

SELECT task_name, status, execution_count
FROM dba_advisor_tasks
WHERE advisor_name = 'SQL Performance Analyzer';

10.2 执行

SELECT execution_name, execution_type, status
FROM dba_advisor_executions
WHERE task_name = 'spa_task';

11. 常见问题

11.1 执行时间长

- STS 太大
- 限制 SQL 数
- 采样

11.2 结果不准

- 数据差异
- 测试环境
- 数据同步

11.3 退化 SQL

- Baseline
- Hint
- 优化

12. 最佳实践

STS 收集：代表性
测试环境：与生产一致
数据同步：相同
对比：多指标
Baseline：退化 SQL
测试：完整
报告：详细
文档：记录
演练：升级前
复盘：总结

13. 参考资料

[1] Oracle Database Testing Guide 19c, “SQL Performance Analyzer” https://docs.oracle.com/en/database/oracle/oracle-database/19/ratup/