Buffer Busy Waits -- Sanity check please

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Mon, 26 Nov 2001 15:30:21 -0800
Message-ID: <F001.003CD472.20011126152030@fatcity.com>

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

---

 
Received on Mon Nov 26 2001 - 17:30:21 CST