Oracle ORA-30009 오류 원인과 해결 방법 완벽 가이드

ORA-30009
2026년 10월 08일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-30009 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-30009 Not enough memory for CONNECT BY operation 는?

ORA-30009 에러는 Oracle에서 계층형 쿼리(Hierarchical Query)를 수행하는 CONNECT BY 절을 실행할 때 메모리가 부족하여 작업을 완료하지 못할 경우 발생합니다. 이 에러는 주로 데이터의 양이 방대하거나, 순환 참조(Cycle)가 발생하거나, 쿼리 자체가 비효율적으로 설계되어 내부적으로 처리해야 하는 중간 결과 집합이 지나치게 커질 때 나타납니다. 특히 조직도, BOM(Bill of Materials), 카테고리 트리 구조와 같이 깊은 계층을 탐색하는 환경에서 자주 목격되며, DBA와 개발자 모두 반드시 숙지해야 할 에러입니다.

주요 발생 원인

1. 과도하게 깊은 계층 구조 또는 무한 루프(Cycle) 발생

CONNECT BY 쿼리는 내부적으로 재귀적 탐색을 수행합니다. 데이터에 순환 참조(A→B→C→A)가 존재하거나 계층의 깊이가 수천 레벨 이상으로 형성될 경우, Oracle은 중간 처리 결과를 메모리에 계속 쌓아야 하므로 결국 메모리 한계에 도달하게 됩니다. NOCYCLE 키워드를 사용하지 않은 상태에서 순환 참조 데이터를 탐색하면 이 에러가 ORA-01436(CONNECT BY loop)과 함께 또는 단독으로 발생할 수 있습니다.

2. PGA 메모리 설정 부족

CONNECT BY 작업은 세션의 PGA(Program Global Area) 메모리를 사용합니다. PGA_AGGREGATE_TARGET 또는 PGA_AGGREGATE_LIMIT 파라미터가 작업 규모에 비해 지나치게 낮게 설정되어 있으면, 중간 결과 집합을 메모리에 유지하지 못하고 에러가 발생합니다. 특히 수십만 건 이상의 대용량 데이터를 계층 탐색할 때 이 문제가 집중적으로 나타납니다.

3. 비효율적인 쿼리 설계 (불필요한 데이터 포함)

CONNECT BY 절 실행 전 WHERE 조건으로 데이터를 충분히 필터링하지 않으면, 탐색 대상이 되는 행의 수가 폭발적으로 증가합니다. 예를 들어 수백만 건의 전체 테이블을 대상으로 계층 탐색을 수행하는 경우, 필요한 서브트리만 탐색하도록 START WITH 조건을 정밀하게 지정하지 않으면 메모리 소모가 걷잡을 수 없이 커집니다.

해결 방법

원인 1 해결: NOCYCLE 키워드 및 CONNECT_BY_ISCYCLE 활용

순환 참조가 의심되는 경우 NOCYCLE 키워드를 추가하여 무한 루프를 방지하고, CONNECT_BY_ISCYCLE 의사 컬럼을 통해 어느 행에서 순환이 발생하는지 확인합니다.

-- 순환 참조가 있는 데이터에서 안전하게 계층 탐색
SELECT
    LEVEL,
    employee_id,
    manager_id,
    first_name,
    CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

-- 순환 참조 데이터를 사전에 탐지하는 쿼리
SELECT
    employee_id,
    manager_id
FROM employees
WHERE CONNECT_BY_ISCYCLE = 1
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

원인 2 해결: PGA 메모리 파라미터 조정

DBA 권한으로 PGA 관련 파라미터를 조정하여 CONNECT BY 작업에 충분한 메모리를 할당합니다.

-- 현재 PGA 설정 확인
SHOW PARAMETER PGA_AGGREGATE_TARGET;
SHOW PARAMETER PGA_AGGREGATE_LIMIT;

-- PGA 목표치 조정 (시스템 메모리 여유에 맞게 설정)
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G SCOPE = BOTH;

-- 세션 레벨에서 임시로 workarea 크기 조정 (수동 PGA 관리 모드)
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 104857600; -- 100MB

-- PGA 사용 현황 모니터링
SELECT
    name,
    value / 1024 / 1024 AS value_mb
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'aggregate PGA auto target',
    'total PGA inuse',
    'total PGA allocated'
);

원인 3 해결: 쿼리 최적화 – 필터링 강화 및 서브쿼리 활용

탐색 범위를 최소화하기 위해 START WITH 조건을 구체적으로 지정하고, 필요한 경우 CTE(Common Table Expression)와 재귀 쿼리를 활용하여 쿼리를 재설계합니다.

-- 비효율적인 쿼리 (전체 테이블 대상)
-- 개선 전: 모든 행을 대상으로 계층 탐색 (메모리 과다 소모)
SELECT LEVEL, category_id, parent_id, category_name
FROM category
CONNECT BY PRIOR category_id = parent_id;

-- 개선 후: START WITH로 특정 루트 노드만 탐색
SELECT
    LEVEL,
    category_id,
    parent_id,
    category_name,
    SYS_CONNECT_BY_PATH(category_name, '/') AS full_path
FROM category
WHERE LEVEL <= 5  -- 탐색 깊이 제한
START WITH parent_id IS NULL
       AND category_id = 100  -- 특정 루트만 지정
CONNECT BY NOCYCLE PRIOR category_id = parent_id;

-- 재귀 CTE를 활용한 대안 쿼리 (Oracle 11g R2 이상)
WITH hierarchy_cte (category_id, parent_id, category_name, lvl) AS (
    -- Anchor: 루트 노드 선택
    SELECT category_id, parent_id, category_name, 1 AS lvl
    FROM category
    WHERE parent_id IS NULL
      AND category_id = 100

    UNION ALL

    -- Recursive: 자식 노드 탐색 (깊이 제한 적용)
    SELECT c.category_id, c.parent_id, c.category_name, h.lvl + 1
    FROM category c
    JOIN hierarchy_cte h ON c.parent_id = h.category_id
    WHERE h.lvl < 5  -- 재귀 깊이 제한
)
SELECT * FROM hierarchy_cte
ORDER BY lvl, category_id;

예방 방법

1. 계층 쿼리 실행 전 데이터 품질 검증 및 깊이 제한 적용

운영 환경에 계층형 쿼리를 배포하기 전, 반드시 대상 테이블의 순환 참조 여부와 최대 계층 깊이를 사전에 검증해야 합니다. LEVEL 의사 컬럼을 활용한 WHERE LEVEL <= N 조건을 항상 명시하여 탐색 깊이를 제어하고, 데이터 입력/수정 시 순환 참조가 발생하지 않도록 애플리케이션 레벨 또는 트리거 기반 제약을 구현하는 것이 Best Practice입니다.

-- 주기적으로 순환 참조 데이터 점검하는 모니터링 쿼리
SELECT COUNT(*) AS cycle_count
FROM employees
WHERE CONNECT_BY_ISCYCLE = 1
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

2. PGA 메모리 모니터링 및 적정 크기 유지

정기적으로 V$PGASTAT, V$SQL_WORKAREA 뷰를 모니터링하여 CONNECT BY를 많이 사용하는 세션의 PGA 소비 패턴을 파악하고, PGA_AGGREGATE_TARGET을 서버 물리 메모리의 20~25% 수준으로 유지하는 것이 권장됩니다. AWR(Automatic Workload Repository) 리포트의 PGA Memory Advisory 섹션을 주기적으로 검토하여 임계값 초과 여부를 모니터링하십시오.