也先看看buffer busy waits在官方文档中的描述

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segment header, undo header, and undo block.

简单来说，由于大量进程并发访问少数buffer block而出现热块问题，其他需要访问热块的进程就需要等待阻塞源进程忙完。buffer busy waits也发生在buffer cache中，最常见于多个进程想要修改同一个buffer block。内存中，同一时刻一个buffer block只允许被一个进程修改，其余进程需要等待，等待的事件即为buffer busy waits。

 

一、 图解buffer busy waits原因（bbw）

下面这个图在分析cbc等待时也出现过，bbw发生在获取cbc latch之后。

• 会话读取或修改buffer cache之前，必须要先获取cbc latch
• 获取后沿着对应hash bucket所在hash chain list访问上面的buffer header，直到发现它需要的bh
• 发现后以共享或独占方式获取缓冲区（bh或buffer block）的PIN，任何时侯只有一个进程可以PIN住一个缓冲区。如果无法获得PIN，会话就会等待buffer busy waits，bbw等待表明存在读/读、读/写、写/写争用。
• 一旦缓冲区被PIN住，会话即释放cbc latch（由于需要获得PIN才释放cbc latch，bbw等待通常会伴随着cbc等待）

 

二、 发生在不同位置的bbw

BBW可能发生在以下位置：

1. data block

• INDEX：通常为高并发插入导致的索引热块，常用解决方法：
  • 使用hash分区索引
  • 使用reverse key索引

BBW on index (because of insert)

If users are inserting data that has a rising key value, especially a monotonically rising value, then all the new inserts will have to update the leading edge leaf block of the index and with high concurrent inserts this can cause buffer busy waits.

Solutions

Hash partition the index 

Reverse Key Index

 

• TABLE：通常也是高并发插入导致的热块，常用解决方法：
  • 将争用对象放在 ASSM 表空间
  • 使用 free lists

BBW on insert

If multiple concurrent users are inserting into a table that doesn't have free lists or is not in an ASSM tablespace then all users will end up inserting into the same block, the first one on the free list and this block will become the hot block.

by adding free lists or moving the table to an ASSM tablespace we will alleviate the bottleneck.

Multiple free lists:

查询表空间是否为ASSM

select
        tablespace_name,
        extent_management  LOCAL,
        allocation_type    EXTENTS,
        segment_space_management ASSM,
        initial_extent
from dba_tablespaces;

 

2. Segment header

数据库中一些表或索引有高频访问高段头活动。进程出于两个主要原因访问段头：一是获得或修改FREELISTS信息，二是扩展高水位标记(HWM)。减少这种情况的等待的方法：

• 对使用FREELIST 进行段管理的表，增加对应对象的FREELISTS和FREELIST GROUPS
• 确保PCTFREE和PCTUSED之间的间隙不是太小，从而可以最小化FREELIST的块循环。
• 下一区的尺寸不能太小，当区高速扩张时，建立的新区需要修改在段头中区映射表。可以考虑将对象移动到合理的、统一尺寸的本地管理的表空间中，或者手动加高高水位线。

 

3. File Header Block

Most likely extent allocation problems, look at extent size on tablespace and increase the extent size to there are few extent allocations and less contention on the File Header Block.

4. free lists 

Add free list groups to the object

5. undo header

通常是数据库中的回滚段过少或者是它们的区尺寸太小，从而造成对段头的频繁更新。如果使用自动管理的UNDO段，就不需要处理这种问题，因为ORACLE会根据需要增加额外的UNDO段。

• 建议使用自动管理的UNDO段
• 可以创建并启用私有回滚段，以减少每个回滚段的事务数量。需要修改init.ora文件中的ROLLBACK_SEGMENTS参数。
• 如果使用公用回滚段可以减少初始化参数transactions_per_rollback_segment的值，ORACLE通过transactions/transactions_per_rollback_segment来获取公有回滚段的最小数量。

6. undo block

有多个并发会话为保证一致性读同时查询更新的数据。这是应用程序存在问题，当应用程序在不同时间内运行查询和DML时，这种问题不会存在。

 

三、 BBW分析步骤

• 该等待事件主要的原因码是什么？即前步提到的发生位置 (参数P3)

等待事件参数说明

事件号	事件名	P1	P2	P3
145	buffer busy waits	file#	block#	9i - 原因码 10g后 - block class#

查询等待发生位置

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;

• buffer busy waits事件等待的块类型 (参数P1)
• 缓冲区所属的对象是什么？(由P1和P2参数配合dba_extents找出)

select s.segment_name, s.partition_name 
  from dba_extents s 
where <P2的值> between s.block_id and (s.block_id + s.blocks -1) and s.file_id = <P1的值>

• 和buffer busy waits事件相关的SQL语句是什么，能否优化

 

参考

https://www.cndba.cn/redhat/article/2478

https://blog.csdn.net/cymm_liu/article/details/7968537

http://www.itpub.net/thread-174551-1-1.html

https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-buffer-busy-wait