RE: buffer busy waits explanation
- From: <krish.hariharan@xxxxxxxxxxxx>
- To: <oralrnr@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Apr 2008 16:44:03 -0600
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Orlando L
Sent: Thursday, April 24, 2008 3:43 PM
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
