Re: Oracle internal memory management

In article <DUb5rLAivhdyEwp5_at_jimsmith.demon.co.uk>, jim_at_jimsmith.demon.co.uk says...
>In article <553elt$qcv_at_charnel.ecst.csuchico.edu>, Chris Hafey
><chafey_at_ecst.csuchico.edu> writes
>>Hello,
>> We have been getting 4031 (unable to allocate shared memory) errors. The
>>only help for this error is "increase shared memory". We have tried this,
>>but it doesn't solve the problem.
>
>There is a known bug in several Oracle versions (7.1.6 and early 7.2.? I
>think) where the shared pool becomes overly fragmented leading to this
>problem. There are patches available. The workaround is to execute an
>'alter system flush shared pool' command as often as necessary to
>prevent the problem. You lose the advantage of cached SQL in the shared
>pool this way, but you don't seem to be using it anyway.

The main cause of fragmentation and the 4031 error is large Pl/SQL objects (packages etc.) which get swapped out & then are unable to find a contiguous area of memory big enough to load them into again when they are again needed.

Flushing the shared pool may not a solution as the system performs a flush anyway when it can't find enough contiguous memory to load the object. Only objects that are not currently being referenced get flushed, so it may well not clear everything out anyway & imposes a major performance hit on the system.

It is a _very_good_idea_ to pin large packages you use on a regular basis using the dbms_shared_pool package. This ensures they are kept in memory (which is where they need to be anyway) - set up a job to pin them daily at least, especially things like DBMS_STANDARD which is referenced all over the place. The x$ksmlru table is also useful for keeping track of the allocation of objects in the shared pool. You also need to get rid of big anonymous blocks, use bind variables etc.

There is a very handy White Paper available from Oracle that covers all of this (note:15646.1 from OCIS), and we haven't had any trouble at all with the shared pool since we followed the advice therein.

Cheers.
Martin. Received on Thu Nov 07 1996 - 00:00:00 CST