Re: Oracle 9i Latching Issue

On Mar 28, 1:36 pm, "Ana C. Dent" <anaced...@xxxxxxxxxxx> wrote:
Johne_uk <edg...@xxxxxxxxxxxxx> wrote in news:412ea049-3b68-4126-a1ad-
f7c5b6379...@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:





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

Database are not slow.
SQL statements are slow.
Identify the slow SQL using SQL_TRACE & make them faster.- Hide quoted text -

- Show quoted text -

The bulk of apps on the server are 3rd party and I do not have the
ability to rewrite the sql as its embedded into the actual
applications. The apps which are 'our own' are written in India and
there would be a huge lead time to make any progress.

cheers
.

Quantcast