RE: buffer busy waits explanation

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

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Thursday, April 24, 2008 7:05 PM
To: mwf_at_rsiz.com; oralrnr_at_gmail.com; oracle-l_at_freelists.org 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  

--
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


 <mailto:mark.bobak_at_il.proquest.com> mark.bobak_at_proquest.com

 <http://www.proquest.com> www.proquest.com

 <http://www.csa.com> www.csa.com


ProQuest...Start here. 

 

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Thursday, April 24, 2008 6:54 PM
To: oralrnr_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: buffer busy waits explanation

 

in line

 

  _____  

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. 

<<< 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.


 




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