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.
