Playing with ORA-4030 error
ORA-4030 has such a long history that for now, even junior DBAs should have a good knowledge on it. But the reality is not like my expectation. That’s why I’m posting this article – to help them play with ORA-4030 and have an insight on it!
Look at this definition.
04030 "out of process memory when trying to allocate %s bytes (%s,%s)" // *Cause: Operating system process private memory has been exhausted // *Action:
It clearly says that this is OS process private memory problem! But I believe that this statement is 50% of truth.
As far as I know, there are 3 common reasons for this error.
- You have under-configured OS memory limitation.
- You have memory leak bug.
- You are allocating too many objects.
In the Unix system, the low limit of the process configuration can cause ORA-4030 error. The quick cure is to increase the limit – the most prefered value is UNLIMITED.
prompt> ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited
In the Windows 32 system, the limit of the private memory is around 1.5G. You cannot grow your process memory over than it regardless of your physical RAM size. For this reason, it’s somtimes recommended to use the Windows 64 system.
If the problem persists even with the higher OS configuration, you have 2 other reasons. You’re hitting Oracle bug(memory leak) and/or allocating too many objects.
The most intuitive and natural way for further investigation is the PGA heap dump. As far as I know, this is the easiest way!
Before introuducing how to use PGA heap dump, you should understand exactly when you hit ORA-4030 error. Let me show you simple examples.
1. I have following minimum PGA size configuration.
alter system set "_pga_max_size" = 15000000; alter system set pga_aggregate_target=50m;
2.I build very big PL/SQL block so that it exceeds the max PGA size. Would I hit ORA-4030 error?
-- case1. this does not cause 4030
set serveroutput on
spool temp.sql
begin
dbms_output.put_line('declare');
for idx in 1 .. 8000 loop
dbms_output.put_line(' v' || idx || ' varchar2(4000) := rpad(''x'',4000);');
end loop;
dbms_output.put_line('begin');
dbms_output.put_line('null;');
dbms_output.put_line('end;');
dbms_output.put_line('/');
end;
/
spool off
@temp
v91 varchar2(4000) := rpad('x',4000);
*
ERROR at line 92:
ORA-06550: line 1753, column 30:
PLS-00123: program too large (Diana nodes)
NAME VALUE
------------------------------ ----------------
session pga memory 2,022,996
session pga memory max 2,022,996
The answer seems NO and Oracle limits the program size below the maximum PGA size. Good. You would never write such an ugly codes anyway!
3.I build 1.2G byte of LOB local variable. Would I hit ORA-4030?
declare
v1 clob;
begin
for idx in 1 .. 1200000 loop
v1 := v1 || rpad('x', 1000, 'x');
end loop;
end;
/
NAME VALUE
---------------------------------------- ----------------
session pga memory 2,547,284
session pga memory max 5,627,476
PL/SQL procedure successfully completed.
Absolutely NO! Oracle gracefully handles the LOB data – well designed. And see the maximum memory usage. It never exceeds some specific size.
We can expect the same behavior with the sort operation. Sorting 2G of data would cause performance problem with 15m of PGA max size, but you never hit ORA-4030 error.
4. Then when do I hit ORA-4030? See following trick.
create or replace procedure proc_rec(depth number)
is
v1 varchar2(1000) := rpad('x',1000);
v2 varchar2(1000) := rpad('x',1000);
v3 varchar2(1000) := rpad('x',1000);
v4 varchar2(1000) := rpad('x',1000);
v5 varchar2(1000) := rpad('x',1000);
v6 varchar2(1000) := rpad('x',1000);
v7 varchar2(1000) := rpad('x',1000);
v8 varchar2(1000) := rpad('x',1000);
v9 varchar2(1000) := rpad('x',1000);
v10 varchar2(1000) := rpad('x',1000);
begin
if depth > 0 then
proc_rec(depth - 1);
end if;
end;
/
UKJA@ukja102> exec proc_rec(100);
NAME VALUE
------------------------------ ----------------
session pga memory 2,940,500
session pga memory max 2,940,500
Elapsed: 00:00:00.00
UKJA@ukja102> exec proc_rec(10000);
NAME VALUE
------------------------------ ----------------
session pga memory 111,140,436
session pga memory max 124,575,316
UKJA@ukja102> exec proc_rec(20000);
NAME VALUE
------------------------------ ----------------
session pga memory 220,323,412
session pga memory max 248,307,284
Do you see that memory usage goes far beyond the PGA max size? Many complex Oracle applications have a complex structure of function calls. If this structure meets unexpected Oracle bug or logic hole, you have a strong chance to hit ORA-4030 error.
5. PL/SQL collections also go beyond your configuration.
create or replace procedure proc_array(len number)
is
type vtable is table of varchar2(1000);
vt vtable := vtable();
begin
for idx in 1 .. len loop
vt.extend;
vt(idx) := rpad('x',1000,'x');
end loop;
end;
/
UKJA@ukja102> exec proc_array(10000);
NAME VALUE
------------------------------ ----------------
session pga memory 221,896,276
session pga memory max 248,307,284
UKJA@ukja102> exec proc_array(10000);
NAME VALUE
------------------------------ ----------------
session pga memory 220,192,340
session pga memory max 364,961,364
UKJA@ukja102> exec proc_array(1200000);
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu
call ,pl/sql vc2)
NAME VALUE
------------------------------ ----------------
session pga memory 219,668,052
session pga memory max 1,405,476,436
The memory usage hit the OS boundary(1.4G here on my Windows 32) – This is why ORA-4030 error is defined as “OS private process memory problem”. But the actual problem lies with the agressive memory allocation by my application.
6. OK, the play is over. Now what can I do? I would prefer PGA heap dump! I can dump PGA heap(including subheaps) automatically using following diagnostic event syntax.
-- This time alter session set events '4030 trace name heapdump level 0x20000001, lifetime 1';
Heapdump on level 0×20000001 is described here.
The next step is summarizing the raw trace file and making some readible reports.
UKJA@ukja102> @heap_analyze ukja10_ora_5728.trc HEAP_NAME HSZ -------------------- ---------- pga heap 1,362.7 koh-kghu call 1,022.8 top uga heap .2 session heap .2 top call heap .1 PLS non-lib hp .0 qmtmInit .0 Alloc environm .0 KSFQ heap .0 Alloc server h .0 koh-kghu sessi .0 callheap .0 12 rows selected. Elapsed: 00:00:00.09 HEAP_NAME CHUNK_TYPE CNT SZ HSZ HRATIO -------------------- --------------- -------- ---------- ---------- ------ Alloc environm freeable 3 .0 .0 80.9 Alloc environm recreate 1 .0 .0 14.6 Alloc environm perm 2 .0 .0 4.5 Alloc server h free 6 .0 .0 94.3 Alloc server h perm 2 .0 .0 3.1 Alloc server h freeable 2 .0 .0 2.6 KSFQ heap perm 2 .0 .0 100.0 PLS non-lib hp freeable 12 .0 .0 75.1 PLS non-lib hp free 6 .0 .0 15.9 PLS non-lib hp perm 2 .0 .0 9.0 callheap free 6 .0 .0 77.1 callheap perm 2 .0 .0 22.9 koh-kghu call freeable 65,415 1,022.8 1,022.8 100.0 koh-kghu sessi freeable 4 .0 .0 100.0 pga heap freeable 65,453 1,024.1 1,362.7 75.2 pga heap free 43,616 338.4 1,362.7 24.8 pga heap recreate 6 .0 1,362.7 .0 pga heap perm 28 .2 1,362.7 .0 qmtmInit freeable 12 .0 .0 69.1 qmtmInit free 8 .0 .0 30.9 session heap perm 2 .1 .2 36.8 session heap freeable 333 .1 .2 33.6 session heap free 14 .0 .2 23.0 session heap recreate 8 .0 .2 6.6 top call heap free 2 .1 .1 93.5 top call heap perm 2 .0 .1 .1 top call heap freeable 1 .0 .1 3.1 top call heap recreate 2 .0 .1 3.3 top uga heap recreate 1 .1 .2 33.3 top uga heap free 6 .1 .2 33.4 top uga heap freeable 1 .1 .2 33.3 31 rows selected. Elapsed: 00:00:00.15 HEAP_NAME OBJ_TYPE CNT SZ HSZ HRATIO -------------------- -------------------- -------- ---------- ---------- ------ Alloc environm 1 .0 .0 17.2 Alloc environm Alloc server h 3 .0 .0 78.4 Alloc environm perm 2 .0 .0 4.5 Alloc server h 8 .0 .0 96.9 Alloc server h perm 2 .0 .0 3.1 KSFQ heap perm 2 .0 .0 100.0 PLS non-lib hp PL/SQL STACK 2 .0 .0 69.3 PLS non-lib hp PLSQL Stack des 2 .0 .0 .2 PLS non-lib hp perm 2 .0 .0 9.0 PLS non-lib hp pl_lut_alloc 1 .0 .0 .4 PLS non-lib hp peihstdep 5 .0 .0 .7 PLS non-lib hp PEIDEF 1 .0 .0 4.2 PLS non-lib hp 6 .0 .0 15.9 PLS non-lib hp pl_iot_alloc 1 .0 .0 .4 callheap 6 .0 .0 77.1 callheap perm 2 .0 .0 22.9 koh-kghu call pl/sql vc2 65,414 1,022.8 1,022.8 100.0 <-- This is it! koh-kghu call pmucalm coll 1 .0 1,022.8 .0 koh-kghu sessi pl/sql vc2 1 .0 .0 39.9 koh-kghu sessi pliost struct 3 .0 .0 60.1 pga heap KJZT context 1 .0 1,362.7 .0 pga heap external name 1 .0 1,362.7 .0 pga heap KFIO PGA struct 1 .0 1,362.7 .0 pga heap KSFQ heap descr 1 .0 1,362.7 .0 pga heap PLS cca hp desc 1 .0 1,362.7 .0 pga heap KFK PGA 1 .0 1,362.7 .0 pga heap kews sqlstat st 1 .0 1,362.7 .0 pga heap koh-kghu call h 2 .0 1,362.7 .0 pga heap kpuinit env han 1 .0 1,362.7 .0 pga heap joxp heap 2 .0 1,362.7 .0 pga heap kjztprq struct 1 .0 1,362.7 .0 pga heap kopolal dvoid 5 .0 1,362.7 .0 pga heap KSFQ heap 1 .0 1,362.7 .0 pga heap Alloc environm 2 .0 1,362.7 .0 pga heap ldm context 13 .0 1,362.7 .0 pga heap qmtmInit 4 .0 1,362.7 .0 pga heap kgh stack 1 .0 1,362.7 .0 pga heap PLS non-lib hp 3 .0 1,362.7 .0 pga heap Fixed Uga 1 .0 1,362.7 .0 pga heap perm 28 .2 1,362.7 .0 pga heap kzsna:login nam 1 .0 1,362.7 .0 pga heap koh-kghu call 65,415 1,024.1 1,362.7 75.2 pga heap 43,616 338.4 1,362.7 24.8 qmtmInit qmushtCreate 3 .0 .0 44.9 qmtmInit 8 .0 .0 30.9 qmtmInit qmtmltAlloc 6 .0 .0 23.0 qmtmInit qmtmltCreate 3 .0 .0 1.2 session heap perm 2 .1 .2 36.8 session heap 14 .0 .2 23.0 session heap koklug hxctx in 1 .0 .2 .0 session heap koklug hlctx in 1 .0 .2 .0 session heap koddcal dvoid 1 .0 .2 .0 session heap system trigger 1 .0 .2 .0 session heap kxsFrame4kPage 5 .0 .2 12.8 session heap koh-kghu sessio 7 .0 .2 4.9 session heap koh-kghu sessi 6 .0 .2 4.6 session heap kxsc: kkspsc0 12 .0 .2 3.6 session heap kgsc ht segs 266 .0 .2 3.2 session heap PLS non-lib hp 2 .0 .2 2.6 session heap kzctxhugi1 1 .0 .2 2.6 session heap kpuinit env han 1 .0 .2 1.0 session heap kgiob 6 .0 .2 .7 session heap kokl lob id has 1 .0 .2 .6 session heap kxs-heap-p 1 .0 .2 .6 session heap kodpai image 1 .0 .2 .6 session heap kxs-krole 7 .0 .2 .4 session heap session languag 1 .0 .2 .3 session heap Session NCHAR l 1 .0 .2 .3 session heap PLS cca hp desc 2 .0 .2 .2 session heap kokl transactio 1 .0 .2 .2 session heap kokahin kgglk 1 .0 .2 .1 session heap kqlpWrntoStr:st 1 .0 .2 .1 session heap kwqidwh memory 2 .0 .2 .1 session heap kwqaalag 2 .0 .2 .1 session heap kgiobdtb 1 .0 .2 .1 session heap kwqb context me 2 .0 .2 .1 session heap kwqica hash tab 2 .0 .2 .1 session heap kwqmahal 2 .0 .2 .1 session heap kodmcon kodmc 1 .0 .2 .0 session heap kzsrcrdi 1 .0 .2 .0 session heap ksulu : ksulueo 1 .0 .2 .0 top call heap perm 2 .0 .1 .1 top call heap callheap 3 .0 .1 6.4 top call heap 2 .1 .1 93.5 top uga heap session heap 2 .1 .2 66.6 top uga heap 6 .1 .2 33.4 86 rows selected. Elapsed: 00:00:00.15 HEAP_NAME SUBHEAP CNT SZ HSZ HRATIO -------------------- -------------------- -------- ---------- ---------- ------ Alloc environm ds=04F665B8 2 .0 .0 63.7 Alloc environm 4 .0 .0 36.3 Alloc server h 10 .0 .0 100.0 KSFQ heap 2 .0 .0 100.0 PLS non-lib hp 20 .0 .0 100.0 callheap 8 .0 .0 100.0 koh-kghu call 65,415 1,022.8 1,022.8 100.0 koh-kghu sessi 4 .0 .0 100.0 pga heap ds=05003858 65,414 1,024.1 1,362.7 75.2 pga heap 43,683 338.6 1,362.7 24.8 pga heap ds=04F67B34 1 .0 1,362.7 .0 pga heap ds=04F8D9CC 2 .0 1,362.7 .0 pga heap ds=04FE3470 3 .0 1,362.7 .0 qmtmInit 20 .0 .0 100.0 session heap 356 .2 .2 98.7 session heap ds=07F7EAAC 1 .0 .2 1.3 top call heap ds=083B8E20 1 .0 .1 3.1 top call heap 6 .1 .1 96.9 top uga heap 7 .1 .2 66.7 top uga heap ds=05007600 1 .1 .2 33.3 20 rows selected. Elapsed: 00:00:00.12
(heap_analyze.sql is here)
It’s a long list, but do you see the culprit? – koh-kghu call pl/sql vc2
KOH means Kernel Object Heap and KGHU means Kernel Generic Service for Heap Management(U might be UGA or User object). So this means that this process is allocating too many objects of PL/SQL Varchar2.
(Seel metalink note 175982.1 for further Oracle naming conventions)
I should admit that the real-life ORA-4030 troubleshooting is far more difficult than my simple test case. You have no access to the source code and the object names are decryptic.
But being on the good start point is very important and I believe that above simple demonstration would help you to start troubeshooting ORA-4030 in more scientific way.
You gave clear explanation.
The way of presented also great.
Thankyou for sharing.
Muruganantham Durairaj
August 21, 2009 at 2:20 pm
Hi Dion,
You have written:
>>
2. I build very big PL/SQL block so that it exceeds the max PGA size. Would I hit ORA-4030 error?
The answer seems NO and Oracle limits the program size below the maximum PGA size. Good. You would never write such an ugly codes anyway!
<<
Is the code size related to PGA? Since the PL/SQL code is stored in the shared pool, the PLS-00123 error should have nothing to do with PGA size. Please clarify.
CJ
July 13, 2010 at 10:55 am
Hi, CJ.
You’ve got the point. It was my misinterpretation on the error.
Thanks for pointing it out.
Dion Cho
August 16, 2010 at 4:38 am