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

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.

-- 
Mladen Gogala
http://mgogala.freehostia.com