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을 생성해 사용하는 방법을 권한다.