Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

Export and import multiple schema using expdp/impdp (Data Pump utility)

Use the below sql query to export and import multiple schema: expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log sql query to export and import a schema: expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both. To export meta data only to get ddl of the schemas: expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics To get the DDL in a text file: impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfil...
3 comments
Read more

Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan

Deal all, Below post content is not my own. It is taken from MOSC document but the example shown below is taken from our environment. I got to know the below topic when we had experienced the bad SQL query performance in one of our production environment during daily batch running time. Hence we had implemented the same mentioned in the title "Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan". Introduction The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints. Some changes in those conditions may affect the calculations of the plan...
9 comments
Read more

SQL query to find the cause or reason for more archive log generation

Finding reason or cause for heavy or more archive log generation in a particular time period As i said in the previous post we are going to see SQL queries to find the cause or reason for more archive log generation in a problematic window... Sometimes customer would ask the sql query which generated more archive logs sometimes before or days before (not for current archive logs generation which is described in the previous post). In such scenarios, follow the steps below. Step 1: ====== First you must know the timing when more number of archive logs generated in the databases. for that you can use the below query. Below sql query gives how many number of archive logs generated for each and every hour... col day for a12 set lines 1000 set pages 999 col "00" for a3 col "01" for a3 col "02" for a3 col "03" for a3 col "04" for a3 col "05" for a3 col "06" for a3 col "07" for...
1 comment
Read more