Re: Oracle 9i Latching Issue

From: <>
Date: Fri, 28 Mar 2008 07:42:00 -0700 (PDT)
Message-ID: <>

On Mar 28, 8:18 am, Johne_uk <> wrote:
> Hi,
> I'm a beginner Oracle DBA who is facing a performance issues on a 9i
> instance running on Solaris. The db
> instance hosts a number of web applications including in-house apps
> and oracle apps such as Discoverer.
> For the past month there have been periods where the db has slowed
> down to an almost unusable state. I've
> looked at various options including reusable sql in the shared pool
> etc but, using statspack, I've finally manged to find a corellation
> whereby during slow periods the latch free waits % increases
> dramatically.
> As there are a multitude of apps running from this instance I need to
> try and pinpoint whichs app(s) are generating the latch free waits.
> My findings. When all is well my Latch Hit % is 99.xx which is normal.
> During slowdown earlier today this dropped down to 85% indicating an
> issue.
> Digging deeper into this the primary culprit for this latch activity
> seems to be with cache buffer chain latches which were experiencing a
> 25% miss rate.
> Once the db speeded up latches reverted back to being insignificant.
> My suspicion is that the issue is being
> caused by Discoverer sessions as the sessions always features
> predominantly in statspack ouput with large
> volumes of buffer gets during slowdown.
> However, what I now need is some input as regards how to find the
> offending sessions that are causing these
> excessive latch waits times. I'd appreciate some assistance from the
> user community on this as I'm on a steep
> learning curve for my experience level.
> It would almost be impossible to rewrite the sql in any apps but I may
> have the option to remove any offending
> apps to another db server as opposed to our primary prod instance.
> It should also be pointed out that the issue is not caused by rogue
> sessions thrashing the db, as activity can be minimal during slowdown.
> Thanks in anticipation
> John

Usually such contention is caused by simultaneous access to a given object (often a single block of that object). It may be an index leaf block causing this contention; you might try rebuilding the suspect index as a reverse-key index and see if that spreads out the access (keys which were adjacent in a standard index likely won't be in a reverse-key index and will lighten the load on a single index leaf block). If it's the root block of that index then there isn't much you can do other than rebuild the table as a partitioned object and use local indexes.

You might look at Metalink Note 163424.1 to investigate the 'hot block' angle.

David Fitzjarrell Received on Fri Mar 28 2008 - 09:42:00 CDT

Original text of this message