힙 덤프 파일

힙 덤프 파일 얻기

SQL> alter session set events 'immediate trace name heapdump level <level>';

/*
<level>
1 : PGA
2 : SGA
3 : UGA
8 : Current Call
16 : User Call
32 : Large Pool
*/

서브 힙 덤프

/*
<Oracle 10g R2 이후>
0x10000000 = including 5 largest subheap (최상위 힙과 그 아래 단계의 서브힙 중 크기가 가장 큰 5개의 서브힙을 포함한 정보를 Trace)
0x20000000 = including 5 largest subheap recursively (위와 같으나 재귀적임)
*/

SQL> alter session set events 'immediate trace name heapdump level 0x20000001';
-- PGA 힙을 5 개의 가장 큰 서브힙과 그 아래 단계의 서브힙까지 정보를 기록

힙 덤프 생성시 주의점

> SGA 힙 덤프의 경우 X$KSMSP 와 같이 래치 경합 발생 가능성 있으 므로 운영중 생성 금지

힙 덤프 활용 예

• 4030 에러가 발생하면 힙 덤프 수행하기

04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"
// *Cause:  Operating system process private memory was exhausted.
// *Action:
-- ORA-4030 : 서버 프로세스가 OS 에서 허용된 이상의 메모리를 요청 할때 발생

SQL> alter session set events '4030 trace name heapdump level 0x20000001';
-- 4030 에러가 발생하면 PGA의  최상위 힙과 크기 순으로 5개의 서브힙을 그 아래 단계의 서브힙들까지 포함해서 트레이스 파일에 기록해라

CREATE OR REPLACE PROCEDURE PROC_ARRAY(LEN NUMBER)
IS
  TYPE VTABLE IS TABLE OF VARCHAR2(30000);
  VT VTABLE := VTABLE();
BEGIN
  FOR IDX IN 1 .. LEN LOOP
    VT.EXTEND;
    VT(IDX) := RPAD('X', 30000, 'X');
  END LOOP;
END;
/

SQL> EXEC PROC_ARRAY(2000000);
BEGIN PROC_ARRAY(2000000); END;

*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 32712 bytes (koh-kghu
call ,pl/sql vc2)

/*
Thu Mar 15 02:49:50 2012
Errors in file /home/oracle/diag/rdbms/milk/MILK/trace/MILK_ora_3127.trc  (incident=25353):
ORA-04030: out of process memory when trying to allocate 32712 bytes (koh-kghu call ,pl/sql vc2)
Incident details in: /home/oracle/diag/rdbms/milk/MILK/incident/incdir_25353/MILK_ora_3127_i25353.trc
*/

/*
Dump continued from file: /home/oracle/diag/rdbms/milk/MILK/trace/MILK_ora_3127.trc
ORA-04030: out of process memory when trying to allocate 32712 bytes (koh-kghu call ,pl/sql vc2)

========= Dump for incident 25353 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------

*** 2012-03-15 02:53:17.836
100% 4065 MB, 130293 chunks: "pl/sql vc2                "  PL/SQL
         koh-kghu call   ds=0x2b874bcbb588  dsprt=0xa99d240
 0%  200 KB,  22 chunks: "permanent memory          "
         pga heap        ds=0xa99d240  dsprt=(nil)
 0%  182 KB,   3 chunks: "free memory               "
         callheap        ds=0xa9a1940  dsprt=0xa9a2840
 0%  170 KB,  42 chunks: "kxsFrame4kPage            "
         session heap    ds=0x2b874bc85ed8  dsprt=0xa9a2a60
 0%  157 KB,  50 chunks: "free memory               "
         session heap    ds=0x2b874bc85ed8  dsprt=0xa9a2a60
 0%  136 KB,   3 chunks: "free memory               "
         top call heap   ds=0xa9a2840  dsprt=(nil)
 0%   94 KB,  16 chunks: "permanent memory          "
         session heap    ds=0x2b874bc85ed8  dsprt=0xa9a2a60
 0%   64 KB,   3 chunks: "free memory               "
         top uga heap    ds=0xa9a2a60  dsprt=(nil)
 0%   56 KB,   6 chunks: "free memory               "
         pga heap        ds=0xa99d240  dsprt=(nil)
 0%   46 KB,  18 chunks: "mark                      "
         Alloc environm  ds=0x2b874bcae8b0  dsprt=0x2b874bc85ed8

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------

*** 2012-03-15 02:53:18.407
******************************************************
PRIVATE HEAP SUMMARY DUMP
4071 MB total:
  4071 MB commented, 201 KB permanent
   257 KB free (0 KB in empty extents),
    4070 MB,   1 heap:    "koh-kghu call  "            10 KB free held
------------------------------------------------------
Summary of subheaps at depth 1
4066 MB total:
  4066 MB commented, 111 KB permanent
   347 KB free (181 KB in empty extents),
    4065 MB, 130293 chunks:  "pl/sql vc2                "
*/