Re: ORA-4031 - shared memory fragmentation

From: Bryan Warfield <Bryan.Warfield_at_DAL.frb.org>
Date: 1997/04/22
Message-ID: <335D0750.291B@DAL.frb.org>#1/1

Graham Newton wrote:
>
> We've been experiencing several ORA-04031 errors recently when perfoming the
> most innocuous SQL statements on an Oracle 7.2.3 server (IBM RS/6000 AIX
> 3.2.5).
>
> Nothing we've done so far has solved the problem. Basically all we've tried
> is increasing the shared_pool_size a little.
>
> Has anyone else been experiencing these problems? Does anyone have any ideas
> on how to overcome them?
>
> Graham Newton

Graham,
I saved the following info from previous discussions on this topic, hope it helps.
Pinning large packages fixed the problem for me. See Oracle Magazine November/December 1996 issue, pp. 113-114, or Advanced Oracle Tuning and Administration, Oracle Press/Osborne for info in pinning packages. Bryan Warfield

---

>> 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 be 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. Received on Tue Apr 22 1997 - 00:00:00 CDT