Re: Oracle 9i Latching Issue

From: Charles Hooper <>
Date: Sun, 30 Mar 2008 10:20:53 -0700 (PDT)
Message-ID: <>

On Mar 29, 4:09 pm, Johne_uk <> wrote:
> I'd just like to thank everyone who contributed.
> One last thing - is there anything I could to to the SYS index below
> to reduce latch waits.
> "There was also high waits on the SYS.I_OBJ2 index on the sys.I_OBJ2
> table. "
> SYS.I_OBJ2      2028
One thing that the Metalink article failed to mention is that you need to properly scope the query into V$LATCH_CHILDREN before making the auumption that you have a specific hot block. To do this, you would capture the current contents of V$LATCH_CHILDREN into another table, start a Statspack cature, let the system run normally where you typically experience slow performance with Discoverer, and after 15 minutes, stop Statspack and compare the ending values of V $LATCH_CHILDREN with those values that were captured in the separate table. Sort the delta values descending by sleeps and then misses.

If you find that a particular cache buffers chains latch floats to the top of the sorted list, with CHILD#=5883 and LEVEL#=1, you can use a query like this to determine the database objects and row ID (if the block belongs to a table) of the first table row in the block (note that this must be run by the SYS user):


  X$BH BH,
  LC.NAME='cache buffers chains'
  AND LC.CHILD#=5883
  AND BH.TCH>100

  BH.TCH DESC; It could very well be that while the touch count for SYS.I_OBJ2 is high, it might be that there were few sleeps and/or misses on the cache buffers chains latch that protects that block during the slow performance period.

I see that you attempted to create a custom DUAL table - you may want to undo that if you upgrade to 10g, as assess to the DUAL table in 10g takes 1/2 the logical IOs of a normal table (this is not the case in 9i).

You may want to check CPU utilization before and after the Statspack run, as high CPU utilization can lead to problems with latches and increased time for log file sync to complete. This may be caused by essessive logical IO, essessive in-memory sorts, excessive parsing (hard or soft), excessive recursive calls (triggers firing or space management), bad execution plans, etc. To determine if this is a potential problem, compare the before and after values found in V $SYSSTAT. Look at the delta value for "CPU used by this session" - divide the delta value by 100, divide again by the number of CPUs in the server, and divide once again by the number of seconds elapsed between the first capture of V$SYSSTAT and the end capture - this is the percentage of available CPU seconds that were used. If you receive a value of 0.90, then that indicates that an _average_ of 90% of available CPU capacity in the time period was used. It could be that 100% of available CPU capacity was used for 10 minutes, and slightly less than 90% for the remaining 5 minutes, so it is important to keep the duration of the Statspack run short so that the averages are not too misleading.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Mar 30 2008 - 12:20:53 CDT

Original text of this message