by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

힙 메모리 분석 기초




힙 메모리 구조

오라클은

항상 힙(Heap) 구조로 메모리를 관리

SGA 힙 덤프 데모
******************************************************
HEAP DUMP heap name="sga heap"  desc=700000000000190
 extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=0
 parent=0 owner=0 nex=0 xsz=0x0 heap=0
 fl2=0x60, nex=0
 ds for latch 1: 0x700000000052160 0x7000000000539b8 0x700000000055210 0x700000000056a68
 ds for latch 2: 0x70000000005ba28 0x70000000005d280 0x70000000005ead8 0x700000000060330
 ds for latch 3: 0x7000000000652f0 0x700000000066b48 0x7000000000683a0 0x700000000069bf8
 reserved granule count 0 (granule size 16777216)
******************************************************
HEAP DUMP heap name="sga heap(1,0)"  desc=700000000052160
 extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=0
 parent=0 owner=0 nex=0 xsz=0x1000000 heap=0
 fl2=0x20, nex=0
 latch set 1 of 3
 durations enabled for this heap
 reserved granules for root 0 (granule size 16777216)
EXTENT 0 addr=700000191000000
  Chunk  700000191000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk  700000191000088 sz=   839496  R-free      "               "
  Chunk  7000001910ccfd0 sz=       48  R-freeable  "reserved stoppe"
  Chunk  7000001910cd000 sz= 15937536    perm      "perm           "  alo=5773792
EXTENT 1 addr=7000001b3000000
  Chunk  7000001b3000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk  7000001b3000088 sz=   839496  R-free      "               "
  Chunk  7000001b30ccfd0 sz=       48  R-freeable  "reserved stoppe"
  Chunk  7000001b30cd000 sz= 15934744    perm      "perm           "  alo=15934744
  Chunk  7000001b3fff518 sz=     2792    free      "               "
EXTENT 2 addr=7000001b8000000
  Chunk  7000001b8000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk  7000001b8000088 sz=   839496  R-free      "               "
  Chunk  7000001b80ccfd0 sz=       48  R-freeable  "reserved stoppe"
  Chunk  7000001b80cd000 sz= 15927856    perm      "perm           "  alo=15927856
...
> SGA 는 여러 개의 서브힙으로 구성됨 - "sga heap(1,0)" ... "sga heap (3,3)"
> 하나의 서브힙에는 여러 개의 익스텐트(Extent 0, Extent 1, ...)가 존재하고, 각 익스텐트는 여러개의 청크(Chunk)가 존재
> 각 청크는 perm, free, recreate 등의 메모리 상태를 가지며, "fixed allocation", "reserved stoppe", "perm" 과 같은 메모리 유형을 가짐
SGA 힙 덤프 데모 (서브힙 확인)
HEAP DUMP heap name="sga heap(1,0)"  desc=700000000052160
HEAP DUMP heap name="sga heap(1,1)"  desc=7000000000539b8
HEAP DUMP heap name="sga heap(1,2)"  desc=700000000055210
HEAP DUMP heap name="sga heap(1,3)"  desc=700000000056a68
HEAP DUMP heap name="sga heap(2,0)"  desc=70000000005ba28
HEAP DUMP heap name="sga heap(2,1)"  desc=70000000005d280
HEAP DUMP heap name="sga heap(2,2)"  desc=70000000005ead8
HEAP DUMP heap name="sga heap(2,3)"  desc=700000000060330
HEAP DUMP heap name="sga heap(3,0)"  desc=7000000000652f0
HEAP DUMP heap name="sga heap(3,1)"  desc=700000000066b48
HEAP DUMP heap name="sga heap(3,2)"  desc=7000000000683a0
HEAP DUMP heap name="sga heap(3,3)"  desc=700000000069bf8

동적 성능 뷰들

V$SYSSTAT - 인스턴스 시작 이후에 수집된 인스턴스 레벨의 통계 정보 누적치
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%memory%';

/*
NAME                                                             VALUE
---------------------------------------------------------------- -----
session uga memory                                               7190041485288 
session uga memory max                                           8386087328 
session pga memory                                               3712816832 
session pga memory max                                           3983169888 
redo k-bytes read (memory)                                           0 
redo k-bytes read (memory) by LNS                                    0 
workarea memory allocated                                            0 
sorts (memory)                                                   303825 
*/
V$SESSTAT - 세션 시작 이후에 수집된 세션 레벨의 통계 정보 누적치
DECLARE
  TYPE  VARCHAR2_ARRAY IS TABLE OF VARCHAR2(30000) INDEX BY PLS_INTEGER;
  VA    VARCHAR2_ARRAY;
  V_PGA NUMBER;
