Re: library cache miss ratio very high - how to find out the rot cause
Date: Tue, 22 Apr 2008 14:18:49 +0200
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 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 ?
Frank Received on Tue Apr 22 2008 - 07:18:49 CDT