엑시엄이 보는 DB 세상
ORA-04030: out of process memory 에러의 이해 0 1 7,121

by axiom ORA-04030 [2014.06.23]


배치 작업 또는 대량의 작업이 수행되는 PL/SQL 프로그램의 경우에 콜렉션을 사용하는 곳을 종종 볼 수가 있다. 콜렉션은 프로그래밍 언어의 배열과 같은 형태 구조로 같은 데이터타입을 가진 요소들로 구성되며 메모리에 일정 저장 공간을 할당 받아 사용해 메모리 내에서 빠른 연산 작업 등을 수행할 수 있다.

PL/SQL에서는 세 가지 콜렉션을 사용할 수 있다.

  • - Varray
  • - Nested Table
  • - Associative Array (index-by table)

이 중 Nested Table과 Associative Array의 경우 저장 공간의 크기에 제한이 없어 프로그래밍의 동적 배열과 유사하다. 문제는 Nested Table과 Associative Array를 사용할 경우 ORA-04030 에러가 발생할 수 있다는 것이다.

ORA-04030은 Oracle 서버 프로세스에 운영체제로부터 더 이상 메모리를 할당 받을 수 없을 때 발생한다. OS의 물리적 메모리 크기, Swap 메모리 구성 등의 설정과 PGA 구성에 따라서 발생이 가능한 것이다. 해당 에러가 발생할 경우에는 다음의 메모리 설정에 대해 확인해야 한다.

  • - OS의 사용 가능한 메모리는 남아 있는가?
  • - OS의 Oracle에 리소스 사용 제한 설정이 되어 있진 않은가?
  • - PGA가 너무 작게 설정되어 있진 않은가?

이 세 가지에 대한 내용에 이상이 없다면 무엇이 문제일까? 결론부터 이야기하자면 운영 시스템의 종류에 따라서 다르지만 하나의 프로세스에서 사용할 수 있는 메모리의 양이 제한적이라는 것이다.

