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

Johne_uk <edgarj@tiscali.co.uk> wrote in news:412ea049-3b68-4126-a1ad-
f7c5b6379c6d@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.

On Mar 28, 1:36*pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> Johne_uk <edg...@tiscali.co.uk> wrote in news:412ea049-3b68-4126-a1ad-
> f7c5b6379...@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

Johne_uk <edgarj@tiscali.co.uk> wrote in
news:c23bb983-dbbb-4b3a-8675-bf4cd8281261@i12g2000prf.googlegroups.com:

> On Mar 28, 1:36*pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>> Johne_uk <edg...@tiscali.co.uk> wrote in
>> news:412ea049-3b68-4126-a1ad-
>> f7c5b6379...@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

On Mar 28, 8:18*am, Johne_uk <edg...@tiscali.co.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

On Mar 28, 2:42*pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Mar 28, 8:18*am, Johne_uk <edg...@tiscali.co.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- Hide quoted text -
>
> - Show quoted text -

Thanks I'll look at that Metalink Note as I'm fairly of the mind that
it could be linked to a hotspot in the buffer cache.

cheers

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

Thanks David, that was a great pointer. As I suspected Discoverer has
caused some issues with latch waits on the DUAL table (I had already
created a local dual table for all EUL schemas to minimise this).

There was also high waits on the SYS.I_OBJ2 index on the sys.I_OBJ2
table.

SEGMENT_NAME TCH
SYS.I_OBJ2 2028
OPSKPI_EUL.DUAL 1648

Im going to investigate further but it looks likely i will move all
our Discoverer EUL schemas onto their own server.

cheers

On Mar 28, 10:24*am, "Ana C. Dent" <anaced...@hotmail.com> wrote:

snip

>
> It appears you then must use the undocumented parameter in initSID.ora
> _make_database_faster_=TRUE-

Looks like Johne is well on the way to identifying problems and
potential alternatives. Nice job!

The contributions of Ana Dent in this thread ... well not so much
imho.

On Mar 28, 11:13*pm, hpuxrac <johnbhur...@sbcglobal.net> wrote:
> On Mar 28, 10:24*am, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>
> snip
>
>
>
> > It appears you then must use the undocumented parameter in initSID.ora
> > _make_database_faster_=TRUE-
>
> Looks like Johne is well on the way to identifying problems and
> potential alternatives. *Nice job!
>
> The contributions of Ana Dent in this thread ... well not so much
> imho.

Also if possible check for full table scans due to lack of proper
indexing.......many times these rogue full table scans has caused a
lot of problems for us and you can easily catch them by monitoring the
v$session_longops view and also from the sql statements in the
statspack report that are ordered by buffer hits

On Fri, 28 Mar 2008 11:13:21 -0700 (PDT), hpuxrac
<johnbhurley@sbcglobal.net> wrote:

>On Mar 28, 10:24*am, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>
>snip
>
>>
>> It appears you then must use the undocumented parameter in initSID.ora
>> _make_database_faster_=TRUE-
>
>Looks like Johne is well on the way to identifying problems and
>potential alternatives. Nice job!
>
>The contributions of Ana Dent in this thread ... well not so much
>imho.

At the same time you got on your periodical 'Holier than thou
' chair, you usually misuse to bash Daniel Morgan, at the same time
not providing anything useful.
Mr. Hurley, are you an authority of moral wisdom, or just a plain
hypocrite? I would bet the latter: It begins to show quite a bit!!!

--
Sybrand Bakker
Senior Oracle DBA