RE: Buffer Busy Waits -- Sanity check please

Check
what segment owns this block: <FONT face="Courier New" color=#000000>file#      72
block#      109177 
<FONT face="Courier New"
size=2> 
This could
help finding the way to approach this problem.

<FONT face=Tahoma

  size=2>-----Original Message-----From: Thomas Jeff   [mailto:ThomasJe_at_tce.com]Sent: Monday, November 26, 2001 6:21   PMTo: Multiple recipients of list ORACLE-LSubject:   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 <FONT   face="Courier New" size=2>of some sort in the buffer cache and other sessions   are blocked.   Although, I've checked and <FONT   face="Courier New" size=2>there's no DML ongoing, so I'm unsure as to why we   would see this.   Note that v$session shows <FONT   face="Courier New" size=2>78 and 393 to be INACTIVE, while 159 is   ACTIVE.    So it's like 159 can't write to <FONT   face="Courier New" size=2>the buffer cache because 78 and 393 have a lock   there.   Note that these are all defined <FONT   face="Courier New" size=2>as persistent connections, via the Vignette   front-end.   I'm sure all the clues are there <FONT   face="Courier New" size=2>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 <FONT face="Courier New" size=2>----- -------------------------- ------------ --
  • ------ ----- ------     <FONT face="Courier New" size=2>   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      
    <FONT face="Courier New"
    size=2>-------------------------------------------- <FONT face="Courier New" size=2>Jeffery D Thomas <FONT face="Courier New" size=2>DBA Thomson Information Services Thomson multimedia Inc. Email: thomasje_at_tce.com <FONT face="Courier New" size=2>DBA Quickplace: <A target=_blank href="http://gkmqp.tce.com/tis_dba">http://gkmqp.tce.com/tis_dba
    <FONT face="Courier New"
    size=2>-------------------------------------------- <FONT face="Courier New" size=2>