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

On Apr 22, 4:56 pm, 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.

We've hit a couple of bugs associated with ancillary operators (like CONTAINS()/SCORE() in Oracle Text, where mandatory literals were being replaced with bind variables and the whole thing stopped working,) but that was in our early 9.2 days and those particular bugs are long time fixed. Also, SIMILAR assumes the histograms are available and current, so if you blindly set it and don't gather all needed stats, you may not get what you expect.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Apr 23 2008 - 07:54:56 CDT