RE: buffer busy waits explanation

Orlando,  

If your question "Why does it matter if the block is being read by another session or current session?" relates to "so the waiting session must wait for the block read to complete", then the latter actually implies that the waiting session must wait for the block read (from disk to SGA) to complete and not for the data read (once in SGA) by the session that brought it in.  

Whether it is data (rows) or house keeping structures there will have to be serialization to ensure that the content move from one consistent state to another. This is true of any system with concurrent access. I would interpret locking, as in row locks, as a more prolonged process where you lock a row, for update, (and go for a coffee break) as opposed to locking for serialization of concurrent, and perhaps incompatible, changes, which are short in duration, that the database does to ensure structural integrity.  

That is my take - I sure there are others who have a more intimate view of how this works  

-Krish  

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