mysql busy buffer_buffer busy wait 解析

虽然buffer busy waits事件的发生可能至少有十个不同的原因，但是代码130和220是最常见的原因。基本上，小于200的代码号意味着这种等待是和I/O有关的。具体代码查询见后面注释

1，带有原因码130的数据块(类#1)争用

1)等待集中在数据块上，并且原因码是130，则意味着多个会话并发请求相同的数据块，但该数据块并不在缓冲存储器中，并且必须从磁盘读取。

2)当多个会话请求不在缓冲存储器中的相同数据块时，ORACLE可以聪明地防止每个会话进行相同的操作系统I/O调用。否则，这可能严重地增加系统I/O的数量，所以，ORACLE只允许一个会话执行实际的I/O，而其他的会话在buffer busy waits上等待块，执行I/O的会话在db file sequential read或db file scattered read等待事件上等待。

3)可在v$session视图中检查SESSION的注册时间，并且等待事件db file sequential(scattered) read和buffer busy waits等待相同的文件号和块号。

解决方法：优化SQL语句，尽可能地减少逻辑读和物理读；

2，带有原因码220的数据块(类#1)争用

1)等待集中在数据块上，并且原因码是220，则意味着多个会话同时在相同的对象上执行DML(相同块中的不同行)。

2)如果数据块的尺寸较大(>=16K)，则可能强化这种现象，因为较大的块一般在每个块中包含更多的行。

3)减少这种情况的等待的方法：减少并发；减少块中行的数量；在另一个具有较小块尺寸的表空间中重新构建对象。

具体方法说明：

使用较大的PCTFREE和较小的PCTUSED重新构建表或索引；

使用alter table minimize records_pre_block命令改变表以最小化每个块的最小行数

从ORACLE9i开始，可以在另一个具有较小块尺寸的表空间中移动或重新构建对象。

注：虽然这些方法可以最小化buffer busy waits问题，但它们无疑会增加全表扫描时间和磁盘空间利用率。

3，数据段头(类#4)的争用

1)如果buffer busy waits的等待事件主要集中在数据段头(即表或索引段头，并且不是UNDO段头)上，这意味着数据库中一些表或索引有高段头活动。

注：进程出于两个主要原因访问段头，一是，获得或修改FREELISTS信息；二是，为了扩展高水位标记(HWM)。

减少这种情况的等待的方法：

>> 对使用自由表进行段管理的表，增加确认对象的FREELISTS和FREELIST GROUPS(注：FREELIST GROUPS的增加也是必须的)；

>> 确保FCTFREE和PCTUSED之间的间隙不是太小，从而可以最小化FREELIST的块循环。

>> 下一区的尺寸不能太小，当区高速扩张时，建立的新区需要修改在段头中区映射表。可以考虑将对象移动到合理的、统一尺寸的本地管理的表空间中。

4，撤销段头(类#17)的争用  (undo segement header )

1)如果buffer busy waits等待事件主要集中在撤销段头，这表明数据库中的回滚段过少或者是它们的区尺寸太小，从而造成对段头的频繁更新。

解决：

segment header争用是因为系统中undo segment不够，需要增加足够的undo segment，根据undosegment的管理方法，若是手工管理模式，需要修改rollback_segments初始化参数来增加rollback

segment，若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增

多rollback segment的数量

5，撤销块的争用(类#18)(undo block)

1)如果buffer busy waits等待事件主要集中在撤销块上，这表明有多个并发会话为保证一致性读同时查询更新的数据。

解决办法是错开应用程序修改数据和大量查询数据的时间.

注释：

在诊断buffer busy waits事件的过程中，获取如下信息会很有用：

1.获取产生buffer busy waits事件的等待原因编号，这可以通过查询该事件的p3参数值获得

2.获取产生此事件的SQL语句，可以通过如下的查询获得：

select sql_text from v$sql t1,v$session t2,v$session_wait t3

where t1.address=t2.sql_address and t1.hash_value=t2.sql_hash_value

and t2.sid=t3.sid and t3.event='buffer busy waits';

3.获取等待的块的类型以及所在的segment，可以通过如下查询获得： select'Segment Header'class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b

where a.header_file=b.p1anda.header_block=b.p2andb.event='buffer busy waits'union

select'Freelist Groups'class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b

where a.header_file=b.p1andb.p2 between a.header_block+1and (a.header_block+a.freelist_groups) anda.freelist_groups>1andb.event='buffer busy waits'union

select a.segment_type||' block'class,a.segment_type,a.segment_name,a.partition_name from dba_extents a,v$session_wait b

where a.file_id=b.p1andb.p2 between a.block_idanda.block_id+a.blocks-1andb.event='buffer busy waits'andnot exists(select 1 from dba_segments where

header_file=b.p1andheader_block=b.p2);