| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Buffer Busy Waits -- Sanity check please
Thanks for the explanation. I usually try to avoid delving that deep into the internals, but I guess it's necessary on occasion just to understand what's going on.
Jared
Riyaj_Shamsude
en_at_i2.com To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.c Subject: Re: Buffer Busy Waits -- Sanity
check please
om
12/03/01 07:00
AM
Please respond
to ORACLE-L
Jared
Say, process A is interested in reading a block, then it hashes the
data block address of the block to find the hash bucket in the buffer
cache. If that specific block is in the buffer cache, then it must be
attached with that hash bucket. Holding the hash bucket latch, the process
A will look for the buffer in that hash chain with that data block address
. If the buffer is found in the buffer cache, then that process has to
examine the state of the buffer before proceeding further.
If another process B is operating on the buffer, i.e. reading a database block from the disk in to the buffer (for FTS or otherwise), then the process B will pin the buffer and the buffer is not available until the read is completed. So, the process A will wait for the buffer to be unpinned, posting 'buffer busy event'. Since this event can happen in various points in the buffer lifecycles, p3 indicates details about the wait itself.
Point being that, two processes can not operate on the same buffer simultaneously. Even though readers do not block readers in terms of locks, they could be blocked due to buffer unavailability, but this event is usually very brief.
As malcolm suggested, probably, the processes are chasing one another.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
Jared Still
<jkstill_at_cybcon.com> To: Multiple recipients of list
Sent by: ORACLE-L <ORACLE-L_at_fatcity.com>
root_at_fatcity.com cc:
Subject: Re: Buffer Busy Waits
-- Sanity check please
12/02/01 10:15 PM
Please respond to
ORACLE-L
Interesting. Any idea of what the point is in preventing other processes from reading a block in the buffer?
Jared
On Saturday 01 December 2001 20:10, MacGregor, Ian A. wrote:
> The P3 value of 130 on the buffer busy waits does indicate that the block
> is being read by another process as Malcolm stated that's the process
doing
> the scattered read (Full table scan). Oracle needs to protect the block
> while it is being read. The others sessions are waiting until the read
of
> that block is complete.
>
> For a definition of the P3 values see Steve Adam's website
> http://www.ixora.com.au/
>
> His full explanation of P3 id 130 is
>
>
> 1013 Block is being read by another session and no other
> or 130 suitable block image was found, so we wait until the read
> is completed. This may also occur after a buffer cache
> assumed deadlock. The kernel can't get a buffer in a
> certain amount of time and assumes a deadlock. Therefore it
> will read the CR version of the block.
>
>
> Ian MacGregor
>
> -----Original Message-----
> Sent: Saturday, December 01, 2001 6:20 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Malcolm,
>
> The paragraph below would indicate that readers are blocking.
>
> Readers don't block in Oracle. The only reason I can think of at
> the moment for a SELECT to cause buffer busy waits is delayed
> block cleanout, of which there has been a lot of discussion lately.
>
> I could be all mixed up here I guess, it's Saturday and I dont' want
> to think too hard about all this. Don't have time to break out the FM
> so I'll just sit back and wait for you to agree or refute. ;)
>
> Jared
>
> On Tuesday 27 November 2001 00:25, Thorns, Malcolm (NESL-IT) wrote:
> > Jeff,
> >
> > The 3 sessions are doing the same (or similar) queries. In this case
> > count(*) which is forcing a full table scan of the table in each
session.
> > The 3 sessions are thus trying to access the same blocks from the SGA,
> > in the same order. Only 1 session can access a block in the SGA at a
> > time - this is the session showing 'db file scattered read'. The other
2
> > sessions need to wait for the block (these waits show as 'buffer busy
> > waits' - ie waiting for the block in the SGA). You will see the block
id
> > (and perhaps the file id) changing as the FTS's progress. Thus the
> > sessions are 'chasing' each other through the blocks - holding each
other
> > up with SGA block contention - which shows up as 'buffer busy waits'.
> > Hope that explains things.
> >
> > Regards,
> >
> > Malcolm
> >
> > -----Original Message-----
> > Sent: Monday, November 26, 2001 11:21 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > We recently had a new website go live. Since then, I'm seeing
constant
> > buffer busy waits
> > and after a period of time, I see sessions hung on the same block#.
> > The SQL query
> > is always a COUNT(*) (below). It's almost as though one session has a
> > lock
> >
> > of some sort in the buffer cache and other sessions are blocked.
> > Although, I've checked and
> > there's no DML ongoing, so I'm unsure as to why we would see this.
Note
> > that v$session shows
> > 78 and 393 to be INACTIVE, while 159 is ACTIVE. So it's like 159
can't
> > write to
> > the buffer cache because 78 and 393 have a lock there. Note that
these
> > are all defined
> > as persistent connections, via the Vignette front-end. I'm sure all
the
> > clues are there
> > but my brain is too fuzzed to piece it together.
> >
> > SID SQL_TEXT
O/S
> > User
> > ----- ----------------------------------------------------------------
> > ---------------
> > 159 SELECT COUNT(*) NUM,SUM(TOTAL_CHARGE_AMT) TOT FROM BBN.BBN_SRV
> > vignette
> > 159 _PAID_WARR_CLAIM WHERE CUSTOMER_ID = :b1 AND ENTERPRISE_CD = :
> > vignette
> > 159 b2 AND (CHECK_ID IS NOT NULL AND CHECK_ID != 'PENDING' )
> > vignette
> >
> > SID EVENT P1TEXT P1 P2TEXT P2
> > P3TEXT P3
> > ----- -------------------------- ------------ -- ----------- ------
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: jkstill_at_cybcon.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Dec 03 2001 - 13:46:23 CST
 |
 |