Optimizing the Shared Pool Reserved Area

Managing the shared pool is not just about setting the sga_target or memory_target or even shared_pool parameter. More often than not, you see ORA-04031: unable to allocate <> bytes of shared memory error. It is the indicator that there is space problem with the shared pool. Either the shared pool needs more space, or it is fragmented.

If you see that there is plenty of space in shared pool and yet you are getting the ORA-04031 error, then it is likely that you are suffering from the shared pool fragmentation. If the objects don't manage to get space from the shared pool, and if their requested space is higher than the  _shared_pool_reserved_min_alloc, then they get it from the shared_pool_reserved_area, which is reserved for free space in case space is needed in shared pool. But ORA-04031 can happen even when there is plenty of space in the shared_pool_reserved_area, because the requested space by the objects is less than the _shared_pool_reserved_min_alloc.

So even when you have enough space in reserverd area, you don't get to use it because requested space is less than the min alloc. If that is happening frequently, you can decrease the size of _shared_pool_reserved_min_alloc, so that the reserved space can be used by starving sessions.

You can check how much shared pool space was requested and failures:

SQL>  select  REQUEST_FAILURES, LAST_FAILURE_SIZE  from v$shared_pool_reserved  ;

REQUEST_FAILURES LAST_FAILURE_SIZE
---------------- -----------------
            2036              4160


and the size of _shared_pool_reserved_min_alloc is:

SQL> select a.ksppinm name, b.ksppstvl value from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%shared%pool%min%alloc%' order by name;

NAME VALUE
------------------------------- ------
_shared_pool_reserved_min_alloc 4400

So 2036 requests for the space failed because the size they requested was 4160, and they need to be higher than 4400 to get space from shared_pool_reserved_area. If you see that frequently, and you have free space in shared_pool_reserved_area, then decrease the size of _shared_pool_reserved_min_alloc below to 4160 to service those requests.

Check the free space in shared_pool_reserved_area as:

 select KSMCHCOM, count(*), sum(KSMCHSIZ) from sys.x$ksmspr group by KSMCHCOM;

SQL> select KSMCHCOM, count(*), sum(KSMCHSIZ) from sys.x$ksmspr group by KSMCHCOM;

KSMCHCOM           COUNT(*) SUM(KSMCHSIZ)
---------------- ---------- -------------
free memory             295     239619456