Oracle Maclean 性能诊断方法论
Oracle Maclean 性能诊断方法论
来源：Maclean Liu (刘相兵) / oradb.me 适用版本：Oracle Database 全版本 文档版本：v1.0 / 2026-07-22
1. 关于 Maclean Liu
Maclean Liu（刘相兵），Oracle ACE，前阿里数据库专家，擅长性能诊断[1]。
- 博客：oradb.me
- 深入 Latch/Mutex、ASH
- 系统化诊断方法论
2. Maclean 诊断方法论
2.1 核心思想
- 等待事件为入口
- ASH 为工具
- 系统性分析
- 数据驱动2.2 步骤
1. 现象描述
2. 数据收集（AWR/ASH）
3. TOP 等待
4. 关联 SQL/会话
5. 根因分析
6. 优化
7. 验证3. 数据收集
3.1 AWR
@?/rdbms/admin/awrrpt.sql3.2 ASH
@?/rdbms/admin/ashrpt.sql3.3 alert log
# 查看 alert log
$ORACLE_BASE/diag/rdbms/prod/prod/trace/alert_prod.log3.4 OS 数据
# CPU
top
vmstat 1 10
# I/O
iostat -x 1 10
# 网络
sar -n DEV 1 104. TOP 等待分析
4.1 系统级
SELECT event, time_waited, total_waits, average_wait
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;4.2 Maclean 分析
- User I/O：评估 SQL
- System I/O：评估磁盘
- Concurrency：评估争用
- Application：评估应用4.3 Wait Class
SELECT wait_class, time_waited
FROM v$system_wait_class
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC;5. SQL 关联
5.1 TOP SQL
SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;5.2 SQL 文本
SELECT sql_text FROM v$sql WHERE sql_id='&sql_id';5.3 执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));6. 会话级
6.1 活跃会话
SELECT sid, serial#, username, event, sql_id, state, seconds_in_wait
FROM v$session
WHERE username IS NOT NULL AND status='ACTIVE';6.2 会话统计
SELECT s.sid, n.name, s.value
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND s.sid = &sid
AND s.value > 0
ORDER BY s.value DESC;6.3 阻塞
SELECT sid, blocking_session, event
FROM v$session WHERE blocking_session IS NOT NULL;7. ASH 深度分析
7.1 实时
SELECT event, count(*)
FROM v$active_session_history
WHERE sample_time > SYSDATE-1/24
GROUP BY event ORDER BY count(*) DESC;7.2 SQL TOP
SELECT sql_id, count(*)
FROM v$active_session_history
WHERE sample_time > SYSDATE-1/24 AND sql_id IS NOT NULL
GROUP BY sql_id ORDER BY count(*) DESC;7.3 会话 TOP
SELECT session_id, session_serial#, count(*)
FROM v$active_session_history
WHERE sample_time > SYSDATE-1/24
GROUP BY session_id, session_serial#
ORDER BY count(*) DESC;7.4 Maclean 推荐
- ASH 是核心
- 实时 + 历史
- 短查询8. 时间模型
8.1 DB Time
- DB Time = DB CPU + Wait Time
- DB Time / Elapsed = 平均活跃会话数8.2 查看
SELECT stat_name, value
FROM v$sys_time_model
WHERE stat_name IN ('DB time','DB CPU','background elapsed time');8.3 Maclean 分析
- DB Time 高 → 数据库忙
- DB CPU 高 → CPU 密集
- Wait Time 高 → I/O 或锁9. Latch/Mutex 分析
9.1 Latch TOP
SELECT name, gets, misses, spin_gets, sleep1
FROM v$latch
ORDER BY misses DESC
FETCH FIRST 10 ROWS ONLY;9.2 Latch Children
SELECT * FROM v$latch_children
WHERE name = 'cache buffers chains'
ORDER BY misses DESC FETCH FIRST 10 ROWS ONLY;9.3 Maclean 分析
- misses 高 → 争用
- 找热块
- 优化详细见：Oracle-Maclean-Latch与Mutex深入.md。
10. 段级分析
10.1 热点段
SELECT owner, object_name, statistic_name, value
FROM v$segment_statistics
WHERE value > 0
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;10.2 Maclean 分析
- 行锁/块争用
- 热点段
- 优化11. OS 诊断
11.1 CPU
top
# us 高：CPU 计算
# sy 高：系统调用
# wa 高：I/O 等待11.2 I/O
iostat -x 1 10
# %util 高：磁盘忙
# await 高：延迟11.3 内存
free -m
vmstat 1 1012. Maclean 案例
12.1 案例：CPU 100%
- ASH 分析
- TOP SQL
- 绑定变量缺失
- 优化12.2 案例：I/O 高
- db file scattered read
- 全表扫描
- 加索引12.3 案例：锁等待
- enq: TX - row lock
- 长事务
- 终止12.4 案例：Library Cache
- latch: shared pool
- 未用绑定变量
- 共享池13. 诊断脚本
13.1 Maclean 常用
-- TOP 等待
SELECT event, time_waited FROM v$system_event
WHERE wait_class != 'Idle' ORDER BY time_waited DESC FETCH FIRST 10 ROWS ONLY;
-- TOP SQL
SELECT sql_id, elapsed_time FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
-- 活跃会话
SELECT sid, event, sql_id FROM v$session WHERE status='ACTIVE' AND username IS NOT NULL;13.2 工具
- AWR
- ASH
- TFA
- 自定义脚本14. Maclean 名言
"数据说话"
"ASH 是性能诊断的瑞士军刀"
"等待事件是入口，不是出口"15. 最佳实践
- 数据驱动：AWR/ASH
- TOP：聚焦
- 系统性：分析
- SQL 关联：定位
- OS：辅助
- 案例：学习
- 脚本：积累
- 验证：优化
- 监控：持续
- 原理：理解
16. 参考资料
[1] Maclean Liu, https://oradb.me [2] Oracle Database Performance Tuning Guide 19c