Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Playing with ORA-4030 error

with 3 comments

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.

  1. You have under-configured OS memory limitation.
  2. You have memory leak bug.
  3. 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?

Note: I had a serious misinterpretation on the following second test case and please kindly ignore this specific test case.

-- 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 0x20000001 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.

About these ads

Written by Dion Cho

July 27, 2009 at 6:18 am

Posted in Troubleshooting

Tagged with

3 Responses

Subscribe to comments with RSS.

  1. You gave clear explanation.
    The way of presented also great.

    Thankyou for sharing.

    Muruganantham Durairaj

    August 21, 2009 at 2:20 pm

  2. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 61 other followers

%d bloggers like this: