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

ORA-00371 not enough shared pool memory 는?

ORA-00371 에러는 Oracle 데이터베이스의 SGA(System Global Area) 내 공유 풀(Shared Pool) 메모리가 부족할 때 발생하는 에러입니다. 공유 풀은 SQL 파싱 결과, PL/SQL 코드, 데이터 딕셔너리 캐시 등을 저장하는 핵심 메모리 영역으로, 이 공간이 부족해지면 새로운 SQL 파싱이나 객체 로딩이 불가능해집니다. 특히 대규모 트랜잭션이 집중되거나 하드 파싱(Hard Parsing)이 빈번하게 발생하는 OLTP 환경에서 자주 나타나며, 시스템 전체 성능에 심각한 영향을 줄 수 있습니다.

---

주요 발생 원인

1. SHARED_POOL_SIZE 파라미터 설정값 부족

가장 근본적인 원인으로, 데이터베이스 초기 설계 시 공유 풀 크기를 너무 작게 설정했거나, 시간이 지남에 따라 애플리케이션 규모가 커져 기존 설정값이 부족해지는 경우입니다. Oracle 11g 이상에서는 AMM(Automatic Memory Management) 또는 ASMM(Automatic Shared Memory Management)을 사용해 자동으로 조정이 가능하지만, 전체 SGA 크기 자체가 작다면 공유 풀 역시 충분한 메모리를 확보받지 못합니다. 현재 설정값과 실제 사용량을 비교하여 적절한 크기로 조정하는 것이 최우선 과제입니다.

2. 하드 파싱(Hard Parsing) 과다 발생

바인드 변수(Bind Variable)를 사용하지 않고 리터럴 값이 포함된 SQL을 반복 실행하면, 동일한 로직임에도 불구하고 매번 새로운 SQL로 인식되어 하드 파싱이 발생합니다. 하드 파싱은 실행 계획(Execution Plan)을 새로 생성하고 공유 풀의 라이브러리 캐시(Library Cache)에 저장하므로 메모리를 급격히 소진시킵니다. 수천, 수만 개의 서로 다른 SQL 커서가 공유 풀을 가득 채우면 결국 ORA-00371 에러로 이어지게 됩니다.

3. 대형 PL/SQL 패키지 또는 객체의 반복 로딩

크기가 큰 PL/SQL 패키지, 프로시저, 함수 등은 처음 실행될 때 공유 풀에 전체 코드가 로딩됩니다. 공유 풀 여유 공간이 부족하면 기존에 캐시된 객체를 강제로 제거(Flush)하고 새로 로딩하는 과정이 반복되며, 이 과정에서 메모리 단편화(Fragmentation)가 심해져 사용 가능한 연속 메모리 공간이 줄어들게 됩니다. 이는 단순히 메모리 총량이 부족한 것이 아니라 연속된 큰 메모리 블록을 할당받지 못하는 상황으로 이어질 수 있습니다.

---

해결 방법

1. 공유 풀 크기 확인 및 증설

먼저 현재 공유 풀 사용 현황을 정확히 파악하는 것이 중요합니다.

-- 현재 공유 풀 사용 현황 조회
SELECT name,
       ROUND(bytes / 1024 / 1024, 2) AS size_mb,
       ROUND(resizes, 0)             AS resizes
FROM   v$sgainfo
WHERE  name LIKE '%Shared Pool%';

-- 공유 풀 상세 사용률 조회
SELECT pool,
       name,
       ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
ORDER  BY bytes DESC
FETCH  FIRST 10 ROWS ONLY;

-- 현재 파라미터 값 확인
SHOW PARAMETER shared_pool_size;
SHOW PARAMETER sga_target;
SHOW PARAMETER memory_target;

현황 파악 후 공유 풀 크기를 동적으로 증설합니다.

-- ASMM 환경에서 공유 풀 크기 동적 증설 (재시작 불필요)
ALTER SYSTEM SET shared_pool_size = 512M SCOPE = BOTH;

-- SGA_TARGET 전체 크기 증설 (ASMM 사용 시)
ALTER SYSTEM SET sga_target = 4G SCOPE = BOTH;

-- AMM 사용 시 전체 메모리 크기 조정
ALTER SYSTEM SET memory_target = 8G SCOPE = SPFILE;

-- 설정 후 확인
SELECT name, value
FROM   v$parameter
WHERE  name IN ('shared_pool_size', 'sga_target', 'memory_target');

2. 하드 파싱 줄이기 – 바인드 변수 사용 및 커서 공유 확인

-- 하드 파싱이 많은 SQL 상위 10개 조회 (즉시 튜닝 대상)
SELECT sql_text,
       parse_calls,
       executions,
       ROUND(parse_calls / DECODE(executions, 0, 1, executions) * 100, 2) AS hard_parse_ratio_pct
FROM   v$sql
WHERE  executions > 0
ORDER  BY parse_calls DESC
FETCH  FIRST 10 ROWS ONLY;

