buffer busy waits explanation

From: Orlando L <oralrnr_at_gmail.com>
Date: Thu, 24 Apr 2008 16:42:36 -0500
Message-ID: <4e0c9d990804241442v36c8b4f5i777f2a5aeea4b0a5@mail.gmail.com>


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?


Received on Thu Apr 24 2008 - 16:42:36 CDT

Original text of this message