Oracle 自动化诊断（ADDM / ASH / AWR）

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

1. 概述

Oracle 自动诊断工具[1]：

工具	说明
AWR	性能数据仓库
ADDM	自动诊断引擎
ASH	活跃会话历史
ADR	诊断数据仓库

2. AWR 详解

2.1 配置

SELECT * FROM dba_hist_wr_control;

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  retention => 43200,  -- 30 天
  interval => 30       -- 30 分钟
);

2.2 手动快照

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

2.3 报告

@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrgrpt.sql  -- RAC

详细见：Oracle AWR 性能报告。

3. ADDM

3.1 自动任务

SELECT * FROM dba_advisor_tasks WHERE advisor_name = 'ADDM';

3.2 手动报告

@?/rdbms/admin/addmrpt.sql

3.3 手动分析

VAR tname VARCHAR2(50);
BEGIN
  :tname := 'ADDM_TASK';
  DBMS_ADVISOR.CREATE_TASK('ADDM', :tname);
  DBMS_ADVISOR.SET_TASK_PARAMETER(:tname, 'START_SNAPSHOT', 100);
  DBMS_ADVISOR.SET_TASK_PARAMETER(:tname, 'END_SNAPSHOT', 110);
  DBMS_ADVISOR.EXECUTE_TASK(:tname);
END;
/

SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname) FROM dual;

3.4 查看建议

SELECT * FROM dba_advisor_findings 
WHERE task_name = 'ADDM_TASK';

4. ASH

4.1 概述

每秒采样活跃会话
V$ACTIVE_SESSION_HISTORY：内存
DBA_HIST_ACTIVE_SESS_HISTORY：磁盘

4.2 报告

@?/rdbms/admin/ashrpt.sql

4.3 查询

-- Top SQL
SELECT 
  sql_id,
  COUNT(*) AS samples,
  ROUND(COUNT(*) * 10 / 60, 2) AS avg_active_secs
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

4.4 Top 等待

SELECT 
  event,
  wait_class,
  COUNT(*) AS waits
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY event, wait_class
ORDER BY waits DESC;

4.5 阻塞会话

SELECT 
  blocking_session,
  session_id,
  event,
  COUNT(*)
FROM v$active_session_history
WHERE blocking_session IS NOT NULL
  AND sample_time > SYSDATE - 1/24
GROUP BY blocking_session, session_id, event;

5. ADR

5.1 概述

Automatic Diagnostic Repository
统一日志/trace

5.2 查看

SELECT * FROM v$diag_info;

5.3 ADRCI

adrci

# 命令
show home
show problem
show incident
show alert

6. 诊断案例

6.1 慢 SQL 诊断

-- 1. AWR 找慢 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 5 ROWS ONLY;

-- 2. ASH 分析
SELECT event, COUNT(*)
FROM dba_hist_active_sess_history
WHERE sql_id = '&sql_id'
GROUP BY event;

-- 3. 执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

6.2 等待事件分析

-- ASH 找等待
SELECT 
  event,
  wait_class,
  COUNT(*) AS waits
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 100 AND 110
GROUP BY event, wait_class
ORDER BY waits DESC;

6.3 锁阻塞

SELECT 
  blocking_session,
  session_id,
  event,
  sql_id,
  sample_time
FROM dba_hist_active_sess_history
WHERE blocking_session IS NOT NULL
  AND snap_id BETWEEN 100 AND 110;

7. ADDM 报告解读

7.1 概要

分析时段
DB Time
关键问题

7.2 问题

TOP 等待事件
慢 SQL
资源瓶颈

7.3 建议

操作类型
影响范围
实施建议

8. 自动化任务

8.1 查看自动任务

SELECT * FROM dba_autotask_client;

8.2 启用/禁用

EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(
  client_name => 'auto optimizer stats collection',
  operation => NULL,
  window_name => NULL
);

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(...);

8.3 窗口

SELECT * FROM dba_autotask_window_clients;

9. 基线

9.1 创建

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
  start_snap_id => 100,
  end_snap_id => 110,
  baseline_name => 'good_perf'
);

9.2 模板

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
  template_name => 'weekly',
  template_type => 'REPEATING',
  day_of_week => 'MONDAY',
  hour_in_day => 9
);

10. 常见坑与排错

10.1 AWR 数据丢失

-- 1. 检查保留期
-- 2. 检查 SYSAUX 空间
SELECT * FROM v$sysaux_occupants WHERE occupant_name LIKE '%AWR%';

10.2 ADDM 无建议

-- 1. 检查任务
SELECT * FROM dba_advisor_tasks;
-- 2. 状态
-- 3. 错误

10.3 ASH 数据少

-- 1. 检查采样
SHOW PARAMETER ash

11. 最佳实践

定期采集 AWR：1 小时
保留 30 天：分析
ADDM 自动诊断：建议
ASH 实时分析：定位
基线对比：异常
ADR 统一日志：管理
ADRCI 工具：命令行
监控 SYSAUX：空间
自动任务启用：维护
历史对比：趋势

12. 参考资料

[1] Oracle Database Performance Tuning Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/