즉 대용량의 테이블에서 콜렉션 등의 배열 타입을 사용할 때 과다한 메모리 사용 시 발생할 수 있다는 것이다. [리스트 1]의 예를 살펴보자.

  • [리스트 1] ORA-04030 에러 발생의 예
  • -- 테스트 환경
    Server : HP-UX B.11.31
    Oracle Ver : 11.2.0.4
    Physical Memory : 130 GB
    PGA_AGGREGATE_TARGET  : 15 GB 
    
    
    
    --  테이블 및 인덱스 생성
    DROP TABLE DT_TEST;
    CREATE TABLE DT_TEST
    (
      COL1 NUMBER,
      COL2 CHAR(2000),
      COL3 CHAR(2000),
      COL4 CHAR(2000),
      COL5 CHAR(2000)
    );
    
    
    INSERT INTO DT_TEST
    SELECT LEVEL, 'A', 'B', 'C'
    FROM DUAL
    CONNECT BY LEVEL < 3000000;
    
    CREATE INDEX IDX_DT_TEST ON DT_TEST (COL1);
    
    
    
    -- 테스트 전 Resource 
    -- PGA 사용량
    SELECT SID, S.SERIAL#, PID, PGA_USED_MEM,PGA_ALLOC_MEM,PGA_MAX_MEM
      FROM V$SESSION S, V$PROCESS P
     WHERE S.PADDR=P.ADDR(+)
       AND SID=&SESSION_ID;
       
    SID   SERIAL#  PID  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_MAX_MEM
    ---   ------- ----  ------------- -------------  ------------
    4784    1087    47      1,957,464    2,040,800      2,040,800
    
    
    
    -- System Memory 현황 (vmstat)
    memory                   page                               faults           cpu
        avm     free    re   at     pi   po    fr   de   sr     in     sy    cs  us  sy id
    1359262  11401760    0    0     0    0     0    0    34  57939 233521 15052   3  1  96
    
    
    
    -- V$PGASTAT 
    SELECT * FROM V$PGASTAT;
    
    NAME                                        VALUE UNIT
    -------------------------------   --------------------
    aggregate PGA target parameter    16,106,127,360 bytes
    aggregate PGA auto target          5,361,988,608 bytes
    global memory bound                1,073,741,824 bytes
    total PGA inuse                   10,153,646,080 bytes
    total PGA allocated               11,375,516,672 bytes
    maximum PGA allocated             27,863,030,784 bytes
    total freeable PGA memory            438,894,592 bytes
    
    
    
    --  PL/SQL 실행
    DECLARE 
        TYPE DT_TEST_ARRAYTYPE IS TABLE OF DT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
        DT_TEST_ARRAY DT_TEST_ARRAYTYPE;
    BEGIN
    
        FOR LOUT IN 1 .. 2097152
        LOOP
        SELECT  COL1, COL2
        INTO DT_TEST_ARRAY(LOUT)   
        FROM DT_TEST
        WHERE COL1 = LOUT;
    
        -- DBMS_OUTPUT.PUT_LINE(DT_TEST_ARRAY.LAST);
    END LOOP;
    
    END;
    /
    
    
    ERROR at line 1:
    ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pl/sql vc2)
    
    
    -- Error 발생 시 Resource
    -- PGA 사용량
    SID   SERIAL# PID   PGA_USED_MEM   PGA_ALLOC_MEM   PGA_MAX_MEM
    ----- ------- ---  --------------  --------------  --------------
    4784     1087  47  16,517,681,856  16,526,025,696  16,526,025,696
    
    
    -- System Memory 현황 (vmstat)
    memory                 page                        faults            cpu
        avm    free  re  at  pi  po  fr  de  sr     in     sy    cs  us sy  id
    5424468  7352427  0   0   0   0   0   0  34  57937 233555 15051  3  1   96
    
    
    -- V$PGASTAT
    NAME                                         VALUE UNIT
    --------------------------------   --------------------
    aggregate PGA target parameter     16,106,127,360 bytes
    aggregate PGA auto target           1,006,632,960 bytes
    global memory bound                 1,073,741,824 bytes
    total PGA inuse                    26,662,909,952 bytes
    total PGA allocated                27,889,073,152 bytes
    maximum PGA allocated              27,902,974,976 bytes
    total freeable PGA memory             438,829,056 bytes
    
    
    -- UNIX Limit
       default pagesize가 64KB일 때 4GB, 256KB일 때 16GB
    
    
    -- OS별 프로세스 Max Memory Size
    -- Windows 
       32bit Windows 2GB or 2.7GB (Windows 버전에 따라 다름)
       64bit Windows 8GB  
    -- Linux  
     default pagesize가 64KB일 때 4GB, 256KB일 때 16GB
    

이처럼 대용량의 테이블을 Array 방식으로 메모리에서만 처리 시 ORA-04030 에러가 발생한다. OS 종류 및 설정에 따라 다르지만 하나의 프로세스에서 너무 많은 메모리를 사용할 경우 시스템에 전반적인 영향을 미칠 수 있어 OS마다 하나의 프로세스에서 사용 가능한 메모리 크기에 제한이 있다.

또한 에러 없이 수행돼도 한 프로세스에서 너무 많은 메모리를 사용할 경우 시스템에 전반적인 영향을 미쳐 성능 저하의 요인이 될 수 있다.

대용량의 테이블을 처리 시 임시 세그먼트에 데이터를 적재하는 Temporary Table 사용이 가능하다.

Temporary Table을 사용할 경우 데이터 처리 시 단일 데이터 행 처리 방식이 아니라 다중 데이터 행 처리 방식으로 처리가 가능하며 대용량의 경우 Nested Loop 방식이 아닌 Hash 방식으로 변경 처리가 가능하다.

대용량 테이블 또는 대량의 배치 작업을 처리할 경우에는 Temporary Table을 생성해 사용하는 방법을 권한다.

- 강좌 URL : http://www.gurubee.net/lecture/2771

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 김군호 [2018.06.01 11:48:23]

잘 봤습니다. 

감사합니다.

참고하겠습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입