RE: Buffer Busy Waits -- Sanity check please

[Khedr, Waleed]

Title: Buffer Busy Waits -- Sanity check please

Check what segment owns this block: file#      72 block#      109177 

 

This could help finding the way to approach this problem.

-----Original Message-----
From: Thomas Jeff [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 26, 2001 6:21 PM
To: Multiple recipients of list ORACLE-L
Subject: Buffer Busy Waits -- Sanity check please

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