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@]

buffer busy wait 的深度剖析

相關文章