Oracle Maclean ASH 实战分析

来源：Maclean Liu (刘相兵) / oradb.me 适用版本：Oracle Database 10g+ 文档版本：v1.0 / 2026-07-22

---

1. 概述

ASH 是 Maclean 性能诊断的核心工具[1]。

详细见：Oracle ASH 性能分析实战。

---

2. ASH 基础

2.1 采样

- 每秒采样活跃会话
- 内存（v$active_session_history）
- 持久化（wrh$_active_session_history）

2.2 优势

- 实时
- 历史
- 细粒度
- 短查询捕获

2.3 Maclean 观点

- ASH 是性能诊断瑞士军刀
- 实时 + 历史
- 关键工具

---

3. 实时分析

3.1 当前活跃

SELECT sid, serial#, session_id, event, sql_id, wait_class
FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24
ORDER BY sample_time DESC;

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 SQL

SELECT sql_id, count(*) AS samples
FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24 AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY samples DESC;

---

4. 历史分析

4.1 dba_hist_active_sess_history

SELECT event, count(*) 
FROM dba_hist_active_sess_history 
WHERE sample_time BETWEEN 
  TO_DATE('2026-07-22 10:00','YYYY-MM-DD HH24:MI') AND
  TO_DATE('2026-07-22 11:00','YYYY-MM-DD HH24:MI')
GROUP BY event ORDER BY count(*) DESC;

4.2 时间段

-- 找出问题时段
SELECT TO_CHAR(sample_time,'YYYY-MM-DD HH24:MI'), count(*)
FROM dba_hist_active_sess_history 
WHERE sample_time > SYSDATE-1
GROUP BY TO_CHAR(sample_time,'YYYY-MM-DD HH24:MI')
ORDER BY 1;

4.3 Maclean 方法

- 找高峰
- 聚焦分析
- 关联 SQL

---

5. 会话分析

5.1 TOP 会话

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

5.2 用户

SELECT u.username, count(*) AS samples
FROM v$active_session_history h, dba_users u
WHERE h.user_id = u.user_id
  AND h.sample_time > SYSDATE-1/24
GROUP BY u.username
ORDER BY samples DESC;

5.3 模块

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

---

6. SQL 分析

6.1 SQL 文本

SELECT sql_text FROM v$sql WHERE sql_id='&sql_id';

6.2 执行计划

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

6.3 历史执行

SELECT snap_id, plan_hash_value, executions, elapsed_time
FROM dba_hist_sqlstat WHERE sql_id='&sql_id';

6.4 Maclean 分析

- 找出 SQL
- 看执行计划
- 优化

---

7. 等待事件分析

7.1 P1/P2/P3

SELECT event, p1, p2, p3, count(*)
FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24
  AND event = '&event'
GROUP BY event, p1, p2, p3;

7.2 定位对象

-- db file sequential read
SELECT owner, segment_name 
FROM dba_extents 
WHERE file_id = &p1 AND &p2 BETWEEN block_id AND block_id+blocks-1;

7.3 Maclean 方法

- 等待事件
- P1/P2/P3
- 定位对象
- 优化

---

8. 阻塞分析

8.1 阻塞链

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

8.2 阻塞者

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

8.3 Maclean 分析

- 找阻塞者
- 终止（必要时）
- 优化应用

---

9. TOP SQL

9.1 CPU 消耗

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

9.2 I/O 等待

SELECT sql_id, count(*) AS samples
FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24
  AND wait_class IN ('User I/O','System I/O')
GROUP BY sql_id ORDER BY samples DESC;

---

10. ASH 报告

10.1 生成

@?/rdbms/admin/ashrpt.sql

10.2 内容

- TOP SQL
- TOP 等待
- TOP 会话
- 阻塞
- 活动

10.3 Maclean 推荐

- 问题时段
- 定向分析
- 关键工具

---

11. 案例：CPU 飙高

11.1 现象

- CPU 100%
- 应用慢

11.2 Maclean 诊断

SELECT sql_id, count(*) FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24 AND wait_class='CPU'
GROUP BY sql_id ORDER BY count(*) DESC;

11.3 优化

- TOP SQL
- 加索引/绑定变量
- 验证

---

12. 案例：间歇慢

12.1 现象

- 偶发慢
- 难复现

12.2 Maclean 诊断

- 历史 ASH
- 找慢时段
- 关联 SQL

12.3 优化

- 锁等待
- 长事务
- 优化

---

13. 案例：I/O 高

13.1 现象

- 磁盘 I/O 高
- db file scattered read

13.2 Maclean 诊断

SELECT sql_id, count(*) FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24 AND event='db file scattered read'
GROUP BY sql_id ORDER BY count(*) DESC;

13.3 优化

- 全表扫描
- 加索引
- 分区

---

14. Maclean ASH 脚本集

14.1 常用

-- 实时 TOP 等待
SELECT event, count(*) FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24
GROUP BY event ORDER BY count(*) DESC;

-- 实时 TOP SQL
SELECT sql_id, count(*) FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24
GROUP BY sql_id ORDER BY count(*) DESC;

-- 阻塞链
SELECT session_id, blocking_session, event 
FROM v$active_session_history 
WHERE sample_time > SYSDATE-1/24 AND blocking_session IS NOT NULL;

14.2 工具

- ashrpt.sql
- 自定义脚本
- OEM

---

15. 最佳实践

1. 实时：v$active_session_history
2. 历史：dba_hist_active_sess_history
3. TOP：聚焦
4. SQL：关联
5. 阻塞：分析
6. P1/P2/P3：定位
7. 报告：ashrpt
8. 案例：学习
9. 监控：持续
10. 原理：理解

---

16. 参考资料

[1] Maclean Liu, “ASH 实战”, https://oradb.me [2] Oracle Database Performance Tuning Guide 19c