ORA-04031 unable to allocate 4080 bytes of shared memory

[Former Member]

Hi Everyone,

The error ORA-04031 has been consistently occuring in our production landscape.

We have Applied Oracle patches in BI7.0 (2004s - Oracle 10.2.0.2) system as per SAP's suggestions. (A set of 35 patches as per note 871096)

(Patches List - 5369855,5253307,6340979,5618049,7133360,5895190,6826661,4704890,6005996,5188321,6447320,3748430,5442919,4952782,5458753,5941030,5345999,6153847,5530958,4883635,5636728,4668719,5635254,5063279,7237154,5103126,4638550,4770693,4864648,6046043,7608184,6729801,5363584,6771608,6435823)

Applying oracle patches has not resolved this issue. The error is reoccuring. Now as per notes 1171650 and 830576 we have tuned in certain parameters.

We also checked the Notes 869006 and 1120481 but nothing much is helpful.

A Go-Live is scheduled this week. Could you please suggest what further actions need to be taken to identify the issue and resolve it.

Thanks in advance.

Best Regards

Bhupesh

[Former Member · Accepted solution]

Hi,

The Ora-4031 error has occurred again in our system.

The following error was observed in one of the trace files.

Did Not dump SGA Heap desc=380000058. Process holds latch.

Current time: 09/09/2009 20:36:09

Last SGA heap dump at 09/02/2009 02:49:22

SGA Heap Dump Count = 0

===============================

End 4031 Diagnostic Information

===============================

• 2009-09-09 20:36:10.134

ksedmp: internal or fatal error

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared

pool","unknown object","sga heap(1,0)","kglsim heap")

Could anyone please explain, what does this mean and

how to resolve the issue.

Thanks.

Best Regards

Bhupesh

[jgleichmann · Accepted solution]

Hi Bhupesh,

please check if value SGA_TARGET is set.

Oracle says the following:

Remove either of the entry of SGA_TARGET and Manual shared Memory Parameters from pfile
If SGA_TARGET is removed, then make sure <SID>.__parameters also removed. 
Make sure SHARED_POOL_SIZE and JAVA_POOL_SIZE is more than 200 MB as mentioned in Note 376612.1 "ORA-04031: unable to allocate xxxx bytes of shared memory" during upgrade to 10gR2.

Oracle support also provides a script to analyze this ORA-04031 please go ahead with this script ( Doc ID 430473.1 )

ORA-04031 is an error with a lot of causes, so it´s not easy to analyze it. Was the last posted error the complete error or is there anything like 'SMON: Restarting fast_start parallel rollback' coming after it?

Regards,

Jens

[Former Member]

Hi Jens,

Thanks for the suggestions.

We have reset the SGA_TARGET parameter and increased the other memory parameters too.

Error - ''SMON: Restarting fast_start parallel rollback' isn't found in the system.

Hope this solves the issue. We will keep monitoring.

Thanks again.

Best Regards

Bhupesh

[Former Member]

Hi Stefan / Jens,

Thanks for the help so far. Running the SQL statement produced more than 21000 rows.

We could get an idea as to which statement is using more memory. Please suggest how to go ahead.

Previous post wasn't readable; so am rewriting.

Thanks & Regards

Bhupesh

[Former Member]

Hi Stefan / Jens,

Thanks for the help so far.

Running the SQL statement produced more than 21000 rows. We could get an idea as to which statement is using more memory.

Top 5 statements are as below

SQL> select * from (SELECT SQL_text, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM FROM V$SQLAREA ORDER BY SHARABLE_MEM desc) where rownum <=5;

SQL_TEXT

-

---

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM

-

---

-

---

-

---

