Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Buffer Busy Waits -- Sanity check please

Re: Buffer Busy Waits -- Sanity check please

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Mon, 03 Dec 2001 07:24:05 -0800
Message-ID: <F001.003D3415.20011203070022@fatcity.com>


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>
Sent by: root_at_fatcity.com
12/02/01 10:15 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:        
        Subject:        Re: Buffer Busy Waits -- Sanity check please



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: thomasje_at_tce.com
> > 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 postmaster_at_northern-electric.co.uk.
> >
> > 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: 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).
Received on Mon Dec 03 2001 - 09:24:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US