Handling ORA-04031: unable to allocate 3000 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","call")

Now this issue has been running for long and started to catch my eyes. So I decided to do some research to find out the root cause and possible solutions.Application has started to complaint about memory shortage in the database and bouncing database seemed easy fixes for initial stage until I started digging out more on it . The error Application Team sees on their panel looked like below.

 ORA-04031: unable to allocate 3000 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","call") 

 As most of the DBAs does , I also tried to flush shared pool using script flushsga and soon it started filling up again and database strapped for memory within couple of minutes after it .

 To plot historical details .. It was good enough to check awr report .I started to chek memory statistics section in awr report to find how database was using memory during the time of issue.I've put in comments along side as per my understanding .

 Memory Dynamic Components

+++++++++++++++++++++++++++++++

Observation
DEFAULT buffer cache <<<<<< ONLY 64 MB for buffer cache.
SGA Target  <<<<<<<<<<<<<< ~20 GB SGA
shared pool<<<<<<<< ~19.2 GB used for shared pool, almost all memory used for shared pool

SGA breakdown difference

+++++++++++++++++++++++++++++++++

 

 Pool Name Begin MB End MB % Diff

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

shared KGLH0 6,050.23    6,050.53    0.01<<<<<<<<<<<<<<<<<< Child cursors usage

shared SQLA 4,370.11    4,365.77   -0.10 <<<<<<<<<<<<<<<<<< this is part of the library cache,This area is highly related to the amount of SQLs being executed. So, if you increased the workload that memory area is going to require more memory space.

shared free memory 5,545.73    5,552.27    0.12 <<<<<<<<<<<<<< 8 GB free memory

 Conclusion:

 SGA memory allocation pattern is directly influenced by application code and workload. Parameter alone cannot determine how SGA elements are allocated.
 
a.)Needed to work with application to understand child cursors usage and also monitor KGLH0 memory usage.
b.)I see huge free memory during ORA-04031 errors, so i suggest following action to to merge durations and monitor ORA-04031 errors.
shared free memory 8,172.13 8,345.36 2.12 <<<<<<<<<<<<<< 8 GB free memory

 Disable duration pools by setting

_enable_shared_pool_durations=false.

This will ensure that free memory is not spread across 4 different duration's and since all requests can tap into a common pool of free memory, it will reduce the likelihood of ORA-4031.

By default, there are 4 durations in SGA. By combining to a single duration, duration will not encounter memory exhaustion while another duration has free memory. The scenario observed in this environment may also caused large Shared Pool and very small buffer cache, which may contribute to performance degradation.

Please test with the setting commentated and see if it can elevate the issue:

for pfile
_enable_shared_pool_durations = false

for spfile
alter system set "_enable_shared_pool_durations"=false scope= spfile;

and restart the instance.

 c.) Check the literal usages . It causes fragmentation in sga. and major contributor queries for sga fragmentation  can be found using below queries

 -- Look for one time execution 1 ..
SELECT sql_text, parsing_schema_name, executions FROM v$sqlarea
Where parsing_schema_name not in ('SYS','SYSTEM','DBSNMP')
ORDER BY executions desc;

-- top contributor for fragmentation

select substr(sql_text, 1, 40) "SQL",
       count(*) cnt,
       sum(executions) "TotExecs",
       sum(sharable_mem) mem,
       min(first_load_time) start_time,
       max(first_load_time) end_time,
       max(hash_value) hash       
from v$sqlarea
where executions < 5   
group by substr(sql_text, 1, 40)
having count(*) > 30   
order by 2 desc;


Highest execution unshared - look for higher number

SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
)
,
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC

What was done ??

Below database parameters were changed .In addition to it App Team was asked to use bind variable and to correct some of the literal extensive queries .

i) sga_max_size parameter has been changed form 20g to 30g.
ii)_enable_shared_pool_duration=false
iii) shared_pool_size=5g
iv) db_cache_size=8g