RE: buffer busy waits explanation

From: Mark W. Farnham <>
Date: Thu, 24 Apr 2008 20:32:12 -0400
Message-ID: <1d5501c8a66b$cfcdc660$>

Right you are. That's a quasi-locked block (in place of row) but NOT a buffer busy wait.  

From: [] On Behalf Of Bobak, Mark
Sent: Thursday, April 24, 2008 7:05 PM
To:;; Subject: RE: buffer busy waits explanation  

Actually, Mark, one minor correction, if you don't mind. In the case of ITL shortage, the wait is on a TX enqueue in mode 4, not a buffer busy wait.  


Mark J. Bobak
Senior Database Administrator, System & Product Technologies
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059

ProQuest...Start here. From: [] On Behalf Of Mark W. Farnham Sent: Thursday, April 24, 2008 6:54 PM To:; Subject: RE: buffer busy waits explanation in line _____ From: [] On Behalf Of Orlando L Sent: Thursday, April 24, 2008 5:43 PM To: Subject: buffer busy waits explanation Hi I am trying to understand what is buffer busy waits. I was reading this: " * The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete. * Another session has the buffer block locked in a mode that is incompatible with the waiting session's request. " In the first case, shall I asume that the block has two different rows that are needed by two different sessions. The first session is bringing the block into the buffer from disk and the second session is also waiting for that block. Second session can then read the block for the second row. Why does it matter if the block is being read by another session or current session? Some session has to bring up the block into memory if a current session needs the block. <<< they don't have to be different rows. This is simply that the second session waits if the first has already started the read, because it likely would be a waste of resources and not any faster for the second session to start a fresh read. It is possible the second session will need a different read consistent point, in which case it may need to create a block clone. But Oracle's guess is that on average you'll be better off to wait for the other session to finish its read than to start your own read when another one is already in progress >>> In the second case, it says the buffer block is locked in a mode incompatible with waiting sessions request.In this case we assume the block is already in SGA. I think they are talking about database buffers in SGA. I thought Oracle locked only at the row level and not at the block level. Does Oracle lock in block level? if so, for which blocks? data and index blocks? <<< locking in Oracle is at the row level, but if you have a block with fewer interested transaction slots than simulateous readers and too full to create another entry in the interested transactions list, then you might have to wait for the block even though no one else wants your specific row. Also, if Oracle is in the middle of splitting a block in an index you might have to wait for that to complete. Notice that you're waiting, and only if your session and another session need and wait for blocks in reverse order will you generate a deadlock. The reason they simply quote "locked in a mode incompatible" is because the enumeration of possibilities is a bit long and complicated - though it might be worthwhile to spit out the actual wait's precise reason, without looking at the code that is hard to guess how big the overhead would be. >>> Orlando. --
Received on Thu Apr 24 2008 - 19:32:12 CDT

Original text of this message