Understanding the role of SHARED_POOL_RESERVED_SIZE

Understanding the role of SHARED_POOL_RESERVED_SIZE

(SHARED POOL FRAGMENTATION (ORA-04031) , applicable to Oracle 9i)

ORA-04031 error is returned if single chunk of space in shared Pool is

not available in order to allocate the space for the object.

The Message you will get is the following:

04031, 00000, "unable to allocate %s bytes of shared memory.

This error usually happens due to inadequate sizing of Shared

Pool/Shared Pool reserved Size or due to heavy fragmentation in

Shared Pool.

SQL> select * from v$sgastat where name like '%free%';

POOL        NAME                            BYTES

----------- --------------------------     ----------

shared pool free memory                   14668032

java pool   free memory                   67108864

Even though we have 14MB of free memory available in Shared Pool, we

are getting this ORA-04031.

This is because, we are trying to make large allocation and we are not

getting a biggest chunk of contiguous memory in shared_pool and the

Shared pool is fragmented.

In the following case, we are trying to allocate 3.59MB

(LAST_FAILURE_SIZE) and we have a contiguous space of only .92MB in

SHARED POOL RESERVE. This information we can get from

V$SHARED_POOL_RESERVED table using the following query:

We need to look at request_failures, last_failure_size & free_space.

SELECT FREE_SPACE/1024/1024,USED_SPACE/1024/1024,LAST_FAILURE_SIZE/1024/1024,REQUESTS,REQUEST_FAILURES

FROM V$SHARED_POOL_RESERVED;

FREE_SPACE   USED_SPACE    LAST_FAILURE_SIZE  REQUESTS    REQUEST_FAILURES

======================================================================

.92MB                3.88MB                   3.59MB

 141111624           453882

Our goal is to make the value of REQUEST_FAILURES to 0.

Find out the current values of SHARED_POOL_SIZE &

SHARED_POOL_RESERVED_SIZE parameter in the init.ora file.

SQL> show parameter shared_pool%

NAME                                     TYPE             VALUE

------------------------------------------     --------               -------

shared_pool_reserved_size       big integer      4194304   ( 4MB)

shared_pool_size                     big integer    83886080  (80MB)

In order to achieve it, Oracle recommends to set the

shared_pool_reserved_size to atleast 10% of the shared_pool_size

parameter. In this case it should be set to 8MB instead of 4MB.

If we set shared_pool_reserved_size as 8MB in the init.ora file, 8MB

of 80MB shared_pool will be reserved for large

allocations in the shared_pool.

Conclusion:

Oracle check the shared pool reserved list for space when there is not

enough contiguous block of sufficient size

on the shared pool free list.  This means that until the shared pool

becomes relatively full (or fragmented), Oracle will

not utilize the shared pool reserved list. So if you size the

SHARE_POOL_RESERVED_SIZE properly, you may avoid getting ORA-04031

error

and if you allocate too much of space to SHARED_POOL_RESERVED_SIZE,

you may end up wasting the memory also.