-- 리터럴 SQL을 사용하는 문제 쿼리 패턴 확인 (동일 SQL이 여러 커서로 존재하는 경우)
SELECT SUBSTR(sql_text, 1, 60) AS sql_sample,
       COUNT(*)                AS cursor_count,
       SUM(executions)         AS total_executions
FROM   v$sql
GROUP  BY SUBSTR(sql_text, 1, 60)
HAVING COUNT(*) > 10
ORDER  BY cursor_count DESC;

-- CURSOR_SHARING 파라미터를 FORCE로 설정 (임시 대응 - 근본 해결은 바인드 변수 사용)
-- 주의: 애플리케이션 테스트 후 적용 권장
ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = BOTH;

-- 바인드 변수 사용 예시 (애플리케이션 코드 수정 방향 제시)
-- 나쁜 예 (하드 파싱 유발)
-- SELECT * FROM employees WHERE employee_id = 100;
-- SELECT * FROM employees WHERE employee_id = 101;

-- 좋은 예 (바인드 변수 사용)
VARIABLE v_emp_id NUMBER;
EXEC :v_emp_id := 100;
SELECT * FROM employees WHERE employee_id = :v_emp_id;

3. 공유 풀 수동 플러시 및 단편화 해소 (긴급 대응)

-- 공유 풀 플러시 (운영 환경 주의! 일시적 성능 저하 발생 가능)
-- 반드시 DBA 승인 및 유지보수 시간에 실행
ALTER SYSTEM FLUSH SHARED_POOL;

-- 공유 풀 내 free memory 확인
SELECT name,
       ROUND(bytes / 1024 / 1024, 2) AS free_mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
  AND  name = 'free memory';

-- 고정(KEEP) 메모리 설정 - 자주 사용하는 대형 패키지를 공유 풀에 고정
-- 단편화 방지에 효과적
EXECUTE DBMS_SHARED_POOL.KEEP('SYS.STANDARD', 'P');
EXECUTE DBMS_SHARED_POOL.KEEP('패키지소유자.패키지명', 'P');

-- 고정된 객체 확인
SELECT owner, name, type, kept
FROM   v$db_object_cache
WHERE  kept = 'YES';

4. 메모리 어드바이저를 활용한 최적 크기 산정

-- Shared Pool Advisor를 통한 최적 크기 권고 확인
-- (statistics_level = TYPICAL 이상 설정 필요)
SELECT shared_pool_size_for_estimate           AS pool_size_mb,
       estd_lc_size                            AS estd_lib_cache_mb,
       estd_lc_memory_object_hits              AS estd_cache_hits,
       estd_lc_time_saved_factor               AS time_saved_factor
FROM   v$shared_pool_advice
ORDER  BY shared_pool_size_for_estimate;

---

예방 방법

1. 정기적인 공유 풀 모니터링 및 알림 체계 구축

공유 풀 사용률이 85%를 초과하면 자동으로 알림이 발생하도록 Oracle Enterprise Manager(OEM) 또는 커스텀 모니터링 스크립트를 구성해야 합니다. 아래 쿼리를 정기적으로 실행하는 모니터링 잡(Job)을 등록하여 공유 풀 상태를 지속적으로 관리하는 것이 Best Practice입니다.

-- 공유 풀 사용률 모니터링 쿼리 (Cron/DBMS_SCHEDULER 등록 권장)
SELECT ROUND((1 - (free.bytes / total.bytes)) * 100, 2) AS used_pct
FROM   (SELECT SUM(bytes) AS bytes FROM v$sgastat
        WHERE  pool = 'shared pool' AND name = 'free memory') free,
       (SELECT SUM(bytes) AS bytes FROM v$sgastat
        WHERE  pool = 'shared pool') total;

2. 개발 단계부터 바인드 변수 사용 표준화 및 코드 리뷰 체계화

애플리케이션 개발 단계에서부터 바인드 변수 사용을 코딩 표준으로 강제화하고, 코드 리뷰 시 리터럴 SQL 사용 여부를 반드시 체크리스트 항목으로 포함시켜야 합니다. 운영 환경 배포 전 v$sql을 조회하여 동일한 패턴의 커서가 과도하게 생성되는지 정기 점검하면, 하드 파싱으로 인한 공유 풀 고갈 문제를 사전에 차단할 수 있습니다.

---

관련 에러

• ORA-04031: unable to allocate N bytes of shared memory — ORA-00371보다 더 직접적으로 공유 풀 메모리 할당 실패를 나타내는 에러로, 가장 빈번하게 함께 발생합니다. ORA-00371이 선행 경고 성격이라면 ORA-04031은 실제 메모리 할당 실패를 의미합니다.
• ORA-00604: error occurred at recursive SQL level N — 공유 풀 부족으로 인해 내부 재귀 SQL 실행 중 오류가 발생할 때 나타나며, ORA-04031 또는 ORA-00371과 함께 스택 에러로 출력되는 경우가 많습니다.
• ORA-07445 / ORA-00600: 극단적인 공유 풀 고갈 상황에서 Oracle 내부 오류로 이어질 수 있으며, alert.log에서 ORA-00371 이후 연쇄적으로 확인될 수 있습니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.