缓冲区忙等待（Buffer Busy Waits）常见于 Oracle10g、11g，主要原因是向表或索引执行插入操作时产生争用。还有少数其他场景会发生争用：老式回滚段（RBS）、文件头块、空闲链表（freelists）。

    在 Oracle10g 之前，还有一大诱因： 读进程等待另一个读进程。即一个用户把数据块物理 IO 读入内存，第二个用户也要读取该块，第二个用户需要等待第一个用户的 IO 完成。从 Oracle 10g 开始，该等待事件被命名为 read by other session（被其他会话读取）；在 10g 之前，该现象归类为 buffer busy waits。

    分析该瓶颈、定位解决方案最简便的方式： 使用 ASH（Active Session History，活动会话历史），该功能在 10g 可用，需要诊断包授权许可；也可以免费使用模拟 ASH（S‑ASH），或者使用 DB Optimizer 这类第三方工具。

ASH 中记录的 数据块类别（Data block class），是分析缓冲区忙等待最重要信息。拿到块类别，就可以判断瓶颈类型：

如果 CLASS（块类别）取值：

1. data block（数据块）   

   若 OTYPE（对象类型）为：   - INDEX（索引）：大概率是索引叶子插入热块。    

   解决方案：     

   ・索引做哈希分区    

   ・使用反向键索引   

   - TABLE（表）：表插入热块    

   解决方案：     

   ・配置空闲链表（free lists）     

   ・将对象迁移到 ASSM（自动段空间管理）表空间

2. Segment header（段头块）   

   同一个对象上同时出现 block，且对象类型 OTYPE=TABLE，说明该表需要使用 free lists 或者 ASSM 表空间。

3. File Header Block（文件头块）   

   大概率是区（extent）频繁分配导致争用。检查表空间的区大小，调大区尺寸，减少区分配次数，降低文件头块的争用。

4. free lists（空闲链表）   给对象增加空闲链表组（free list groups）。
5. undo header（撤销段头）   撤销段数量不足；如果使用老式 RBS 回滚段，切换到 AUM 自动撤销管理。
6. undo block（撤销块）   撤销段出现热点，属于应用侧问题。

如何查询块类别？

执行下面 ASH 查询语句：

select
       o.object_name obj,
       o.object_type otype,
       ash.SQL_ID,
       w.class
