面试宝典: Oracle数据库buffer busy waits等待事件处理过程

Oracle数据库中的buffer busy waits等待事件是反映内存缓冲区争用的关键性能指标，多由并发会话频繁访问同一数据块（热块）引发。以下从产生机制、典型场景、根因分类、排查流程到优化方案进行系统解析：

---

⚙️ 一、等待事件产生机制

当会话需访问SGA中的缓冲区时，需经历以下步骤：

1. 获取Cache Buffers Chains Latch：遍历哈希链定位目标缓冲区头（Buffer Header）。
2. Pin缓冲区头：以共享（读）或独占（写）模式Pin住缓冲区头。
3. 操作缓冲区：执行数据读取或修改。
4. 释放资源：操作完成后Unpin缓冲区头并释放Latch。

触发等待的条件：
若目标缓冲区已被其他会话以不兼容模式Pin住（如：写操作阻塞读操作），当前会话将挂起并记录buffer busy waits等待事件。

---

🔥 二、典型场景与根因分类

根据参数class#（P3）和原因码，主要分为五类：

📊 表1：buffer busy waits根因分类与特征

原因类型	原因码 (class#)	触发场景	关联事件
数据块未缓存	130 (类#1)	多会话并发请求磁盘中同一数据块，仅一个会话执行I/O，其余等待	db file sequential read
数据块DML冲突	220 (类#1)	多会话并发修改同一块内不同行（常见于大块或高并发DML）	enq: TX - row lock
数据段头争用	4	频繁修改段头（如扩展HWM、管理FREELIST）	space management
UNDO段头争用	17	回滚段过少或区尺寸小，导致段头频繁更新	enq: US - contention
UNDO块争用	18	一致性读需访问UNDO块时被阻塞（查询与DML时间重叠）	read consistency

⚠️ 高频场景：

1. 热块（Hot Block）：
   • 小表频繁全扫（如配置表）或索引叶块高并发访问。
   • 影响：块内行密度高（如8KB块存100行），多会话争用同一块。
2. 批量DML操作：
   • 大量INSERT/UPDATE导致FREELIST管理压力或HWM移动。
3. UNDO资源不足：
   • 自动UNDO管理下UNDO_RETENTION过小或事务量激增。

---

🔍 三、详细排查流程

步骤1：确认等待事件影响范围

-- 查看AWR报告Top等待事件
SELECT event, total_waits, time_waited_ms 
FROM dba_hist_system_event 
WHERE event = 'buffer busy waits';

若该事件占比超过总DB Time的5%，需深入分析。

步骤2：定位热点对象

-- 实时会话定位
SELECT sid, event, p1 file#, p2 block#, p3 class# 
FROM v$session_wait 
WHERE event = 'buffer busy waits';

-- 关联对象信息
SELECT owner, segment_name, segment_type 
FROM dba_extents 
WHERE file_id = &file_id 
  AND &block_id BETWEEN block_id AND block_id + blocks - 1;

关键：通过file#和block#定位具体表/索引/段。

步骤3：分析原因码与块类型

-- 根据class#判断类型
SELECT 
  CASE &class# 
    WHEN 4  THEN 'Data Segment Header'
    WHEN 17 THEN 'Undo Segment Header'
    WHEN 18 THEN 'Undo Block'
    ELSE 'Data Block' 
  END AS block_type 
FROM dual;

步骤4：关联SQL与操作

-- 查找引发等待的SQL
SELECT sql_text 
FROM v$sql 
WHERE sql_id IN (
  SELECT sql_id 
  FROM v$session 
  WHERE sid IN (
    SELECT sid 
    FROM v$session_wait 
    WHERE event = 'buffer busy waits'
  )
);

重点检查：全表扫描、高频DML语句。

---

🛠️ 四、优化方案（根因导向）

📊 表2：优化方案与适用场景

根因类型	优化方案	操作示例
数据块未缓存 (130)	优化SQL减少物理I/O；增大Buffer Cache	ALTER SYSTEM SET db_cache_size=8G;
数据块DML冲突 (220)	降低块内行密度：增大PCTFREE、迁移至小尺寸块表空间	ALTER TABLE t MOVE TABLESPACE small_blocks;
数据段头争用 (4)	增加FREELIST组数（仅限MSSM表）	ALTER TABLE t STORAGE (FREELISTS 10 FREELIST GROUPS 4);
UNDO段头争用 (17)	增加UNDO表空间大小或UNDO段数量	ALTER SYSTEM SET UNDO_RETENTION=1800;
热块问题	使用反向键索引或哈希分区分散数据	CREATE INDEX idx ON t(emp_id) REVERSE;

⚙️ 进阶优化技巧：

1. 减少热块冲突：
   • 反转键索引：打散索引叶块连续性（如序列主键）。
   • 哈希分区：将数据分散至多个物理块（如按employee_id分区）。
2. 调整存储参数：
   • 对大并发DML表设置PCTFREE=40（预留更新空间），PCTUSED=40（降低块填充率）。
3. UNDO优化：
   • 自动UNDO管理下增加UNDO_TABLESPACE大小，避免UNDO_RETENTION不足。

---

💎 五、总结

buffer busy waits的本质是缓冲区访问串行化，核心解决路径为：

1. 定位热点对象：通过v$session_wait锁定文件号与块号。
2. 根因诊断：依据class#区分为I/O型（130）或并发型（220/4/17/18）。
3. 针对性优化：
   • I/O型 → 减少物理读（SQL优化+缓存扩容）
   • 并发型 → 分散数据分布（分区/反转索引）或调整存储结构（FREELIST/PCT参数）。

监控建议：定期检查v$segment_statistics中"buffer busy waits"统计，识别热点段前置优化。对RAC环境需同步关注gc buffer busy事件（全局缓存争用）。

欢迎关注我的公众号《IT小Chen》