DB / Oracle / SQL 开发

Oracle SQL 调优顾问详解

2026.09.06

Oracle SQL 调优顾问详解

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

1. 概述

SQL 调优顾问自动优化 SQL[1]：

详细见：Oracle SQL 调优顾问。

2. SQL Tuning Advisor

2.1 创建任务

DECLARE
  v_task VARCHAR2(30);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => '&sql_id',
    task_name => 'tune_task_1'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tune_task_1');
END;
/

2.2 报告

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_task_1') FROM dual;

2.3 文本

DECLARE
  v_task VARCHAR2(30);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT * FROM employees WHERE dept_id = 10',
    task_name => 'tune_text_1'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tune_text_1');
END;
/

3. 接受 Profile

3.1 SQL Profile

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name => 'tune_task_1',
  name => 'profile_1',
  force_match => TRUE
);

3.2 查看

SELECT name, status, type, force_matching 
FROM dba_sql_profiles;

3.3 管理

-- 禁用
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('profile_1', 'STATUS', 'DISABLED');

-- 启用
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('profile_1', 'STATUS', 'ENABLED');

-- 删除
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('profile_1');

4. Automatic Tuning

4.1 启用

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL
  );
END;
/

4.2 配置

BEGIN
  DBMS_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
    parameter => 'ACCEPT_SQL_PROFILES',
    value => 'TRUE'
  );
END;
/

4.3 报告

SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM dual;

5. SQL Access Advisor

5.1 创建

DECLARE
  v_task VARCHAR2(30);
BEGIN
  v_task := DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', NULL, 'access_task_1');
END;
/

5.2 工作负载

-- SQL Tuning Set
DECLARE
  v_set VARCHAR2(30) := 'my_sts';
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(v_set);
  -- 加载 SQL
END;
/

5.3 执行

EXEC DBMS_ADVISOR.EXECUTE_TASK('access_task_1');

5.4 报告

SELECT DBMS_ADVISOR.GET_TASK_REPORT('access_task_1') FROM dual;

6. SQL Tuning Set

6.1 创建

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

6.2 加载

-- 从游标缓存
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''',
        ranking_measure1 => 'elapsed_time'
      )
    ) p;
  
  DBMS_SQLTUNE.LOAD_SQLSET('my_sts', v_cur);
END;
/

-- 从 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;
/

6.3 查看

SELECT sql_text, executions, elapsed_time
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('my_sts'));

6.4 删除

EXEC DBMS_SQLTUNE.DROP_SQLSET('my_sts');

7. SQL Plan Baseline

7.1 捕获

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

7.2 加载

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

7.3 演化

DECLARE
  v_report CLOB;
BEGIN
  v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => '&handle'
  );
END;
/

7.4 查看

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

7.5 固定

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

详细见：Oracle SQL Plan Baseline 基线。

8. SQL Monitor

8.1 实时

SELECT sql_id, status, elapsed_time, cpu_time
FROM v$sql_monitor
WHERE status = 'EXECUTING';

8.2 报告

-- 文本
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

-- HTML
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => '&sql_id',
  type => 'HTML'
) FROM dual;

9. 优化建议

9.1 统计信息

- 收集
- 直方图
- 扩展

9.2 索引

- 创建
- 重建
- 删除未用

9.3 SQL 重写

- 函数阻止索引
- 绑定变量
- JOIN 优化

9.4 Profile

- 自动调整
- 不改 SQL
- 兼容

10. 自动诊断

10.1 ADDM

@?/rdbms/admin/addmrpt.sql

10.2 ASH

@?/rdbms/admin/ashrpt.sql

10.3 AWR

@?/rdbms/admin/awrrpt.sql

详细见：Oracle AWR 详解。

11. 应用场景

11.1 慢 SQL

- 自动分析
- 建议
- Profile

11.2 索引建议

- SQL Access Advisor
- 自动建议

11.3 计划稳定

- Baseline
- 防止回退

12. 性能

12.1 资源

- 调优消耗
- 限制时间
- 并行

12.2 限制

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
  task_name => 'tune_task_1',
  parameter => 'TIME_LIMIT',
  value => 3600
);

13. 常见坑与排错

13.1 任务失败

SELECT task_name, status, execution_end 
FROM dba_advisor_log 
WHERE task_name = 'tune_task_1';

13.2 Profile 不生效

- force_match
- 启用
- 验证

13.3 Baseline 不用

- accepted
- fixed
- 启用

14. 最佳实践

  1. 自动调优：启用
  2. STS 收集：TOP SQL
  3. Profile 接受：谨慎
  4. Baseline 演化：稳定
  5. 监控：报告
  6. 统计信息：基础
  7. 索引：建议
  8. 测试：验证
  9. 文档：记录
  10. 持续：优化

15. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “SQL Tuning Advisor” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-tuning-advisor.html