Re: Oracle 9i Latching Issue

From: Johne_uk <edgarj_at_tiscali.co.uk>
Date: Fri, 28 Mar 2008 06:44:14 -0700 (PDT)
Message-ID: <c23bb983-dbbb-4b3a-8675-bf4cd8281261@i12g2000prf.googlegroups.com>

On Mar 28, 1:36 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:

> Johne_uk <edg..._at_tiscali.co.uk> wrote in news:412ea049-3b68-4126-a1ad-
> f7c5b6379..._at_s13g2000prd.googlegroups.com:
>
>
>
>
>
> > 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 Received on Fri Mar 28 2008 - 08:44:14 CDT

Original text of this message