Re: library cache miss ratio very high - how to find out the rot cause
From: Frank Zimmer <frank.zimmer_at_euroscript.lu>
Date: Tue, 22 Apr 2008 14:18:49 +0200
Message-ID: <6763ddF2nd68cU1@mid.individual.net>
>
> 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.
>
Date: Tue, 22 Apr 2008 14:18:49 +0200
Message-ID: <6763ddF2nd68cU1@mid.individual.net>
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
Received on Tue Apr 22 2008 - 07:18:49 CDT