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

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. Received on Tue Apr 22 2008 - 07:56:20 CDT