Re: Buffer Busy Waits -- Sanity check please

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