Analysis of ORA-04031: unable to allocate xxx bytes of shared memory ("shared pool","unknown object","KGLH0^6ab33a","kglob")

Hi all,

One of our production databases is frequently experienced below errors in alert log and database became unresponsive due to continuous hit of this error and CPU load also went high at that time.

Here the time duration for this error is between Tue Dec 09 15:33:18 2014 and Tue Dec 09 15:50:27 2014.

Errors in alert log:

ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","KGLH0^6ab33a","kglob")

ORA-04031: unable to allocate 120 bytes of shared memory ("shared pool","unknown object","CCUR^857b59d8","kkocsCardEst: kkocsNodeAllocated")

ORA-04031: unable to allocate 1072 bytes of shared memory ("shared pool","select HIERARCHY_TYPE, SITE_...","CCUR^28cddb74","kkocs.c.kgght")

ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","CCUR^9d1660bd","kglob")

ORA-04031: unable to allocate 4248 bytes of shared memory ("shared pool","UPDATE QP_NPREQ_LINES_TMP A ...","CCUR^95757316","column mapping")

Our immediate solution is to restart the database since the db became unresponsive and i have prepared an RCA for this issue to report to customer. Below is my finding.

Whenever CPU load is high in our servers we usually will take the top CPU consumed session details with wait events using below query (The blow query is for only current sessions consuming more CPU)

set pages 1000

set lines 1000

col OSPID for a06

col SID for 99999

col SERIAL# for 999999

col SQL_ID for a14

col USERNAME for a15

col PROGRAM for a23

col MODULE for a18

col OSUSER for a10

col MACHINE for a25

select * from (

select p.spid "ospid", 

(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,

se.VALUE/100 cpu_usage_sec,ss.event 

from

v$session ss, 

v$sesstat se, 

v$statname sn,

v$process p

where

se.STATISTIC# = sn.STATISTIC#

and

NAME like '%CPU used by this session%'

and

se.SID = ss.SID

and ss.username !='SYS' and 

ss.status='ACTIVE'

and ss.username is not null

and ss.paddr=p.addr and value > 0 

order by se.VALUE desc);

From the output of the above query,I noticed some wait events on “SGA: allocation forcing component growth, latch: cache buffers chains" which related to excessive memory resize operations".

You can also track the session details with their wait events using below query if you didn't get it when the load was high.

set lines 1000
set pages 500
col SAMPLE_TIME for a28
col sid for 9999
col serial for 99999
col MODULE for a17
col EVENT for a45
col MACHINE for a25
select distinct SESSION_ID sid,SESSION_SERIAL# serial,user_id,sql_id,MODULE,MACHINE,EVENT from dba_hist_active_sess_history
where sample_time between to_date('22-DEC-14 15:30:00', 'dd-MON-yy hh24-mi-ss')
and to_date('22-DEC-14 15:50:00', 'dd-MON-yy hh24:mi:ss')  order by 1;

Note:

in the above query, sample_time is the time we got hit by the error ORA-04031 in the alert log.

SGA: allocation forcing component growth:

This is an AMM message indicating that the size of sga_target or memory_target is too small, causing excessive AMM resize operations.

latch: cache buffers chains:

"latch: cache buffers chains" contention is typically encountered because SQL statements read more buffers than they need to and multiple sessions are waiting to read the same block.

Here I concentrated on wait event "SGA: allocation forcing component growth".in this analysis.

I tracked SQL queries which is causing wait event "SGA: allocation forcing component growth" by using below query.

set lines 1000

select distinct sql_id,EVENT from dba_hist_active_sess_history

where sample_time between to_date('09-DEC-14 15:30', 'dd-MON-yy hh24:mi')

and to_date('09-DEC-14 4 15:50', 'dd-MON-yy hh24:mi') and EVENT like 'SGA%' group by SAMPLE_TIME,sql_id,EVENT order by 2;

OUTPUT:

SQL_ID        EVENT

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

2h83mk46003q4 SGA: allocation forcing component growth

3n87y2yarawsq SGA: allocation forcing component growth

6dgaurcnhgcru SGA: allocation forcing component growth

9x37c2yp147t5 SGA: allocation forcing component growth

bp9419hksnw42 SGA: allocation forcing component growth

gaf2hsang61x7 SGA: allocation forcing component growth

Note:

in the above query, sample_time is the time we got hit by the error ORA-04031 in the alert log.

According to the MOSC doc “How To Deal With "SGA: allocation forcing component growth" Wait Events (Doc ID 1270867.1)”

SYMPTOMS:

When using Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM)  by setting either SGA_TARGET or MEMORY_TARGET, you may see locking issues when the following event is encountered:

'SGA: allocation forcing component growth'  may or may not be accompanied by ORA-4031 errors.

CAUSE:

ASMM and AMM use an auto-tuning memory management scheme. With either of these architectures enabled, memory can be moved automatically between components in the SGA (such as the buffer cache and shared pool) in order to fill a memory request in one of these components.

The 'SGA: allocation forcing component growth' can occur if the SGA_TARGET or MEMORY_TARGET is set too low for the instance, and there has to be frequent moving of the memory between the components of the SGA like the shared pool and the buffer cache.

SOLUTION:

The 'SGA: allocation forcing component growth' can often be reduced by increasing the setting of either the SGA_TARGET (for an ASMM setup) or MEMORY_TARGET (for an AMM setup). Enlarging the SGA_TARGET or MEMORY_TARGET will allow for the various SGA components to grow without the need for "stealing" memory of another component.

Increasing by at least 10% would be a good starting point and it can be tuned further if needed.

So we have advised to increase the SGA size based on SGA_TARGET_ADVISORY report.

And also you can easily get analysis in just few seconds by uploading alert log and trace file via online tool "ORA-4031 Troubleshooting Tool (Doc ID 1521925.1)" from oracle support website