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

From: Frank Zimmer <>
Date: Tue, 22 Apr 2008 14:18:49 +0200
Message-ID: <>

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.


we are running 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 ?

Frank Received on Tue Apr 22 2008 - 07:18:49 CDT

Original text of this message