Buffer Busy Wait小结

当进程需要存取SGA中的buffer的时候，它会依次执行如下步骤的操作：

1.获得cache buffers chains latch，遍历那条buffer chain直到找到需要的buffer header
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 header pin，是因为为了保证数据的一致性，同一时刻一个block只能被一个进程pin住进行存取，因此当一个进程需要存取buffer cache中一个被其他进程使用的block的时候，这个进程就会产生对该block的buffer busy waits事件。

截至Oracle 9i，buffer busy waits事件的p1,p2,p3三个参数分别是file#,block#和id，分别表示等待的buffer block所在的文件编号，块编号和具体的等待原因编号，到了Oracle 10g，前两个参数没变，第3个参数变成了块类型编号，这一点可以通过查询v$event_name视图来进行验证：
PHP code:--------------------------------------------------------------------------------
Oracle 9i
SQL> select parameter1,parameter2,parameter3 from v$event_name where name='buffer busy waits';
PARAMETER1 PARAMETER2 PARAMETER3
------------------------ ------------------------ ------------------------
file# block# id
Oracle 10g
PARAMETER1 PARAMETER2 PARAMETER3
------------------------ ------------------------ ------------------------
file# block# class#
--------------------------------------------------------------------------------
在诊断buffer busy waits事件的过程中，获取如下信息会很有用：
1.获取产生buffer busy waits事件的等待原因编号，这可以通过查询该事件的p3参数值获得

P3 代表reason code，该值的具体意义表示如下：

Code Reason for wait - A modification is happening on a SCUR or XCUR buffer but has not yet completed. 0 The block is being read into the buffer cache. 100 We want to NEW the block, but the block is currently being read by another session (most likely for undo). 110 We want the CURRENT block either shared or exclusive but the block is being read into cache by another session, so we have to wait until itsread() is completed. 120 We want to get the block in current mode, but someone else is currently reading it into the cache. Wait for the user to complete the read. This occurs during buffer lookup. 130 Block is being read by another session, and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block. 200 We want to NEW the block, but someone else is using the current copy, so we have to wait for that user to finish. 210 The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock, so does not show up as waiting very long. In this case, the statistic: "exchange deadlocks" is incremented, and we yield the CPU for the "buffer deadlock" wait event. 220 During buffer lookup for a CURRENT copy of a buffer, we have found the buffer but someone holds it in an incompatible mode, so we have to wait. 230 Trying to get a buffer in CR/CRX mode, but a modificationhas started on the buffer that has not yet been completed. 231 CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.

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，可以通过如下查询获得：
PHP code:--------------------------------------------------------------------------------
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);--------------------------------------------------------------------------------
查询的第一部分：如果等待的块类型是segment header，那么可以直接拿buffer busy waits事件的p1和p2参数去dba_segments视图中匹配header_file和header_block字段即可找到等待的segment名称和segment类型，进行相应调整
查询的第二部分：如果等待的块类型是freelist groups，也可以在dba_segments视图中找出对应的segment名称和segment类型，注意这里的参数p2表示的freelist groups的位置是在segment的header_block+1到header_block+freelist groups组数之间，并且freelist groups组数大于1
查询的第三部分：如果等待的块类型是普通的数据块，那么可以用p1、p2参数和dba_extents进行联合查询得到block所在的segment名称和segment类型

对于不同的等待块类型，我们采取不同的处理办法：
1.data segment header：
进程经常性的访问data segment header通常有两个原因：获取或修改process freelists信息、扩展高水位标记，针对第一种情况，进程频繁访问process freelists信息导致freelist争用，我们可以增大相应的segment对象的存储参数freelist或者freelist groups；若由于数据块频繁进出freelist而导致进程经常要修改freelist，则可以将pctfree值和pctused值设置较大的差距，从而避免数据块频繁进出freelist；对于第二种情况，由于该segment空间消耗很快，而设置的next extent过小，导致频繁扩展高水位标记，解决的办法是增大segment对象的存储参数next extent或者直接在创建表空间的时候设置extent size uniform
2.data block：
某一或某些数据块被多个进程同时读写，成为热点块，可以通过如下这些办法来解决这个问题：
(1)降低程序的并发度，如果程序中使用了parallel查询，降低parallel degree，以免多个parallel slave同时访问同样的数据对象而形成等待降低性能
(2)调整应用程序使之能读取较少的数据块就能获取所需的数据，减少buffer gets和physical reads
(3)减少同一个block中的记录数，使记录分布于更多的数据块中，这可以通过若干途径实现：可以调整segment对象的pctfree值，可以将segment重建到block size较小的表空间中，还可以用alter table minimize records_per_block语句减少每块中的记录数
(4)若热点块对象是类似自增id字段的索引，则可以将索引转换为反转索引，打散数据分布，分散热点块
3.undo segment header：
undo segment header争用是因为系统中undo segment不够，需要增加足够的undo segment，根据undo segment的管理方法，若是手工管理模式，需要修改rollback_segments初始化参数来增加rollback segment，若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量
4.undo block：
undo block争用是由于应用程序中存在对数据的读和写同时进行，读进程需要到undo segment中去获得一致性数据，解决办法是错开应用程序修改数据和大量查询数据的时间

小结：buffer busy waits事件是oracle等待事件中比较复杂的一个，其形成原因很多，需要根据p3参数对照Oracle提供的原因代码表进行相应的诊断，10g以后则需要根据等待的block类型结合引起等待时间的具体SQL进行分析，采取相应的调整措施

9. 虽然buffer busy waits事件的发生可能至少有十个不同的原因，但是代码130和220是最常见的原因。基本上，小于200的代码号意味着这种等待是和I/O有关的。
10. 带有原因码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等待相同的文件号和块号。

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

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

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

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

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

4)具体方法说明：

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

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

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

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

12. 数据段头(类#4)的争用

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

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

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

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

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

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

13. 撤销段头(类#17)的争用

1)如果buffer busy waits等待事件主要集中在撤销段头，这表明数据库中的回滚段过少或者是它们的区尺寸太小，从而造成对段头的频繁更新。如果使用ORACLE9I的由数据库系统管理UNDO段，就不需要处理这种问题，因为ORACLE会根据需要增加额外的的UNDO段。

2)可以创建并启用私有回滚段，以减少每个回滚段的事务数量。需要修改init.ora文件中的ROLLBACK_SEGMENTS参数。

3)如果使用公用回滚段可以减少初始化参数transactions_per_rollback_segment的值，ORACLE通过transactions/transactions_per_rollback_segment来获取公有回滚段的最小数量。

14. 撤销块的争用(类#18)\

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

2)这是应用程序存在问题，当应用程序在不同时间内运行查询和DML时，这种问题不会存在。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23757700/viewspace-711631/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23757700/viewspace-711631/