BEGIN
  FOR IDX IN 1 .. 10000 LOOP
    -- ALLOCATE MEMORY
    VA(IDX) := RPAD('X', 30000);
    
    IF MOD(IDX, 1000) = 0 THEN
      SELECT S.VALUE INTO V_PGA
        FROM V$SESSTAT S, V$STATNAME N
       WHERE N.NAME = 'session pga memory'
         AND N.STATISTIC# = S.STATISTIC#
         AND S.SID = USERENV('SID');
         
      DBMS_OUTPUT.PUT_LINE('CURRENT PGA = ' || TRUNC(V_PGA/1024/1024) || 'MB');
    END IF;
  END LOOP;
END;
/
         
/*
CURRENT PGA = 41MB
CURRENT PGA = 72MB
CURRENT PGA = 103MB
CURRENT PGA = 134MB
CURRENT PGA = 166MB
CURRENT PGA = 197MB
CURRENT PGA = 228MB
CURRENT PGA = 259MB
CURRENT PGA = 291MB
CURRENT PGA = 322MB
*/ 

-- 위 데모와 같이 V$SESSTAT 뷰를 통해서 PGA 크기 변화 추적 가능.
-- 하지만 변화가 어떤 오브젝트에 왜 생겼는지는 알 수 없으며, 알기 위해서는 HEAP DUMP
V$SGASTAT
SELECT POOL, ROUND(SUM(BYTES)/1024/1024) AS MB FROM V$SGASTAT GROUP BY POOL ORDER BY SUM(BYTES) DESC;
/*
POOL         MB
------------ --
shared pool  284 
             157 
large pool    4 
java pool     4 
streams pool  4 
*/

SELECT * FROM V$SGASTAT ORDER BY POOL ASC, BYTES DESC;
/*
POOL         NAME                       BYTES
------------ -------------------------- -----
java pool    free memory                4194304 
large pool   free memory                3702784 
large pool   PX msg pool                491520 
shared pool  free memory                122057000 
shared pool  SQLA                       41663536 
shared pool  KGLS                       11681736 
shared pool  CCUR                       7804392 
shared pool  PLMCD                      7744088 
shared pool  row cache                  7584808 
shared pool  KGLSG                      5264904 
shared pool  KGLHD                      5097048 
shared pool  KGLH0                      5087648 
shared pool  PCUR                       4708568 
shared pool  kglsim hash table bkts     4194304 
shared pool  KCB Table Scan Buffer      3981120 
...
*/

-- 특정 풀에서 메모리 크기 문제가 발생 했을 때, 어떤 종류의 오브젝트가 원인인지 분석 가능 [shared pool / large pool / java pool / streams pool]
-- 오라클은 shared pool latch 경합 문제 해소를 위해 shared pool 을  가능한 여러 개의 서브 풀로 나눠서 관리 하나 V$SGASTAT 에는 서브 풀 정보가 없음

-- 서브 풀 수 : _KGHDSIDX_COUNT 파라미터 (CPU 4개 이상, shared pool 250 MB 이상 일때 나뉘어짐)
SELECT A.KSPPINM, B.KSPPSTVL 
  FROM X$KSPPI A, X$KSPPSV B
 WHERE A.INDX=B.INDX
   AND A.KSPPINM = '_kghdsidx_count';
/*
KSPPINM         KSPPSTVL
--------------- --------
_kghdsidx_count 3

-- 서브 풀을 관찰하기 위해서 베이스 X$KSMSS 테이블 활용
-- 공유 풀 > 힙 > 서브 힙 > 오브젝트 와 같은 계층 관계를 관찰하기 위해서  X$KSMSP 테이블 혹은 SGA 힙 덤프 이용
V$SHARED_POOL_RESERVED

-- 공유 풀(Shared Pool)은 ORA=4031 에러와 같은 치명적인 에러를 예방하기 위해 예비 풀(Reserved Pool)을 보유

SELECT A.KSPPINM, B.KSPPSTVL 
  FROM X$KSPPI A, X$KSPPSV B
 WHERE A.INDX=B.INDX
   AND A.KSPPINM LIKE '%shared_pool_reserved%';
/*
KSPPINM                            KSPPSTVL
--------------------------------   -----------------
shared_pool_reserved_size          15518924
_shared_pool_reserved_pct          5
_shared_pool_reserved_min_alloc    4400
*/

