ORA-04031 - unable to allocate %s bytes of shared memory (\”%s\”,\”%s\”,\”%s\”,\”%s\”).

Cause of Error :-

More shared memory is needed than actually allocated to Shared_Pool.

Reason :- 

Issue may be due to the complex SELECT queries that had run with huge list of input parameters to IN operator
and with OR concatenation.

ORA-04031: unable to allocate 664 bytes of shared memory ("shared pool","SELECT  "MANDT" , "OBJNR" , ...","sql area","ctxdef : kkslod")

First Perception Strikes -  Shared memory is running shortage in space and we need to increase amount of shared memory in init.ora file.

   ************************

Workaround :-

1. To check if ASMM is configured in the database : So, SGA is dynamically managing memory allocations to memory parameters.

Since, SGA_TARGET is configured : shared_pool_size is Ignored in parameter file.

2. Need to check if SGA has sufficient memory allocated to it if needed by memory parameters. the DBA should look at the current SGA dynamic components and SGA resize operations first before trying to give more memory to SGA/shared pool.

------------   show parameter SGA;  ------   show parameter SGA_TARGET;  ---------------

3. Oracle Support is also providing Automatic Diagnostic tools . We just need to attach and provide the associated trace file generated by ORA-04031.the DBA should look at the current SGA dynamic components and SGA resize operations first before trying to give more memory to SGA/shared pool.

Check how fast the resize ops is taking place -

SQL> SELECT component,
oper_type,
oper_mode,
parameter,
initial_size,
target_size,
final_size,
status,
TO_CHAR (start_time, ‘dd-mon-yyyy:hh24:mi:ss’) started,
TO_CHAR (end_time, ‘dd-mon-yyyy:hh24:mi:ss’) ended
FROM v$sga_resize_ops
ORDER BY started;

Check Shared Pool Advice how much extra memory is needed -

SQL> SELECT shared_pool_size_for_estimate, estd_lc_size, estd_lc_time_saved
FROM v$shared_pool_advice;

Check Shared Pool Reserved Size -

SQL> SELECT free_space,
avg_free_size,
free_count,
max_free_size,
request_misses,
request_failures
FROM v$shared_pool_reserved;

4. However, Fragmentation of shared pool memory is also another possible cause of Error .

It is caused by not using binding variable in queries. So even it appears you have free space in shared pool, you can't allocation enough space for a query. 

If the problem is more likely to be shared pool fragment problem. No matter how much memory you give to shared pool, eventually the problem will come back again if you don't deal with the source of the problem.