Re: library cache miss ratio very high - how to find out the rot cause

On Apr 22, 8:56 am, Helma <helma.vi..._at_hotmail.com> wrote:
> On Apr 22, 2:18 pm, Frank Zimmer <frank.zim..._at_euroscript.lu> wrote:
>
>
> > Mladen Gogala schrieb:
>
> > > On Tue, 22 Apr 2008 10:28:55 +0200, Frank Zimmer wrote:
>
> > >> Hi
>
> > >> we are driving a Documentum based ECM system on a big sun machine (12
> > >> CPU) (i know that Documentum is creating very bad SQL) Right now
> > >> sometimes (quite often) the system is totally slow. With some monitoring
> > >> we can see that at that point the miss ratio for the sql library cache
> > >> goes over 60 %. At that time the shared SQL area of the SGA is used only
> > >> by 60 % and the parse to execute ratio is nearly one.
>
> > >> How can i get deeper into that ?
>
> > > First and foremost, is your performance suffering, are your users
> > > complaining? If they are, address the problem. To do that, see what
> > > are the user processes waiting for. In my experience, high library cache
> > > miss ratio is a sign of intense parsing. On the other hand, I haven't seen
> > > any statspack or AWR report, I haven't seen any logs and I have had no
> > > insight into your database so I can't really give you an answer. I don't
> > > even know the version of your database.
>
> > Hi,
>
> > we are running 10.2.0.3 on Solaris 10
> > But my question was more related to what kind of possibilities
> > i have to find out the root cause.
> > I still have seen heavy parsing but i'd like to know what sql statements
> > there are running inside a given timeframe to see how to make them better.
> > Also the AWR does not show some useful details, or is there a way to
> > make this report more detailed ?
>
> > Regards
> > Frank
>
> Hi Frank,
>
> You can check the statspack (and TOP SQL) if you want to know more
> about the sql in play during slow periods.
>
> Be careful to see the hitratio as a representative of the performance
> problem. I have seen cases where the hitratio can be improved
> considerably, but only at the expense of *worse* performance. So you
> need to look for the root causes of your slow system, not of your low
> hitratio. There may be a connection between these two.
> As Vladimir pointed out, it's likely that bind variables aren't used.
>
> To Vladimir: What are the 'bizarre side effects' you have seen from
> the CURSOR_SHARING=SIMILAR ? I haven't seen anything yet from my
> experience.- Hide quoted text -
>
> - Show quoted text -

Frank,

1 - Use of the AWR requires a tuning pack license.

2 - Statspack and the traditional v$ dynamic performance views are free and can show you all current SQL. In the case of statspack it will save the "top" sql which you can query directly or see in the reports. Look at the statspack levels available to you.

Failure to use bind variables and heavy flushing of the SGA to make room for more SQL can lead to the problem identified.

HTH -- Mark D Powell -- Received on Tue Apr 22 2008 - 09:27:05 CDT