Re: Oracle 9i Latching Issue
From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Fri, 28 Mar 2008 14:24:00 GMT
Message-ID: <4c7Hj.3$x67.0@newsfe12.phx>
>> 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 tex
>>
>> - Show quoted text -
Date: Fri, 28 Mar 2008 14:24:00 GMT
Message-ID: <4c7Hj.3$x67.0@newsfe12.phx>
Johne_uk <edgarj_at_tiscali.co.uk> wrote in
news:c23bb983-dbbb-4b3a-8675-bf4cd8281261_at_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 tex
> t -
>>
>> - 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 >
It appears you then must use the undocumented parameter in initSID.ora _make_database_faster_=TRUE Received on Fri Mar 28 2008 - 09:24:00 CDT