DB / Oracle / 性能诊断

Oracle SQL 调优顾问（SQL Tuning Advisor）

2026.09.06

Oracle SQL 调优顾问（SQL Tuning Advisor）

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

1. 概述

SQL Tuning Advisor（STA） 自动分析 SQL 并提供优化建议[1]：

功能：

  • 统计信息分析
  • SQL Profile 生成
  • 访问路径分析
  • SQL 重构建议

2. 创建调优任务

2.1 从 SQL ID

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

2.2 从 SQL 文本

DECLARE
  v_task VARCHAR2(100);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT * FROM employees WHERE dept_id = 10',
    user_name => 'SCOTT',
    scope => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'tune_sql_text',
    description => 'Tune SELECT'
  );
END;
/

2.3 从 AWR

DECLARE
  v_task VARCHAR2(100);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    begin_snap => 100,
    end_snap => 110,
    task_name => 'tune_awr'
  );
END;
/

3. 执行任务

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tune_my_sql');

3.1 查看状态

SELECT status FROM user_advisor_tasks WHERE task_name = 'tune_my_sql';

4. 查看报告

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_my_sql') FROM dual;

5. 建议类型

5.1 统计信息

Recommendation: 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');

5.2 SQL Profile

Recommendation: 接受 SQL Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'tune_my_sql', name => 'my_profile');

5.3 索引建议

Recommendation: 创建索引
CREATE INDEX idx_emp_dept ON employees(dept_id);

5.4 SQL 重构

Recommendation: 使用绑定变量
原: WHERE id = 100
改: WHERE id = :1

6. 接受 SQL Profile

6.1 接受

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name => 'tune_my_sql',
  name => 'my_profile',
  force_match => TRUE
);

6.2 force_match

  • TRUE：相似 SQL 都适用
  • FALSE：仅精确 SQL

6.3 查看

SELECT 
  name, 
  sql_text, 
  status, 
  force_matching
FROM dba_sql_profiles;

6.4 删除

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('my_profile');

7. 管理 Profile

7.1 修改属性

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(
  name => 'my_profile',
  attribute_name => 'STATUS',
  value => 'DISABLED'
);

7.2 查看 Hint

SELECT 
  name, 
  type, 
  sql_text
FROM dba_sql_profiles;

8. Automatic SQL Tuning

8.1 自动任务

-- 启用
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(
  client_name => 'sql tuning advisor',
  operation => NULL,
  window_name => NULL
);

-- 查看
SELECT * FROM dba_autotask_client WHERE client_name = 'sql tuning advisor';

8.2 配置

EXEC DBMS_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
  parameter => 'ACCEPT_SQL_PROFILES',
  value => 'TRUE'
);

8.3 报告

SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM dual;

9. SQL Access Advisor

9.1 概述

  • 推荐索引/物化视图
  • 更全面的访问结构分析

9.2 创建

DECLARE
  v_task VARCHAR2(100);
BEGIN
  v_task := DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', v_task);
  DBMS_ADVISOR.SET_TASK_PARAMETER(v_task, 'EXECUTION_TYPE', 'INDEX ONLY');
  DBMS_ADVISOR.EXECUTE_TASK(v_task);
END;
/

10. 应用场景

10.1 调优慢 SQL

-- 1. 找到 SQL ID
SELECT sql_id, elapsed_time FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 5 ROWS ONLY;

-- 2. 调优
DECLARE
  v_task VARCHAR2(100);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id', task_name => 'tune');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tune');
END;
/

-- 3. 查看报告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune') FROM dual;

-- 4. 应用建议

10.2 AWR 中 Top SQL

-- 找慢 SQL
SELECT 
  sql_id, 
  elapsed_time_total
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 100 AND 110
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

-- 调优

11. 常见坑与排错

11.1 任务失败

-- 查看错误
SELECT status, error_message 
FROM user_advisor_tasks 
WHERE task_name = 'tune';

-- 重试
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tune');

11.2 建议无效

-- 1. 检查统计信息
-- 2. 手动测试
-- 3. SQL Plan Baseline 替代

11.3 Profile 不生效

-- 检查 status
SELECT name, status FROM dba_sql_profiles;

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

12. 最佳实践

  1. 定期调优 Top SQL：性能
  2. 接受 Profile：自动优化
  3. force_match：相似 SQL
  4. 结合 SQL Access Advisor：索引建议
  5. 自动任务：无需干预
  6. 测试建议：验证
  7. 监控 Profile：效果
  8. 结合 AWR：分析
  9. 结合 HINT：精准
  10. 文档化：可重复

13. 参考资料

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