DB / Oracle / 性能诊断

Oracle ASH 报告深度分析

2026.09.06

Oracle ASH 报告深度分析

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

1. 概述

ASH（Active Session History） 每秒采样活跃会话[1]：

优势：

  • 实时性
  • 细粒度
  • 短期问题定位

2. ASH 报告

2.1 生成

@?/rdbms/admin/ashrpt.sql

2.2 选择时段

- 当前（sysdate - 5/1440）
- 自定义

2.3 报告章节

  • Top User Events
  • Top Background Events
  • Top Cluster Events
  • Top SQL
  • Top Sessions
  • Top Blocking Sessions

3. 关键查询

3.1 Top SQL

SELECT 
  sql_id,
  COUNT(*) AS samples,
  ROUND(COUNT(*) * 10 / 60, 2) AS 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;

3.2 Top 等待

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

3.3 Top Session

SELECT 
  session_id, 
  session_serial#,
  user_id,
  program,
  COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY session_id, session_serial#, user_id, program
ORDER BY samples DESC;

3.4 Top Object

SELECT 
  o.object_name,
  h.current_obj#,
  COUNT(*) AS samples
FROM v$active_session_history h, dba_objects o
WHERE h.current_obj# = o.object_id(+)
  AND h.sample_time > SYSDATE - 1/24
GROUP BY o.object_name, h.current_obj#
ORDER BY samples DESC;

4. 阻塞分析

4.1 阻塞会话

SELECT 
  blocking_session,
  blocking_session_serial#,
  session_id,
  event,
  sql_id,
  sample_time
FROM v$active_session_history
WHERE blocking_session IS NOT NULL
  AND sample_time > SYSDATE - 1/24
ORDER BY sample_time;

4.2 阻塞链

-- 阻塞链
SELECT 
  level,
  session_id,
  blocking_session,
  event
FROM (
  SELECT session_id, blocking_session, event
  FROM v$active_session_history
  WHERE sample_time > SYSDATE - 1/24
)
CONNECT BY PRIOR session_id = blocking_session
START WITH blocking_session IS NULL;

5. 短期问题

5.1 短事件分析

-- 1 秒级别采样
SELECT 
  TO_CHAR(sample_time, 'HH24:MI:SS') AS t,
  session_id,
  event,
  sql_id
FROM v$active_session_history
WHERE sample_time BETWEEN 
  TO_DATE('2026-07-21 15:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
  TO_DATE('2026-07-21 15:30:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY sample_time;

5.2 突发问题

-- 短时间高负载
SELECT 
  TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI'),
  COUNT(DISTINCT session_id) AS active_sessions
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')
ORDER BY 1;

6. SQL 性能分析

6.1 SQL 等待事件

SELECT 
  sql_id,
  event,
  COUNT(*) AS samples
FROM v$active_session_history
WHERE sql_id = '&sql_id'
  AND sample_time > SYSDATE - 1/24
GROUP BY sql_id, event
ORDER BY samples DESC;

6.2 SQL 执行历史

SELECT 
  TO_CHAR(sample_time, 'HH24:MI') AS t,
  COUNT(*) AS samples
FROM v$active_session_history
WHERE sql_id = '&sql_id'
GROUP BY TO_CHAR(sample_time, 'HH24:MI')
ORDER BY 1;

7. 历史数据

7.1 DBA_HIST_ACTIVE_SESS_HISTORY

-- 磁盘历史（30 分钟采样）
SELECT * FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 7;

7.2 ASH vs AWR

  • ASH：1 秒采样，内存，1 周
  • AWR：30 分钟采样，磁盘

8. Top Consumer

8.1 Top SQL by DB Time

SELECT 
  sql_id,
  SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS on_cpu,
  SUM(CASE WHEN session_state = 'WAITING' THEN 1 ELSE 0 END) AS waiting,
  COUNT(*) AS total_samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY sql_id
ORDER BY total_samples DESC;

8.2 Top Session by DB Time

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

9. 报告解读

9.1 Top User Events

  • 主要用户事件
  • 占比

9.2 Top SQL

  • 高负载 SQL
  • 计划/执行

9.3 Top Sessions

  • 活跃会话
  • 程序

9.4 Top Blocking

  • 阻塞源

10. 实战案例

10.1 案例：15 分钟慢

1. ASH 报告 14:50-15:05
2. Top Event: enq: TX - row lock contention
3. Top SQL: UPDATE orders WHERE id = :1
4. 阻塞会话: sid=123, serial#=456
5. 杀掉会话
6. 优化事务顺序

10.2 案例：CPU 突增

1. ASH 时间段
2. Top SQL ON CPU
3. SQL 执行计划：全表
4. 加索引
5. CPU 下降

11. 常见坑与排错

11.1 数据不足

-- 1. 检查 ash 采样
SHOW PARAMETER ash

-- 2. 检查内存
SELECT * FROM v$ash_info;

11.2 短问题遗漏

-- ASH 1 秒采样
-- 短问题可能错过
-- 使用 v$session 实时

12. 最佳实践

  1. ASH 实时分析：短期问题
  2. Top SQL 定位：瓶颈
  3. 阻塞分析：锁
  4. 历史对比：趋势
  5. 结合 AWR：长期
  6. 结合 SQL Monitor：执行
  7. 时段精准：定位
  8. 采样足够：数据
  9. 结合 ADDM：建议
  10. 文档化：积累

13. 参考资料

[1] Oracle Database Performance Tuning Guide 19c, “ASH” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/active-session-history.html