Re: ORA-04031 Problem

Yong Boon, Lim wrote:

> My company database has recently encounter the oracle error ORA-04031,
> i.e.
> ORA-04031 unable to allocate num bytes of shared memory num, num, num
> my question is, is shared memory followed LRU algorithm like Data buffer?
>
> Thank you!

Yes, the Shared Pool does indeed use an LRU algorithm.

You will still get 4031s on the Shared Pool, however, because of memory fragmentation... as things are aged out, they leave behind memory 'holes'. Eventually, you end up with lots of little, discrete, 'holes', and then something comes along (a new PL/SQL procedure, a new complex SQL query etc) which requires a single allocation of a relatively large chunk of contiguous memory. Your myraid of 'holes' might provide all the memory this thing needs, but it can't be allocated because it's not contiguous free space. Hence a 4031. Your 'holes' are only re-useable if things want memory allocations smaller than, or equal in size to, the size of those 'holes'.

You might want to check, though: the 4031 error is also raised when the *large* pool runs out of memory, and that *doesn't* have an LRU mechanism.

To fix out of error memory problems in the large pool, make the large pool bigger.

To fix out of memory errors in the shared pool, you should be setting the shared_pool_reserved_size parameter so that things that request large chunks of memory have a special area of their own to look in first. You should also investigate the use of
dbms_shared_pool.keep('procedure/package_name'), immediately after startup -that way, standard procedures and packages that are used a lot by your application can pinch all the memory they need as soon as it is made available, instead of waiting for a user to invoke them and then fight over whatever memory fragments are available by then.

In 8i, there's an 'after startup on database' trigger event, so you can write a trigger to cause all these packages and procedures to be kept automatically. Before 8i, it's a question of remembering to run a script you have to write yourself straight after every startup to achieve the same thing.

Regards
HJR Received on Tue Sep 16 2003 - 04:53:47 CDT