Re: ORA-4031 and Shared Pool Fragmentation

The words keep and default refer to block buffer pools, not the shared pool. Upgrading to 8.1.6 would allow you to set cursor_sharing=force. Any sql that is differing only in literals would automagically have bind variables substituted in. If the sql is truely dynamic, this won't help but if its just that a third party application uses literals instead of binds, it could. There is a script that will automatically pin packages that are in the shared pool it can be downloaded off of http://www.evergreen-database.com.

In article <01c048d7$79fdd260$617da8c0_at_opc097>,   "Alan Molloy" <alan_molloy_at_hotmail.com> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 07 2000 - 12:19:58 CST