Re: Help: Library cache

Mike-

First, what exactly convinces you there is a library cache performance problem? Hit ratios are about as misleading for the lib cache as they are for the buffer cache. You probably ought to start by running Statspack snaps at fairly tight intervals (say 1/2 hour) during peak periods or periods of particular concern. Then, run a report and see what the top wait events are and go from there. If you see lots of "latch free" waits and the details show you "library cache" and / or "shared pool" latches, then you probably have a lib cache problem.

If indeed there is some valid reason to be worried about this lib cache, you probably need to pay attention to the "reloads" and "invalidations" values. Invalidations cause hard parsing to take place when there is otherwise good, sharable sql in the cache. What is causing the invalidations? Normally ddl, perhaps table truncations or someone is analyzing objects during production hours. Something dumb, no doubt.

Reloads indicate good sharable sql being flushed out of the lib cache to reuse memory for new statements. Common in a "mixed workload" environment where you have lots of sharable and non-sharable sql. The normal cure is to use the dbms_shared_pool package to "keep" the statements you care about. Read the doc for the "how to". The main thing is to pass both the hash value and the address, concatenated, to the keep procedure. Works like a champ.

In the meantime, forget about the ratios. At best they are interesting, but not useful.

• ricky

Mike F wrote:
>
> I am new to statspack performance tuning. Even though our library
> cache hit ratio is very high, 99%, ther is some reading which concerns
> me. Could somebody tells me, based on the following reading, is there
>
> anything we need to improve?
>
> but for sql area,
>
> get request 715,631
> pct miss is 0.1
> ping request is 2,252,042
> pct miss is 0.1
> reload is 309
> invalidation is 919
>
> for table/procedure
>
> get request 38,132
> pct miss is 0.3
> ping request is 3,193,023
> pct miss is 0.0
> reload is 111
> invalidation is 0
>
> Thank you very much for you help
>
>
>
> --
> Sent by dbadba62 from hotmail in field com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
Received on Tue Jan 22 2002 - 11:15:00 CST