from v$active_session_history ash,
     ( select rownum class#, class from v$waitstat ) w,
      all_objects o
where event='buffer busy waits'
   and w.class#(+)=ash.p3
   and o.object_id (+)= ash.CURRENT_OBJ#
Order by sample_time;

示例输出： 表格

当 block，还需要更多诊断信息：对象类型、对象名、对象所在表空间。

下面 SQL 获取完整信息：

set linesize 120col block_type for a20
col objn for a25
col otype for a15
col filen for 9999col blockn for 9999999col obj for a20
col tbs for a10select
       bbw.cnt,
       bbw.obj,
       bbw.otype,
       bbw.sql_id,
       bbw.block_type,
       nvl(tbs.name,to_char(bbw.p1)) TBS,
       tbs_defs.assm ASSMfrom (
    select
       count(*) cnt,
       nvl(object_name,CURRENT_OBJ#) obj,
       o.object_type otype,
       ash.SQL_ID sql_id,
       nvl(w.class,'usn '||to_char(ceil((ash.p3-18)/2))||' '||
                    decode(mod(ash.p3,2),
                         1,'header',
                         0,'block')) block_type,
       ash.p1 p1    from v$active_session_history ash,
        ( select rownum class#, class from v$waitstat ) w,
        all_objects o    where event='buffer busy waits'
      and w.class#(+)=ash.p3
      and o.object_id (+)= ash.CURRENT_OBJ#
      and ash.session_state='WAITING'
      and ash.sample_time > sysdate - &minutes/(60*24)
   group by o.object_name, ash.current_obj#, o.object_type,
         ash.sql_id, w.class, ash.p3, ash.p1  ) bbw,
    (select   file_id, 
       tablespace_name name  from dba_data_files   ) tbs,
    (select
 tablespace_name    NAME,
        extent_management  LOCAL,
        allocation_type    EXTENTS,
        segment_space_management ASSM,
        initial_extent     from dba_tablespaces 
   ) tbs_defs  where tbs.file_id(+) = bbw.p1    and tbs.name=tbs_defs.nameOrder by bbw.cnt/

样例输出： 表格

Oracle7、8、9 版本

10g 之前，缓冲区忙等待也会发生：一个 IO 阻塞另一个相同 IO 请求。Oracle9i 中缓冲区忙等待主要诱因：

1. IO 读争用（仅 9i 及更早版本）
2. 表、索引插入块争用
3. 回滚段争用

8、9 版本中，参数 p3 含义发生变化，不再代表块类型，而是代表等待类别，两类关键值：

• 100 区间：读等待，本质就是 IO 等待。读进程阻塞读进程：会话 A 把块读入内存，会话 B 也要读该块，发生 buffer busy waits，p3=130。
• 200 区间：写争用（和 10g 场景一致）。写进程阻塞其他写进程，例如表缺少 free lists，大量会话同时插入同一个索引块。

如果部署 S‑ASH 模拟 ASH 或者 DB Optimizer，可以执行：

select
       count(*) cnt,
       o.object_name obj,
       o.object_type otype,
       ash.CURRENT_OBJ#,
       ash.SQL_ID,
       decode(substr(ash.p3,1,1),1,'read',2,'write',p3) p3from v$active_session_history ash,
      all_objects owhere event='buffer busy waits'
   and o.object_id (+)= ash.CURRENT_OBJ#
group by o.object_name, o.object_type, ash.sql_id, ash.p3,ash.CURRENT_OBJ# order by cnt/

输出示例： 表格

经常出现 current_obj# = -1，无法直接定位对象。此时备选方案：

col block_type for a18
col objn for a25
col otype for a15
col event for a15
col blockn for 999999col segment_name for a20
col partition_name for a15
col owner for a15set timing on/*
drop table myextents;
create table myextents as select * from dba_extents;
*/select
       count(*),
       ext.owner,
       ext.segment_name,
       ext.partition_name,
      ext.segment_type,
        decode(substr(ash.p3,1,1),1,'read',2,'write',p3) p3from v$active_session_history ash,
     myextents extwhere
       event = 'buffer busy waits'
   and ( current_obj# = -1 or current_obj#=0  or current_obj# is null )
   and  ext.file_id(+)=ash.p1 and
        ash.p2 between  ext.block_id and ext.block_id + ext.blocksgroup by
       ext.owner,
       ext.segment_name,
       ext.partition_name,
       ext.segment_type,
       p3Order by count(*)/

说明：直接查询 DBA_EXTENTS性能很差，所以创建 myextents副本表提升查询速度。该操作需要确认业务可接受，大库拷贝会消耗时间。

输出样例： 表格

没有 ASH 数据怎么办？

缺少 ASH，只能依靠其他视图做推断：

1. V$waitstats：查看发生争用的块类别。

2. X$KCBFWAIT：定位发生缓冲区忙等待的数据文件。
3. V$segstat（9i 及以上）：列出遭受 buffer busy waits 的对象。

Oracle7、8 版本，没有 ASH 采集工具，定位对象会非常困难。

Buffer Busy Waits 根本成因

简言之： 多个会话尝试同时修改同一个内存块。 多个会话可以同时修改同一行（未提交），但这和修改内存块本身不是一回事。内存中的数据块修改，同一时刻只允许一个进程操作，防止内存损坏。不同块可以并发修改； 同一个内存块只能单进程修改。

要理解该等待，需要了解 Oracle 内存管理、块访问机制。

机器内存包含：

1. SGA（系统全局区）：所有 Oracle 会话共享内存

2. LGWR：日志写进程

3. DBWR：数据库写进程

4. User1,2,3 用户影子进程

磁盘文件系统：

1. 重做日志文件

2. 数据文件

SGA 主要组件：

1. Log Buffer 日志缓冲区

2. Library Cache 库缓存

3. Buffer Cache 缓冲区缓存

Buffer Cache 工作原理： 

进程访问块， 首先获取 cache buffer chains latch（缓存缓冲链闩锁），用来保护缓冲区链表；找到目标缓冲区头之后释放 latch。缓冲区头指向内存中的真实数据块。 修改内存块之前，会话需要锁定缓冲区头。不管是将块读入内存，还是修改已经在内存的块，都要持有该锁。锁持有时间通常很短；高并发访问场景下缓冲区头就会形成瓶颈。

读场景的 Buffer Busy Waits：read by other session

Oracle7/8/9：会话 A 把块读入内存，会话 B 也需要该块，B 不会发起新 IO，等待 A 完成 IO。 Oracle10g：该等待独立命名为 read by other session，不再归类 buffer busy waits。

Insert 插入场景 Buffer Busy Waits

多张会话并发插入，表没有 free lists、不在 ASSM 表空间，所有插入会落到同一个空闲块，形成热块。 解决：配置 free lists，或者迁移到 ASSM 自动段空间管理表空间。ASSM（Automatic Segment Space Management）在表空间级别开启。空闲块信息存放在一级 BMB 位图块；根据进程 ID 做哈希算法挑选 BMB 块，把插入负载分散到多个数据块。 ASSM 会额外消耗少量空间：每 16 个数据块占用 1 个 BMB 管理块；访问块需要逐级查找 3 级位图。综合收益高于 free lists 管理成本。查询哪些表空间开启 ASSM：

select
        tablespace_name,
        extent_management  LOCAL,
        allocation_type    EXTENTS,
        segment_space_management ASSM,
        initial_extentfrom dba_tablespaces;

创建 ASSM 表空间示例：

create tablespace data2 
datafile '/d3/kyle/data2_01.dbf' size 200M
segment space management auto;

索引插入引发 Buffer Busy Waits

插入持续递增键值（自增主键），所有新记录更新索引最右侧叶子块，高并发插入产生热块争用。 解决方案：

1. 索引哈希分区

2. 反向键索引

老式 RBS 回滚段引发 Buffer Busy Waits

块类别编号 > 18，代表老式 RBS 回滚段。

Select  CURRENT_OBJ#||' '||o.object_name objn,
          o.object_type otype,
          CURRENT_FILE# filen,
          CURRENT_BLOCK# blockn,
          ash.SQL_ID,
          w.class ||' '||to_char(ash.p3) block_typefrom v$active_session_history ash,
      (select rownum class#, class from v$waitstat ) w,
       all_objects owhere event='buffer busy waits'
    and w.class#(+)=ash.p3
    and o.object_id (+)= ash.CURRENT_OBJ#Order by sample_time;

块类别 > 18 时，经常返回不到对象名，通过文件号、块号查询 dba_extents 定位回滚段：

select  segment_name,  
            segment_typefrom     dba_extents 
where
     &P2  between 
    block_id and block_id + blocks - 1
     and 
     file_id = &P1 ;

解决方案：切换为 AUM 自动撤销管理

alter system set undo_management=auto  scope=spfile;

文件头块等待

ASH 中，buffer busy waits 场景： P1=文件号，P2=块号； current_file#、 current_block#也会记录文件、块号。 当 p1 != current_file#或者 p2 != current_block#， 优先使用 P1、P2，结果更可靠。临时表空间文件发生文件头块忙等待，一般是频繁分配 extent 区，调大临时表空间 next extent 大小。

ADDM 分析器

ADDM 报告不会直接展示新增业务负载，但可以识别： 数据库块读写争用消耗大量 DB 时间，分别输出表插入争用、索引插入争用的分析结果。