为什么Oracle数据库出现大量buffer busy waits_通过ASH定位热点块-Oracle-PHP中文网

buffer busy waits 根本原因是物理块被争用，必须先定位 current_file# 和 current_block#，再结合 p3 值（如 130/i/o 缺失、220/数据块 dml 冲突、4/段头争用等）精准定因，否则调优无效。

buffer busy waits 不是 sql 写得烂，而是有物理块正被卡住——必须先定位到 current_file# 和 current_block#，否则所有调优动作都是盲打。

为什么不能直接查 SQL 文本找“热点 SQL”

很多人一看到 buffer busy waits 就去 v$sql 里搜 UPDATE 或 INSERT，结果查出一堆语句，却无法收敛。因为等待本身不绑定具体 SQL：同一块可能被多个不同语句访问（比如一个 SELECT 做一致性读、一个 UPDATE 在改数据、一个 INSERT 在扩展 HWM），它们共享同一个 current_file#/current_block#，但 SQL 文本毫无共性。
更关键的是：sql_text LIKE '%UPDATE%' 这类过滤在 ASH 里基本无效——ASH 记录的是等待发生时的上下文，不是执行开始时的 SQL。

真正有效的过滤条件只有两个：
• event IN ('buffer busy waits', 'enq: TX - row lock', 'read by other session')
• sample_time > SYSDATE - 1/24（建议严格控制在 1 小时内，避免历史噪声淹没真实热点）

用 ASH 快速聚合 Top 热点块

执行以下语句直接拿到最热的 5 个物理块：

SELECT current_file#, current_block#, COUNT(*) cnt
FROM dba_hist_active_sess_history
WHERE event = 'buffer busy waits'
  AND sample_time > SYSDATE - 1/24
GROUP BY current_file#, current_block#
ORDER BY cnt DESC
FETCH FIRST 5 ROWS ONLY;

注意：
• current_obj# = 0 很常见，不代表没对象——可能刚进入逻辑读阶段，还没完成段定位；此时更要盯紧 current_file# 和 current_block#
• 如果返回空，检查是否用了 dba_hist_active_sess_history（需 AWR license）；临时替代方案是查 v$session_wait_history，但只保留最近 10 行，可靠性低
• RAC 环境下必须加 inst_id 分组，否则会跨实例混计

根据 file_id + block_id 反查段名和类型

拿到 current_file# 和 current_block# 后，用 dba_extents 精确匹配所属段。别用模糊猜（比如看到索引名就重建索引）：

QuantOracle

63个确定性量化金融计算器 + 10个通过MCP的复合工作流。期权定价、Greeks、奇异衍生品、风险指标、投资组合优化……

下载

SELECT s.segment_name, s.partition_name, s.segment_type, s.tablespace_name
FROM dba_extents s
WHERE &block# BETWEEN s.block_id AND (s.block_id + s.blocks - 1)
  AND s.file_id = &file#;

要点：
• 一个块只属于一个 extent，但可能属于分区表的某个 partition，所以结果里 partition_name 不能为空字段
• 查不到结果？说明该块大概率是临时段、UNDO 块或回滚段头——立刻转向 dba_rollback_segs 或 v$rollstat
• 如果 segment_type = 'INDEX'，别急着重建：结合 P3 值判断是叶块（220）还是段头（4），处理方式完全不同

P3 值决定后续动作，跳过这步等于白查

p3（即 ASH 中的 event_id 或 v$session_wait 的 p3）是诊断分水岭，不同值对应完全不同的根因路径：

• p3 = 130：块不在 buffer cache，多会话抢着从磁盘读 → 本质是 I/O 延迟暴露为 buffer wait，优先查 db file sequential read 对应的 SQL 和存储响应时间
• p3 = 220：数据块行级 DML 冲突（如高并发更新同一块内不同行）→ 检查块内行密度、考虑减小 pctfree 或拆分热点表
• p3 = 4：段头争用（FREELIST 或 ASSM 位图块）→ 若是 MSSM 表空间，改用 ASSM；若是 ASSM，检查是否需要增加 initrans
• p3 = 17 或 18：UNDO 段头或 UNDO 块争用 → 查 v$undostat 看 UNDO 使用峰值，必要时增大 undo_tablespace 或调整 undo_retention