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

On Apr 23, 2:54 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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

Bolshoi spasibo, Vladimir :) Received on Thu Apr 24 2008 - 03:15:19 CDT