-- 전체 공유 풀 크기의 5 퍼센트(_shared_pool_reserved_pct)를 예비 풀로 사용 하며, 그 크기(shared_pool_reserved_size)는 15518924 바이트 임.
-- 공유 풀에서 필요한 크기의 여유 공간을 찾지 못하고, 그 크기가 4400 바이트(_shared_pool_reserved_min_alloc) 이상이면 예비 풀 사용

-- ORA-4031: unable to allocate 4200 bytes of shared memory ("shared pool", "unknown object", "sga heap", "state objects") 에러가 발생 한 경우, _shared_pool_reserved_min_alloc 파라미터를 4200 이하로 낮추면 해결 됨 (예비 풀 적용)
-- 위와 같이 _shared_pool_reserved_min_alloc 를 지나치게 작게 설정 할 경우, 예비 풀 자체가 단편화 됨, 근본적인 해결을 위해서 메모리의 재사용성을 높여야 함 (Literal SQL 대신 Bind SQL 활용 등)

EXEC PRINT_TABLE('SELECT * FROM V$SHARED_POOL_RESERVED');
/*
FREE_SPACE                    : 10882080
AVG_FREE_SIZE                 : 197856
FREE_COUNT                    : 52
MAX_FREE_SIZE                 : 217008
USED_SPACE                    : 12127872
AVG_USED_SIZE                 : 220506.763636363636363636363636363636364
USED_COUNT                    : 3
MAX_USED_SIZE                 : 4172016
REQUESTS                      : 0
REQUEST_MISSES                : 0
LAST_MISS_SIZE                : 0
MAX_MISS_SIZE                 : 0
REQUEST_FAILURES              : 0 <-- ORA-4031 발생 시 예비 풀 상황 추적 가능
LAST_FAILURE_SIZE             : 0 <-- ORA-4031 발생 시 예비 풀 상황 추적 가능
ABORTED_REQUEST_THRESHOLD     : 2147483647
ABORTED_REQUESTS              : 0
LAST_ABORTED_SIZE             : 0
*/

X$ 테이블

X$KSMSS - shared pool

-- 특정 서브 풀만 크기가 커지면서 ORA-4031 에러 발생 가능 : 서브 풀 불균형
SELECT KSMCHIDX "SUBPOOL",
       SUM(KSMCHSIZ) BYTES
  FROM SYS.X$KSMSP
 GROUP BY KSMCHIDX;
/*
   SUBPOOL      BYTES
---------- ----------
         3  889187784
         2  973073424
         1  922742040
*/