select a.segment_type, round(a.mb,2) size_mb, round(decode(seg_num,0,0,a.mb/a.seg_num),2) size_avg_mb, a.seg_num, a.ext_num, nvl(b.more100ext,0) more100ext, decode(lower(a.segment_type),'table',c.com_tab, 'index',d.com_ind, 'table partition',e.com_tpar, 'index partition',f.com_ipar, 0) compressed, decode(lower(a.segment_type),'table',g.log_tab, 'index',h.log_ind, 'table partition',i.log_tpar, 'index partition',j.log_ipar, 'lobsegment',k.log_lob, 'lobindex',k.log_lob, 0) nologging from (select segment_type, nvl(count(*),0) seg_num, sum(nvl(bytes,0))/10

1257431 441584 436528

select a.username user_name, a.user_id user_id, round(nvl(b.mb,0),2) size_mb, nvl(b.seg,0) segments, nvl(c.tab,0) tables, nvl(d.ind,0) indexes, nvl(b.ext,0) extents, a.default_tablespace default_tablespace, a.temporary_tablespace temporary_tablespace, to_char(a.created, 'dd.mm.yyyy') created, to_char(a.created, 'hh:mm:ss') created, a.account_status account_status, a.profile profile from dba_users a, (select owner, count() seg, sum(nvl(bytes,0))/1024/1024 mb, sum(nvl(extents,0)) ext from dba_segments group by owner) b, (select owner, count() tab from dba_segments where segment_type='TABLE' group by owner) c, (select owner, count(*) ind from dba_segments where segment_type='INDEX' group by owner) d where a.username=b.owner() and a.username=c.owner() and a.username=d.owner(+)

578473 200064 196176

SELECT count(*) over () as total_count, sd_xe_ash_nm.event_name, sd_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WHEN (sd_xe_ash_nm.parameter1 is NULL OR sd_xe_ash_nm.parameter1 = '0') THEN 0 ELSE 1 END) as p1valid, sd_xe_ash_nm.parameter2 as p2text, (CASE WHEN (sd_xe_ash_nm.parameter2 is NULL OR sd_xe_ash_nm.parameter2 = '0') THEN 0 ELSE 1 END) as p2valid, sd_xe_ash_nm.parameter3 as p3text, (CASE WHEN (sd_xe_ash_nm.parameter3 is NULL OR sd_xe_ash_nm.parameter3 = '0') THEN 0 ELSE 1 END) as p3valid, sd_xe_ash_nm.keh_evt_id, nvl(xc.class#, 0) as class_num, sd_xe_ash_nm.wait_class_id, nvl(xc.keh_id, 0) as keh_ecl_id, sd_xe_ash_nm.ash_cnt, sd_xe_ash_nm.tot_wts_diff,

453779 138168 132136

select a.name db_name, round((nvl(b.data_KB,0)nvl(c.temp_KB,0))/1024/1024,2) size_gb, round((nvl(b.data_KB,0)-nvl(d.data_free,0) nvl(e.temp_KB_used,0))/1024/1024,2) used_gb, round((nvl(d.data_free,0)nvl(c.temp_KB,0)- nvl(e.temp_KB_used,0))/1024/1024,2) free_gb, to_char(((nvl(b.data_KB,0)-nvl(d.data_free,0) nvl(e.temp_KB_used,0))/(nvl(b.data_KB,0)+ nvl(c.temp_KB,0)))100, '999') percent_used, to_char(((nvl(d.data_free,0)nvl(c.temp_KB,0)- nvl(e.temp_KB_used,0))/(nvl(b.data_KB,0) nvl(c.temp_KB,0)))100, '999') percent_free, f.num_seg, g.num_ts, h.num_us, to_char((nvl(b.data_KB,0)/(nvl(b.data_KB,0)+ nvl(c.temp_KB,0)))100,'999') percent_data, to_char((nvl(c.temp_KB,0)/(nvl(b.data_KB,0)+ nvl(c.temp_KB,0)))100,'999') percent_temp, round(to_char((f.tab_kb/(nvl(b.data_KB,0)+ nvl(c.temp_KB,0)))*100,'999'),0) percent_tab, round(to_char((f.

449213 154968 151184

select ts, round(size_mb,2) size_mb, round(free_mb,2) free_mb, round(decode(size_mb,0,'',(size_mb- free_mb)100/size_mb),0) per_used, autoextensible, round(total_size_mb,2) tot_size_mb, round(total_free_mb,2) tot_free_mb, round(decode(total_size_mb,0,'',(total_size_mb- total_free_mb)100/total_size_mb),0) per_tot_used, files, segments, extents, status, contents from (select a.tablespace_name ts, nvl(c.mb,0)nvl(e.mb,0) size_mb, decode(lower(a.contents),'temporary', nvl(e.mb,0)-nvl(f.used_mb,0),nvl(d.free_mb,0)) free_mb, nvl(c.files,0)nvl(e.files,0) files, nvl(b.seg,0) segments, nvl(b.ext,0) extents, decode(lower(a.contents),'temporary', decode(sign(nvl(e.tot_mb,0)-nvl(e.mb,0)),1,'YES','NO'), decode(sign(nvl(c.tot_mb,0)-nvl(c.mb,0)),1,'YES','NO'))

429595 139184 136072

SQL>

Please suggest how to go ahead.

Thanks & Regards

Bhupesh

[Former Member]

Hello Jens / Manoj,

Thanks for that. Checked the note suggested(690241). In our case, we have 4 CPUs in the DB server (and 8 in CI server).

Current value of the parameter shared_pool_size (2224M) is much more than the suggesed value(650M).

Please correct me if I am wrong and suggest further preventive actions to avoid the issue.

Regards

Bhupesh

[stefan_koehler]

Hello,

please execute the following SQL and post the result (just to get an idea what is happening in your database)::

shell> sqlplus / as sysdba
SQL> SELECT SQL_ID , SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM 
     FROM V$SQLAREA ORDER BY SHARABLE_MEM DESC;

Depending on your workload and parsed statements (maybe custom code with large SQLs??) the 2224MB shared pool should be sufficient.

Regards

Stefan

[jgleichmann]

Hi Bhupesh,

that´s only a benchmark value from sap, normally that´s sufficient. Please check which statement uses so much memory. If you don´t know how to do it (check stefans suggestion) than increase the value first and check if the error persists.

If it still occurs you must do a deeper analyze.

Regards,

Jens

[Former Member]

Hi Jens,

The values of shared_pool_size is 2224M. Also, the value of shared_pool_reserved_size is 111.2M.

Thanks & Regards

Bhupesh

[Former Member]

You may want to increase this parameter and check.

[jgleichmann]

Hi

please refer to note 690241

Increase this value to 2,75GB or 3 GB depending on the number of CPUs

check and give feedback

Regards,

Jens

[jgleichmann]

Hi Bhupesh,

the cause of this error can be insufficient Shared Pool Memory.

What is your value for 'SHARED_POOL_SIZE'?

Regards,

Jens