转载

buffer busy wait 解析

一：首先解释buffer busy wait等待事件出现的原因：

进程获取SGA中的buffer会遵循以下步骤：

1.获得cache buffers chains latch，遍历那条buffer chain直到找到需要的buffer header。 #块在缓存中的排列成的链就是 cache buffers chains

2.根据需要进行的操作类型(读或写)，它需要在buffer header上获得一个共享或独占模式的buffer pin或者buffer lock。

3.若进程获得buffer header pin，它会释放获得的cache buffers chains latch，然后执行对buffer block的操作。

4.若进程无法获得buffer header pin，就会产生buffer busy waits事件等待。

那么什么情况下会产生buffer busy waits呢？

1，块可能被其它的session读到缓冲区，所以session必须等待块的读入结束。
2，session可能有与等待的session查询不协调的缓冲块。,

3，出现了hot block，所谓hot block指的是同时有若干个session，同时对同一个block进行操作，导致此block比较忙；

导致block比较忙的原因是：oracle数据库的数据存放的最小单位就是block，数据每次读取数据的时候只能以block为单位；
如果有多条记录存放在一个block上的话，那么导致多个session访问同一个block的可能性就越大；

题外话：【 block的freelist和pctused两个参数：

我们知道Oracle数据库的读取单位是数据块（Block），而一个Block是否允许被写入数据是基于一定的空闲度，这就是大家知道的pctfree和pctused存储参数设置。

假设pctfree=10, pctused=40，这就表明当一个Block的空间使用率达到了90%（100-pctfree）时，这个block就不再允许被用于新增数据（insert），而保留下来的这10%的空间则被预留为行更新（update）所可能需要的空间扩展，我们说此时这个block就从freelist上被摘走了（实际上还有另外一种情况，就是当块剩余空间不足以插入一条记录并且该块的使用率已经超过了pctused定义的值并且该块位于freelist header处时，该块也会从freelist上被摘走，术语称为UNLINK）。当有数据删除（delete）的时候，只有该block中的数据被删除到一定的程度，该块才会重新被加入到freelists中，而这个程度就是pctused参数定义的数值，如我们这个例子中，只有块中的数据降低到40%以下的时候，该块才被重新允许用于新增数据。

通过上面的描述，可以知道所谓freelists，就是一个指定了所有可以用于insert操作的数据块的列表。存在在这个列表中的数据块才能用于insert操作，一旦一个数据块无法用于insert（达到了pctfree参数指定的限度）则立刻从这个列表中被摘除。freelists的作用就在于管理高水位标志（HWM）以下的空闲空间。】

由上面括号内容可知增加freelist，减小pctused；这样的目的是将一个block上可以使用的空间减少，这样的话：一个block上的数据存放的较少，可以提高应用的访问并发率，减少了一个块上存多条数据的可能，减少hot block的产生；

虽然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 <table_name> 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，根据undo segment的管理方法，若是手工管理模式，需要修改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.p1 and a.header_block=b.p2 and b.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.p1 and b.p2 between a.header_block+1 and (a.header_block+a.freelist_groups) and a.freelist_groups>1 and b.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.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1 and b.event='buffer busy waits' and not exists(select 1 from dba_segments where   header_file=b.p1 and header_block= b.p2);