Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘ORA-4030

Rapid PGA size increase

with 8 comments

A couple of days ago, one of our customers experienced a rapid PGA increase and the system got rebounced by PMON. We suggested dumping the PGA heap while the problem reoccurs. But the problem is that the PGA increase is too rapid(upto 3.6G in less than 2 min), so it is a bit hard to get the PGA dump manually.

Fortunately, in situation like this, Oracle provides a way of getting automatic PGA heap dump.

1. First, let me set the 10261 diagnostic event to limit the size of the PGA heap. For instance, following command will limit the size of the PGA heap to 100000KB.

alter system set events '10261 trace name context forever, level 100000';

2. With the 10261 event set, the process would fail with ORA-600 [723] when the size of the PGA heap reaches 100000KB. As you already know, the 10261 event is used as a band-aid for the ORA-4030 error.

-- make big pga
declare
 type varchar2_array is table of varchar2(32767) index by pls_integer;
 vc  varchar2_array;
 v  varchar2(32767);
begin
 for idx in 1 .. 10000 loop
 v := rpad('x',32767,'x');
 vc(idx) := v;
 end loop;
end;
/

ERROR at line 1:
ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [],
[], [], []

3. Now, let me set the 600 diagnostic event to get the heap dump when the process hits ORA-600 error. With the combination of 10261 and 600 event, the process would record the PGA heap dump automatically when the PGA heap size reaches the limit.

alter system set events '600 trace name heapdump level 0x20000001';

4. When the PGA heap size reaches the limit(100000KB in this case) again, I would have the complete PGA heap dump including all the recursive subheaps by virtue of the dump level 0x20000001.

DDE: Problem Key 'ORA 600 [723]' was flood controlled (0x2) (incident: 44800)
ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [], [], [], []
****** ERROR: PGA size limit exceeded: 102450812 > 102400000 *****
******************************************************
HEAP DUMP heap name="pga heap"  desc=11AFB098
 extent sz=0x206c alt=92 het=32767 rec=0 flg=2 opc=1
 parent=00000000 owner=00000000 nex=00000000 xsz=0xfff8 heap=00000000
 fl2=0x60, nex=00000000
EXTENT 0 addr=39150008
  Chunk 39150010 sz=    24528    free      "               "
  Chunk 39155fe0 sz=    40992    freeable  "koh-kghu call  "  ds=0D4D9A60
EXTENT 1 addr=39140008
  Chunk 39140010 sz=    24528    free      "               "
  Chunk 39145fe0 sz=    40992    freeable  "koh-kghu call  "  ds=0D4D9A60
...

5. The last step is to analyze the heap dump. For instance, I have my own library to analyze the heap dump.

select * from table(tpack.heap_file_report('C:\oracle\diag\rdbms\ukja1106\ukja1106\trace\ukja1106_ora_3640.trc'));

TYPE     HEAP_NAME        ITEM             ITEM_COUNT  ITEM_SIZE  HEAP_SIZE      RATIO
-------- ---------------- ---------------- ---------- ---------- ---------- ----------
HEAP     pga heap                                   0      97.14      97.14        100
HEAP     top call heap                              0        .18        .18        100
HEAP     top uga heap                               0        .31        .31        100
CHUNK    pga heap         free                   1554       36.2       97.1       37.3
CHUNK    pga heap         recreate                  9          0       97.1          0
CHUNK    pga heap         perm                     14          0       97.1          0
CHUNK    pga heap         freeable               1597       60.7       97.1       62.5
CHUNK    top uga heap     recreate                  1          0         .3       19.9
CHUNK    top uga heap     free                      5          0         .3          0
CHUNK    top uga heap     freeable                  4         .2         .3       79.9
CHUNK    top call heap    free                      3         .1         .1       65.5
CHUNK    top call heap    recreate                  2          0         .1          1
CHUNK    top call heap    freeable                  1          0         .1       33.3
CHUNK    top call heap    perm                      1          0         .1          0
OBJECT   pga heap         kews sqlstat st           1          0       97.1          0
OBJECT   pga heap         pesom.c:Proces            3          0       97.1          0
...

