Re: ORA-4031 and Shared Pool Fragmentation

Thanks for your reply Steve,
I'll increase the size of my shared pool and await an upgrade to 8.1.6. Regards,
Alan.

Steve Adams <steve.adams_at_ixora.com.au> wrote in article <3a08be37.1125359641_at_nsw.nnrp.telstra.net>...
> 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 Wed Nov 08 2000 - 04:43:11 CST