Oracle 9i Latching Issue

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 Received on Fri Mar 28 2008 - 08:18:58 CDT