6. It is also possible to write the script to monitor the V$SESSTAT view(session pga memory) and execute the heap dump when the value hits some specific threshold. For instance, I have my own library which is used like following.

col report_id new_value report_id

select tpack_server.create_report('Heap Dump') as report_id from dual;

exec tpack_server.add_parameter('&report_id', 'dump_level', '0x20000001');
exec tpack_server.add_parameter('&report_id', 'get_whole_contents', 0);

exec tpack_server.add_condition('&report_id', 'STAT', 'session pga memory', '>100000000', 'SUM');

exec tpack_server.register_report('&report_id');

-- start server
exec tpack_server.start_server;

When the PGA heap size hits the limit(100000000B), it would execute the predefined procedure which executes the heap dump.

Fri Jun 11 06:19:10 GMT+00:00 2010 : Session 142 got! sum=659645392, name = session pga memory
...
Fri Jun 11 06:27:50 GMT+00:00 2010 : executing report 1:142:1973827792 for session 142
Fri Jun 11 06:27:55 GMT+00:00 2010 : executing report = begin tpack.heap_dump(  dump_level=>'0x20000001', get_whole_contents=>0,  session_id => 142); end;
...

Also note that the combination of 10261 and 600 event is just a temporary solution and the most important thing is to analyze the heap dump very carefully to make the rapid PGA increase never happen again!

Written by Dion Cho

June 14, 2010 at 2:10 am

Posted in Troubleshooting

Tagged with , , ,

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.

Written by Dion Cho

July 27, 2009 at 6:18 am

Posted in Troubleshooting

Tagged with

Troubleshooting PGA leak with PGA heap dump

with 12 comments

Jonathan Lewis shared interesting and practical experience on PGA memory leak here. Here he mentions on the memory leaks by forall batch insert bug.

This is actually a very interesting case where troubleshooting using PGA heap dump seems to be one of the feasible ways to analyze the problem(as Tanel responded to Jonathan’s request). I would just like to show and discuss how I would investigate this problem and find the solution using PGA heap dump.

This is the simple simulation made by Jonathan Lewis which makes memory leak on PGA.

define m_string_length = 20

drop table t1 purge;
create table t1(v1 varchar2( &m_string_length ));

create or replace procedure proc1 (
	i_rowcount	in number	default 1000000,
	i_bulk_pause	in number	default 0,
	i_forall_pause	in number	default 0,
	i_free_pause	in number	default 0
)
as
	type w_type is table of varchar2( &m_string_length );
	w_list 		w_type := w_type();
	w_free		w_type := w_type();
begin
	for i in 1..i_rowcount loop
		w_list.extend;
		w_list(i) := rpad('x', &m_string_length );
	end loop;

	dbms_lock.sleep(i_bulk_pause);

	forall i in 1..w_list.count
	  insert into t1 values(w_list(i));

	dbms_lock.sleep(i_forall_pause);
	commit;
	w_list := w_free;
	dbms_session.free_unused_user_memory;

	dbms_lock.sleep(i_free_pause);
end;
/



Let me monitor the session memory usage while making multiple calls on this procedure.

UKJA@ukja102> exec proc1;

PL/SQL procedure successfully completed.

SYS@ukja10> select
  2     name, value
  3  from
  4     v$sesstat ss,
  5     v$statname sn
  6  where
  7     sn.name like '%ga memory%'
  8  and        ss.statistic# = sn.statistic#
  9  and        ss.sid = 149
 10  ;

NAME                                      VALUE
------------------------------ ----------------
session uga memory                  498,942,840
session uga memory max              500,053,908
session pga memory                  599,907,924
session pga memory max              675,602,004

UKJA@ukja102> exec proc1;

PL/SQL procedure successfully completed.

NAME                                      VALUE
------------------------------ ----------------
session uga memory                  695,429,288
session uga memory max              695,429,288
session pga memory                  904,191,572
session pga memory max              904,257,108

UKJA@ukja102> exec proc1;

PL/SQL procedure successfully completed.

