Subject:            Resolving the ora-4031 error
Author:            RBEARD
Modified:           11 Sep 94 10:24:36           



_____________________________________________________________________________

                    RESOLVING THE ORA-4031 ERROR

A lot of customers have been recently reporting that they were having
problems with the SHARED_POOL size.  This bulletin discusses resolving
the ora-4031 error.  It introduces a new package that allows users to
pin packages in the shared pool area to reduce memory fragmentation.
This is the dbms_shared_pool package available only with 7.0.13 and higher,
by including dbmspool.sql with the rdbms code.

Background:

Imagine the SHARED_POOL being  similar to a tablespace.  While you may
get ora 1547 when you can not get sufficient contigious free space in the
tablespace, similarly, you will get ora 4031 when you cannot get contiguous
freespace in the SHARED_POOL(sga).

Problem description:

Users usually get this error while attempting to load a big package
or while executing a very large procedure and there is not sufficient
contiguous free memory in the SHARED_POOL.  This may be due to:

        1. Fragmentation
                or
        2. Insufficient memory in the shared pool.

If it is due to the former, one needs to flush the shared pool and/or break up
the package or procedure into smaller blocks. If the shared pool is badly
fragmented, even using small packages or procedures can result in this error.
Flushing the shared pool might not help all the time because it will not flush
the PINNED buffers that are being changed at that time.

In the latter, SHARED_POOL_SIZE should be increased, default is 3.5M.
Increasing the SHARED_POOL might not be a   viable solution in some production
situations because we allocate the size of the SHARED_POOL during startup time.
This size is fixed and one cannot extend it on the fly. So increasing this size
will require a shutdown and startup of the database. Furthermore, we will not
revert back to version 6 implementation of the sga.

Current workarounds include:

1. Utilize dbms_shared_pool package available with 7.0.13 and higher.
   This package allows you to display the sizes of objects in the shared pool,
   and mark them for PINNING in the SGA in order to reduce memory
fragmentation.

NOTE:  due to bug #184513, you must grant SYS execute on the package in order
       to keep the package in  the shared pool.  Procedures are run under the
       schema of the owner, therefore, if SYS does not have execute
       privileges on the package, he will get the error 'object does not
       exist'.

2. Increase the SHARED_POOL_SIZE
   You need to change SHARED_POOL_SIZE because the default tends to be a low
   estimate when utilizing the procedural option.  As discussed earlier, one
   needs to shutdown/startup when changing the SHARED_POOL_SIZE.

Future Enhancements:

At the current time, oracle needs one contiguous chunk of memory to
process a given package or procedure. In the future releases of Oracle,
developement have modified the PL/SQL code such that it will request for
certain amount of memory and will accept several smaller contiguous chunks for
the same package or procedure there by reducing the probability of this error.

We have recognized that this was a deterrent but with the dbms_shared_pool
package, we believe that careful planning and adminstration will eliminate
this error for good.