Re: ORA-04031 and shared pool

sfitzgerald_at_centurytel.net (Sean Fitzgerald) wrote in message news:<605a2fd5.0108290620.6b591c71_at_posting.google.com>...
> Things are aged of the shared pool. In fact, by the time you get the
> 4031, oracle has already done everything it can to flush out the pool.
> Problem is, the free memory is fragmented and you are getting the
> 4031 because a contiguous chunk of memory can't be found in the size
> you need.
>
> The view v$shared_pool_reserved will show you the size of the object
> that last failed.
>
> You need to look at "keeping" large and/or frequently used objects
> (typically procedures) in the shared pool. Having them move in and
> out causes fragmentation.
>
> You are also on the right track to use bind variables.

Actually Oracle has had several bugs in the past where O4031 errors were returned because the rdbms did not manage the pool correctly. Remember version 7.2.0 and Oracle changed how they manage the shared pool with ver 8.1+ (per Steve Adams Oracle Internals book). I think perhaps Oracle has a few bugs in their code. In fact considering some of our other tars with Oracle I am sure they do.

The view v$shared_pool_reserved usually returns no rows in our case. If it does show anything it is a small object that according a map of the shared pool is smaller than available space.

We pin all our large packages and most of the major Oracle packages on instance startup.

Our current set up has been very stable for a couple of years. It ran fine for weeks under 7.3 and 8.0 but immediately started to have problems in 8.1.7. We added 20M to the pool (plus 20M for java pool, unused) and still get errors.

In our shop 8.1.7.0 has proven to be less stable than 8.0.5.2.1. We are waiting for the .1 and .2 patch sets that are under development.

• Mark D Powell --