NAME                                      VALUE
------------------------------ ----------------
session uga memory                  840,565,088
session uga memory max              840,565,088
session pga memory                1,077,861,972
session pga memory max            1,077,861,972



Very serious memory leak! Isn’t it? 1) Oracle does not release memory even after the call has completed and 2) Oracle does not 100% reuse the memory.

The problem is that it’s almost impossible to identify on which objects we’re losing memory. As far as I know, Oracle does not expose the PGA memory structure by v$ views nor x$views. (Jonathan Lewis pointed me to the X$KSMPP view)

So what else can we do? PGA heap dump is the only thing left to us.

oradebug setospid 6760
oradebug dump heapdump 1



The heap dump file is quite easy to interpret, but it is sometimes too long to interpret with our eyes. So I built heap_analyze.sql to retrieve summarized report from raw dump file. Here is the result for the PGA heap dump.

UKJA@ukja102> @heap_analyze heap_dump_1.trc
UKJA@ukja102> set echo off

     TSIZE
----------
 598488868

Elapsed: 00:00:00.57

ATYPE                         CSIZE  HEAP_SIZE  RATIO
------------------------ ---------- ---------- ------
freeable                  498650144  598488868  83.32
perm                         181928  598488868    .03
recreate                      87180  598488868    .01
free                       99569616  598488868  16.64

Elapsed: 00:00:00.75

CTYPE                               CSIZE  HEAP_SIZE  RATIO
------------------------------ ---------- ---------- ------
Alloc environm                       4144  598488868    .00
Fixed Uga                           20572  598488868    .00
KFIO PGA struct                        72  598488868    .00
KFK PGA                               260  598488868    .00
KJZT context                           60  598488868    .00
KSFQ heap                            3928  598488868    .00
KSFQ heap descr                        92  598488868    .00
PLS cca hp desc                       212  598488868    .00
PLS non-lib hp                      18560  598488868    .00
callheap                             2144  598488868    .00
external name                          24  598488868    .00
joxp heap                            2000  598488868    .00
kews sqlstat st                      1292  598488868    .00
kgh stack                           17012  598488868    .00
kjztprq struct                       2068  598488868    .00
koh-kghu call h                      1328  598488868    .00
kopolal dvoid                        2524  598488868    .00
kpuinit env han                      1584  598488868    .00
kzsna:login nam                        24  598488868    .00
ldm context                         12712  598488868    .00
perm                               181928  598488868    .03
qmtmInit                            13980  598488868    .00
session heap                    498632732  598488868  83.32
free                             99569616  598488868  16.64

24 rows selected.

Elapsed: 00:00:00.65

DS                        CSIZE  HEAP_SIZE  RATIO
-------------------- ---------- ---------- ------
083BD9CC                  10320  598488868    .00
08563470                  12436  598488868    .00
085A7600              498567256  598488868  83.30
free                   99898856  598488868  16.69

Elapsed: 00:00:00.68



Hm… nothing special except the fact that the subheap(085A7600) occupies 83.3% of the PGA heap. So I do subheap dump for that heap address.

oradebug dump heapdump_addr 1 0xa067600

UKJA@ukja102> @heap_analyze heap_subdump_1.trc
UKJA@ukja102> set echo off

     TSIZE
----------
 498561776

Elapsed: 00:00:00.40

ATYPE                         CSIZE  HEAP_SIZE  RATIO
------------------------ ---------- ---------- ------
freeable                  488817960  498561776  98.05
perm                          54896  498561776    .01
recreate                      14852  498561776    .00
free                        9674068  498561776   1.94

Elapsed: 00:00:00.56

