| 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
Enabling parallel=2 or higher on this table could resolve the problem for the original poster.
Regards,
Waleed
-----Original Message-----
Sent: Saturday, December 01, 2001 9: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>
>
> --------------------------------------------
>
>
>
-- 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: Khedr, Waleed INET: Waleed.Khedr_at_FMR.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 - 11:58:28 CST
 |
 |