WITH X AS (
  SELECT KSMDSIDX AS POOLIDX,
         'shared pool' AS POOL,
         KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
         SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
         SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
    FROM SYS.X$KSMSS
   WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
     , TRUNC(BYTES/1024/1024,2) AS MB
     , TRUNC(100*BYTES/SP_BYTES,2) AS PCT
  FROM X
 ORDER BY POOLIDX, PCT DESC;
/*
   POOLIDX POOL        NAME                               MB        PCT
---------- ----------- -------------------------- ---------- ----------
         1 shared pool SQLA                           510.61      58.02
         1 shared pool CCUR                            97.49      11.07
         1 shared pool free memory                      91.9      10.44
         1 shared pool PCUR                            46.22       5.25
         1 shared pool KGLHD                           20.59       2.33
         1 shared pool KQR L PO                        12.33        1.4
         1 shared pool Cursor Stats                    11.45        1.3
         1 shared pool KGLH0                            9.56       1.08
         1 shared pool KGLS                             6.92        .78
         1 shared pool ASH buffers                       6.5        .73
         1 shared pool private strands                  5.19        .59
...
         2 shared pool SQLA                           504.24      54.33
         2 shared pool free memory                    108.36      11.67
         2 shared pool CCUR                            99.85      10.76
         2 shared pool PCUR                            43.79       4.71
         2 shared pool KGLHD                           19.92       2.14
         2 shared pool FileOpenBlock                   15.19       1.63
         2 shared pool KQR M PO                        11.91       1.28
...
         3 shared pool SQLA                           505.32      59.58
         3 shared pool CCUR                           101.99      12.02
         3 shared pool free memory                     67.14       7.91
         3 shared pool PCUR                            46.12       5.43
         3 shared pool KGLHD                           20.29       2.39
*/
X$KSMLS - large pool
WITH X AS (
  SELECT KSMDSIDX AS POOLIDX,
         'large pool' AS POOL,
         KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
         SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
         SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
    FROM SYS.X$KSMLS
   WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
     , TRUNC(BYTES/1024/1024,2) AS MB
     , TRUNC(100*BYTES/SP_BYTES,2) AS PCT
  FROM X
 ORDER BY POOLIDX, PCT DESC;
/*
POOLIDX POOL       NAME                       MB PCT
------- ---------- -------------------------- -- ---
      1 large pool free memory                63.53 99.26 
      1 large pool PX msg pool                0.46 0.73 
*/
X$KSMLS - java pool
WITH X AS (
  SELECT KSMDSIDX AS POOLIDX,
         'java pool' AS POOL,
         KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
         SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
         SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
    FROM SYS.X$KSMJS
   WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
     , TRUNC(BYTES/1024/1024,2) AS MB
     , TRUNC(100*BYTES/SP_BYTES,2) AS PCT
  FROM X
 ORDER BY POOLIDX, PCT DESC;
/*
POOLIDX POOL      NAME                       MB PCT
------- --------- -------------------------- -- ---
      0 java pool free memory                64 100 
*/
X$KSMLS - streams pool
WITH X AS (
  SELECT KSMDSIDX AS POOLIDX,
         'streams pool' AS POOL,
         KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
         SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
         SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
    FROM SYS.X$KSMSTRS
   WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
     , TRUNC(BYTES/1024/1024,2) AS MB
     , TRUNC(100*BYTES/SP_BYTES,2) AS PCT
  FROM X
 ORDER BY POOLIDX, PCT DESC;
/*
POOLIDX POOL         NAME                       MB PCT
------- ------------ -------------------------- -- ---
      0 streams pool free memory                64 100  
*/
X$KSMSP - SGA 청크 정보

-- X$KSMSP.KSMCHDUR : 청크의 지속 시간 (공유 풀 > 서브 힙 > 미니 힙, 동일한 미니 힙에서 청크를 할당 받기 위함)
-- X$KSMSP.KSMCHCLS : 청크의 유형 
--   (free: Free Chunk, perm: Permanent Chunk, recr: Recreatable Chunk, freeabl: Freeable Chunk)
--   "R-" 접두어가 붙은 경우 예비 풀 영역의 청크임
-- X$KSMSP.KSMCHCOM : 청크의 컴포넌트 타입 (예: kxt.c: Trigger ==> 트리거 실행 정보)
-- X$KSMSP.KSMCHSIZ : 청크의 크기 (BYTE)

SELECT KSMCHCLS CLASS
     , COUNT(KSMCHCLS) NUM
     , SUM(KSMCHSIZ) SIZ
     , TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
  FROM X$KSMSP
 GROUP BY KSMCHCLS
 ORDER BY 4 DESC;
/*
CLASS    NUM SIZ AVG SIZE    
-------- --- --- -------------
perm       2 127498952 62,255.35KB   
R-free     5 16792680 3,279.82KB    
free     144 108427992 735.32KB      
freeabl  14801 47377384 3.13KB        
recr     20124 35446392 1.72KB        
R-freea   10 480 .05KB          
*/

SELECT KSMCHCOM CLASS
     , COUNT(KSMCHCLS) NUM
     , SUM(KSMCHSIZ) SIZ
     , TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
  FROM X$KSMSP
 GROUP BY KSMCHCOM
 ORDER BY 4 DESC;
/*
CLASS            NUM SIZ AVG SIZE    
---------------- --- --- -------------
permanent memor    2 127498952 62,255.35KB   
KSFD SGA I/O b     1 4190296 4,092.09KB    
free memory      145 125192608 843.16KB      
character set o    5 534912 104.48KB      
MS alert log       1 72728 71.02KB       
NETWORK BUFFER     1 32832 32.06KB       
PX subheap         1 32744 31.98KB       
KGSK scheduler     2 52280 25.53KB       
dbgefgHtAddSK-1   24 482624 19.64KB       
kwqmncini-slv      1 18424 17.99KB       
...
*/

-- X$KSMSP 엑세스시 래치 필요(Shared Pool Latch) - 래치 경합 발생 가능성 있음 (운영중 조회 금지)

-- 11g NF
SELECT KSMCHCOM,
       KSMCHSIZ
  FROM SYS.X$KSMSP 
 WHERE REGEXP_LIKE(KSMCHCOM, '[[:alnum:]]+\^[[:xdigit:]]+')
   AND ROWNUM <= 100;
/*
KSMCHCOM           KSMCHSIZ
---------------- ----------
SQLA^aa0beccb          4096
KGLS^283faff0          4096 <<< KGLS : X$KGLOB, 283faff0 : X$KGLOB.KGLNAHSH
KGLS^f678506c          4096
KGLS^19563d19          4096
SQLA^aa0beccb          4096 <<< SQLA : V$SQLAREA, aa0beccb : V$SQLAREA.HASH_VALUE
SQLA^ac17a07b          4096
SQLA^ac17a07b          4096
SQLA^ac17a07b          4096
SQLA^ac17a07b          4096
SQLA^ac17a07b          4096
SQLA^2b3396e2          4096
...
*/

SELECT SQL_TEXT
  FROM V$SQLAREA
 WHERE HASH_VALUE = TO_NUMBER('aa0beccb', 'xxxxxxxxxxx')
/*
SQL_TEXT
--------------------------------------------------------------------------------
select java_pool_size_for_estimate s,           java_pool_size_factor * 100 f...
*/

SELECT KGLNAOBJ
  FROM X$KGLOB
 WHERE KGLNAHSH = TO_NUMBER('283faff0', 'xxxxxxxxxxx');
/*
KGLNAOBJ
--------------------------------------------------------------------------------
GV$JAVA_POOL_ADVICE
*/
X$KSMPP - PGA 청크 정보
SELECT KSMCHCLS CLASS
     , COUNT(KSMCHCLS) NUM
     , SUM(KSMCHSIZ) SIZ
     , TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
  FROM X$KSMPP
 GROUP BY KSMCHCLS
 ORDER BY 4 DESC;
/*
CLASS    NUM SIZ AVG SIZE    
-------- --- --- -------------
free       6 60400 9.83KB        
perm      19 186568 9.59KB        
recr       6 24384 3.97KB        
freeabl   59 195104 3.23KB             
*/

SELECT KSMCHCOM CLASS
     , COUNT(KSMCHCLS) NUM
     , SUM(KSMCHSIZ) SIZ
     , TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
  FROM X$KSMPP
 GROUP BY KSMCHCOM
 ORDER BY 4 DESC;
/*
CLASS            NUM SIZ AVG SIZE    
---------------- --- --- -------------
Fixed Uga          1 38248 37.35KB       
kgh stack          1 17024 16.63KB       
free memory        6 60400 9.83KB        
KFK_IO_SUBHEAP     1 9976 9.74KB        
permanent memor   19 186568 9.59KB        
Alloc environm     8 50192 6.13KB        
PLS PGA hp         3 18672 6.08KB        
diag pga          14 59368 4.14KB        
peshm.c:Proces     2 5616 2.74KB        
kjztprq struct     1 2600 2.54KB        
KSFQ heap          1 2040 1.99KB             
...
*/

-- X$KSMPP 엑세스시 래치 불필요 (PGA 영역은 프로세스간 공유 안됨)
-- X$KSMPP(PGA) 에는 "R-" 로 시작하는 예비 풀이 없음
-- X$KSMPP 테이블은 프로세스 자기 자신만 조회 가능, 다른 프로세스의 PGA 분석은 PGA 힙 덤프 사용

힙 덤프 파일

힙 덤프 파일 얻기
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                "
*/

4031 진단 데이터

ORA-4031
> 오라클은 ORA-4031 에러 발생시 기본적인 진단 데이터를 트레이스 파일에 기록 함 (SGA 힙 덤프)
> _4031_DUMP_BITVEC 파라미터에 의해 기능 사용 설정
ORA-4031 데모
SQL> exec proc_4031(1);
BEGIN proc_4031(1); END;

*
ERROR at line 1:
ORA-04031: unable to allocate 4016 bytes of shared memory ("shared pool", "select 'x ...", "SQLA^7291b95f", "ub1[]: qkexrXformVal")
ORA-06512: at "UDEV.PROC_4031", line 11
...

-- 첫번째 파라미터 : shared pool - 공유 풀에서 발생한 에러
-- 두번째 파라미터 : 오브젝트 명
-- 세번째 파라미터 : 컴포넌트(SQLA : SQLArea, 7291b95f : V$SQL.HASH_VALUE)
-- 네번재 파라미터 : 내부적으로 수행한 함수 정보(콜 트리)

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.