Re: library cache growth

Library cache growth is normal as the server gets busy. The cursors are organized in the library cache under a hash table. Library cache handles are linked to the table "buckets", and each handle references a parent cursor object.

When the hash chains average 2 handles or more in length, the entire hash table gets roughly doubled in size and all the lib cache objects are rehashed. This keeps contention down. Eventually, the table will stop growing, after about seven "doublings". So, this growth is normal and cannot really be controlled. It is just the server adapting to the workload.

The root issue is the library cache parsing activity. If you use bind variables and make your sql sharable, you will have less demand on the library cache. If you make your object references fully qualified, you will have less work to parse then statements. For example, select * from ricky.maria" instead of "select * from maria".

Follow the usual guidelines for statement tuning, bind variable usage, etc.

And, upgrade to a supported release, because it is the sensible thing to do.

• ricky

Prakash.Bhandari_at_cexp.com wrote:

> Hi,
>
> I am seeing a very wierd behavior in my Oracle 7.3.4.4 database. My shared
> pool size is currently set to 400MG. After bootup the performance is
> generally good until a heavy period during the day after which the library
> cache latch misses and sleeps go through the roof. There is no noticeable
> shared pool latch misses and sleeps. What I observe from v$sgastat view is
> that the size of the 'library cache' parameter continually increases.
> Although the subcomponents of the Library Cache like 'sql area' don't show
> the corresponding growth. It keeps on growing until the size is 320MB.
> Through out this period, I see the sql area invalidations of about 800-900
> every hour (v$librarycache view) and the reloads in the same magnitude.
>
> Oracle Support has been completely useless in resolving this issue. Their
> answer is upgrade but we can't upgrade without upgrading the Oracle
> Application Server to 4.0.8.2 and my management doesn't want to take the
> risk to do that.
>
> Any insights in this particular problem? I have tried pinning all the
> packages, increased session_cached_cursors (which seemed to help quite a
> bit) but I still have this period where the performance is bad.
>
> Thanks in advance.
>
> Prakash
>
> --
> Posted from mailgate.cexp.com [63.64.59.168]
> via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Mon Apr 02 2001 - 17:05:35 CDT