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
[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] 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
<[EMAIL PROTECTED]> To: Multiple recipients of list
Sent by: ORACLE-L <[EMAIL PROTECTED]>
[EMAIL PROTECTED] 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
> > ----- -------------------------- ------------ -- ----------- ------
-----
> > ------
> > 78 buffer busy waits file# 72 block# 109177 id
> > 130
> > 393 buffer busy waits file# 72 block# 109177 id
> > 130
> > 159 db file scattered read file# 72 block# 109177
> > blocks 8
> >
> >
> > --------------------------------------------
> > Jeffery D Thomas
> > DBA
> > Thomson Information Services
> > Thomson multimedia Inc.
> >
> > Email: [EMAIL PROTECTED]
> > DBA Quickplace: http://gkmqp.tce.com/tis_dba
> > <http://gkmqp.tce.com/tis_dba>
> >
> > --------------------------------------------
> >
> >
> >
*************************************************************************
> >** ***************** E mail Disclaimer
> >
> > You agree that you have read and understood this disclaimer and you
agree
> > to be bound by its terms.
> >
> > The information contained in this e-mail and any files transmitted with
> > it (if any) are confidential and intended for the addressee only. If
you
> > have received this e-mail in error please notify the originator or
> > telephone 0191 210 2060 or e-mail [EMAIL PROTECTED]
> >
> > This e-mail and any attachments have been scanned for certain
> > viruses prior to sending but neither Northern Electric plc nor any of
the
> > companies in the Northern Electric group of companies from whom this
> > e-mail originates shall be liable for any losses as a result of any
> > viruses being passed on.
> >
> > No warranty of any kind is given in respect of any information
contained
> > in this e-mail and you should be aware that that it might be
incomplete,
> > out of date or incorrect. It is therefore essential that you verify all
> > such information with us before placing any reliance upon it.
> >
> > Northern Electric plc
> > Carliol House
> > Market Street
> > Newcastle-upon-Tyne
> > NE1 6NE
> > Registered in England and Wales: Number 2366942
> >
> >
> >
*************************************************************************
> >** *****************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
