Re: ORA-4031 and Shared Pool Fragmentation

Hi Alan,

From 8.0 onwards a reserved pool is created for you automatically. All you can do is change its size (using 'shared_pool_reserved_size' - default is 5% of 'shared_pool_size') and the threshold size for entries to be eligible to go there (using '_shared_pool_reserved_min_alloc' - default is 5000 bytes, minimum is 4000 bytes).

Your 4031 errors are not so much an indication of fragmentation as an indication that your 'shared_pool_size' is too small. Just increase it in increments of 10% until the error goes away. Also, upgrading to 8.1.6 is a good idea, because shared pool management is greatly improved in that release.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/

-----Original Message-----
From: "Alan Molloy" <alan_molloy_at_hotmail.com>

Thanks,
So assuming that there is very little shared SQL, and I have that kept in the keep pool, and there is an every increasing set in dynamic statements(mainly INSERT and UPDATE statements with different values) coming at the database, am I right in saying that these statements are fragmenting my shared pool to the extent that these 4031 errors occur? Does having a reserved pool ensure an area that will be kept free for new statements, and is "flushed" on the fly? Regards,
Alan

Howard J. Rogers <howardjr_at_www.com> wrote in article <3a0812a4_at_news.iprimus.com.au>...
> Well, in that case you have the option to set up a reserved pool so that
> there is (theoretically) plenty of unfragmented space in your SGA to
> accomdate an extra package or three.
>
> You should also try and used dbms_shared_pool to keep what SQL you can,
 to
> minimise the contention issues, in the keep pool -though it sounds as if
> you'll have an enormous default pool, and a fairly small keep pool. A
> reserved pool will certainly help with the 4031 errors, though.
>
> A bigger shared pool size in and of itself is not a bad idea.
>
> Flushing will fix the same problem in the same way that taking a
> sledgehammer to a walnut will result in something approximating in a
 shelled
> piece of edible nut.
>
> Regards
> HJR
Received on Tue Nov 07 2000 - 20:51:42 CST