buffer busy wait 的深度剖析

buffer busy wait 与 free buffer wait 的区别在于：前者指的是多个session对于同一个block的读取竞争；后者指的是，在buffer cache中，LRU列表中已经没有了空闲buffer space来接纳新的block信息。一般而言，发生buffer busy wait是由于：
1. The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete. 等待另外一个session读取动作的完成
2. Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
另外一个session已经对buffer block上锁 buffer busy wait 有3个parameter：p1 代表文件号，p2代表block号，P3代表reason code 根据p1 和p2 ，我们执行以下查询：
select owner, segment_name, segment_type
from dba_extents
where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
再根据v$segment_statistics来确定相关segment 的统计信息 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 its read() 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 modification has 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.

解决办法：
1、 发现hot block，改变pctused 和pctfree，使得一个block 中可以容纳更多得数据
2、 增加freelist group 和 freelist
3、 增加一定数量的回滚段

ref: http://carson.yculblog.com/post.1249737.html

[@more@]

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

转载于:http://blog.itpub.net/7916042/viewspace-915055/