Re: ORA-4031 and Shared Pool Fragmentation

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 - 10:31:46 CST