Re: Contention for undo segments

[K Gopalakrishnan <kaygopal@xxxxxxxxx>]

Tim,

I don't know what application you are running , but buffer busy global
CR can happen for many reasons.  I suspect some sort of enqueue
contention (TX?) or some serialization or hotspots in the objects and
that is causing the problem. DO you have the statspack during that
time.

You can use the following script to identify the system calls which
are responsible for buffer busy events. This will give the sample out
put like this..


 1  SELECT WH.KCBWHDES MODULE,SW.WHY0 CALLS,SW.WHY2 WAITS,
 2  SW.OTHER_WAIT "CAUSED WAITS"
 3  FROM x$kcbwh WH, x$kcbsw SW
 4  WHERE WH.indx = SW.indx
 5  AND SW.OTHER_WAIT > 0
 6* ORDER BY SW.OTHER_WAIT
SQL> /

MODULE                  CALLS      WAITS CAUSED WAITS
------------------ ---------- ---------- ------------
ktswh72: ktsbget         2088          0            1
ktswh76: ktsxadd        34682          0            1
ktspfwh8: ktspScan        327          0            1
Init1

kdiwh08: kdiixs       3030936          0            1
ktewh25: kteinicnt     285908          0            1
ktbwh00: ktbgtl           491          0            2
ktspswh6: ktspInit        327          0            2
Scan

I have  a brief discussion about GC waits and the internals in
Chapter-8 of OWI book. Just have a loook if you have the book handy.



> As we wait for feedback from Oracle Support, we are trying to find the
> session that is at the head of the logjam. The reason code on which
> these processes are hanging is 130 (P3=130, buffer busy global CR
> request).  Our question is, how do you translate the
> V$LOCK_ELEMENT.LOCK_ELEMENT_NAME to a an actual resource and a SID?
> 
> (If you have any ideas for us, please cc: sbala@xxxxxxxxxx if you
> don't mind.  Thank you!)

-- 

Best Regards,
K Gopalakrishnan 
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
http://www.freelists.org/webpage/oracle-l

• References:
  • Contention for undo segments
    • From: Tim Hall

Other related posts:

Contention for undo segments

Re: Contention for undo segments