CTYPE                               CSIZE  HEAP_SIZE  RATIO
------------------------------ ---------- ---------- ------
PLS cca hp desc                       400  498561776    .00
PLS non-lib hp                  488740608  498561776  98.03
Session NCHAR l                       552  498561776    .00
kgict                                  40  498561776    .00
kgicttab                               44  498561776    .00
kgicu                                  92  498561776    .00
kgiob                                1928  498561776    .00
kgiobdtb                              192  498561776    .00
kgsc ht segs                         5720  498561776    .00
koddcal dvoid                          24  498561776    .00
kodmcon kodmc                          64  498561776    .00
kodpai image                         1036  498561776    .00
koh-kghu sessi                      15888  498561776    .00
koh-kghu sessio                     14252  498561776    .00
kokahin kgglk                         140  498561776    .00
kokl lob id has                      1036  498561776    .00
kokl transactio                       268  498561776    .00
koklug hlctx in                        24  498561776    .00
koklug hxctx in                        24  498561776    .00
kpuinit env han                      1584  498561776    .00
kqlpWrntoStr:st                       112  498561776    .00
ksulu : ksulueo                        40  498561776    .00
kwqaalag                               92  498561776    .00
kwqb context me                        92  498561776    .00
kwqica hash tab                        92  498561776    .00
kwqidwh memory                         92  498561776    .00
kwqmahal                               92  498561776    .00
kxs-heap-d                           1036  498561776    .00
kxs-heap-p                           4148  498561776    .00
kxs-krole                             780  498561776    .00
kxsFrame4kPage                      28840  498561776    .01
kxsc: kkspbd0                         968  498561776    .00
kxsc: kkspsc0                        7756  498561776    .00
kzctxhugi1                           4108  498561776    .00
kzsrcrdi                               60  498561776    .00
perm                                54896  498561776    .01
session languag                       552  498561776    .00
system trigger                         36  498561776    .00
free                              9674068  498561776   1.94

39 rows selected.

Elapsed: 00:00:00.51

DS                        CSIZE  HEAP_SIZE  RATIO
-------------------- ---------- ---------- ------
08B3608C                   2076  498561776    .00
08B3EAAC              488738512  498561776  98.03
08B578F4                   2080  498561776    .00
08B5A114                   2080  498561776    .00
0FD400B0                   2080  498561776    .00
free                    9814948  498561776   1.97

6 rows selected.

Elapsed: 00:00:00.54



Well, again, another subheap(08B3EAAC) does occupy 98% of the space.

oradebug dump heapdump_addr 1 0x0A08EAAC

UKJA@ukja102> @heap_analyze heap_subdump_2.trc
UKJA@ukja102> set echo off

     TSIZE
----------
 488662552

Elapsed: 00:00:02.50

ATYPE                         CSIZE  HEAP_SIZE  RATIO
------------------------ ---------- ---------- ------
freeable                    4750540  488662552    .97
perm                            144  488662552    .00
free                      483911868  488662552  99.03

Elapsed: 00:00:03.37

CTYPE                               CSIZE  HEAP_SIZE  RATIO
------------------------------ ---------- ---------- ------
DPAGE                             4750096  488662552    .97
peihstdep                             260  488662552    .00
perm                                  144  488662552    .00
pl_iot_alloc                           92  488662552    .00
pl_lut_alloc                           92  488662552    .00
free                            483911868  488662552  99.03

6 rows selected.

Elapsed: 00:00:03.20

no rows selected

Elapsed: 00:00:02.42



Okay, now I see the problem. The deepest subheap occupies 466M(488662552) but, the 99% is FREE! Oracle does not release the free space even after the call has completed and even denies to reuse it 100%.

This certainly looks like a bug and I search metalink using these keywords.

metalink search

And this links me to the bug 5866410 which is described as the free chunks problem with FORALL insertion.

Bulk insert in PLSQL can consume a large amount of PGA memory which can lead to ORA-4030 errors.

A heapdump will show lot of free memory in the free lists which is not used but instead fresh allocations are made.

Well, above process might seem natural and easy for who read this post, but following things make the process somewhat difficult to follow

  • The heap dump file is sometimes quite long and hard to analyze without proper analysis tool.
  • Heap has hierarchical structure as shown above. It sometimes is composed of multiple subheaps and subheap itself could be composed of multiple subheapds.
  • The object names are sometimes hard to decode.

But I believe that above test case proves the usefulness of the PGA heap dump analysis and it would turn out to be very successful under certain circumstances.

Written by Dion Cho

June 9, 2009 at 5:35 am

Follow

Get every new post delivered to your Inbox.

Join 59 other followers