RE: buffer busy waits explanation

Hi Orlando,

The first case you cite is easier to explain, so, I'll start w/ that. Suppose you have session #1, and it needs block X. It checks the buffer cache and finds that block X is not currently in the cache. So, it identifies a free buffer (yes, I'm oversimplifying a bit), and takes an exclusive lock on the buffer, which it will maintain till the data has been read from disk and populated in the buffer. Now, suppose session #2 decides it also needs block X. It will look for it in the buffer cache, and find it, but, it will see that session #1 has an exclusive lock on the buffer, for the purpose of reading data from disk and loading into the buffer. Session #2 must wait till session #1 completes the population of the buffer, or else it may attempt to read a block that's only ½ way populated. As soon as session #1 finishes reading the data from disk and loading into the buffer, it releases the lock and then session #2 can proceed. So, for the time that the block is being read, session #1 will be waiting on a 'db file sequential read' or 'db file scattered read'. For the time the buffer is locked by session #1 (i.e. busy), session #2 will wait on a 'buffer busy wait' with the P3 parameter set to 130. Starting w/ 10g, the wait event would be 'buffer read by other session'.

The above scenario is, in my experience, the one you're most likely to encounter. Other scenarios are described in MetaLink Doc ID 34405.1.

Finally, the thing to remember, is that these "buffer locks" are NOT enqueues, and are not the types of locks that are taken when Oracle does row-level locking. These buffer locks are serialization mechanisms, much like latches, that ensure buffers are not corrupted, or read in error.

Row level locks, aka enqueues, are taken for much longer duration, i.e. transaction duration, whereas buffer locks are taken only for the very short time required for the buffer cache to maintain internal consistency.

Hope that helps,

-Mark

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak_at_proquest.com<mailto:mark.bobak_at_il.proquest.com>
www.proquest.com<http://www.proquest.com>
www.csa.com<http://www.csa.com>

ProQuest...Start here.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Orlando L
Sent: Thursday, April 24, 2008 5:43 PM
To: oracle-l_at_freelists.org
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.

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?

Orlando.



--
http://www.freelists.org/webpage/oracle-l