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

ORA-04025 maximum amount of memory for library cache exceeded 는?

ORA-04025 에러는 Oracle 데이터베이스의 Library Cache가 사용할 수 있는 최대 메모리 한계를 초과했을 때 발생하는 에러입니다. Library Cache는 Shared Pool 내에 위치하며, SQL 문장, PL/SQL 블록, 저장 프로시저, 함수, 패키지 등의 파싱된 코드와 실행 계획을 저장하는 공간입니다. 주로 대규모 PL/SQL 객체를 컴파일하거나, Shared Pool 크기가 충분하지 않은 환경에서 복잡한 패키지를 로드할 때 이 에러가 발생합니다.

주요 발생 원인

• Shared Pool 크기 부족

가장 일반적인 원인으로, SHARED_POOL_SIZE 파라미터가 실제 워크로드에 비해 너무 작게 설정된 경우입니다. Oracle은 SQL 파싱 결과와 PL/SQL 컴파일 결과물을 Library Cache에 저장하는데, 이 공간이 부족하면 새로운 객체를 로드할 수 없어 ORA-04025가 발생합니다. 특히 야간 배치 작업이나 수많은 동시 세션이 새로운 SQL을 실행할 때 급격히 메모리를 소모하는 상황에서 자주 나타납니다.

• 비효율적인 SQL 작성 (Literal SQL 남발)

바인드 변수를 사용하지 않고 리터럴 값을 SQL에 직접 포함시키는 경우, 값이 조금만 달라도 Oracle은 완전히 다른 SQL로 인식하여 각각 별도로 파싱하고 Library Cache에 저장합니다. 예를 들어 WHERE id = 1, WHERE id = 2, WHERE id = 3 처럼 수천 개의 유사 SQL이 쌓이면 Library Cache를 급격히 소진시킵니다. 이는 OLTP 환경에서 애플리케이션 코드가 바인드 변수 없이 동적 쿼리를 생성하는 패턴에서 매우 흔하게 발생합니다.

• 대형 PL/SQL 패키지 또는 복잡한 객체 컴파일

수천 줄에 달하는 대형 PL/SQL 패키지나 복잡하게 중첩된 뷰, 트리거를 컴파일하거나 처음 실행할 때 Library Cache에서 한 번에 큰 연속된 메모리 청크(chunk)를 요구합니다. Shared Pool이 단편화(Fragmentation)되어 있을 경우, 충분한 전체 여유 메모리가 있더라도 연속된 큰 공간을 확보하지 못해 ORA-04025가 발생할 수 있습니다. 이 경우 DBMS_SHARED_POOL.KEEP 프로시저를 활용하여 중요 객체를 메모리에 고정해 단편화를 줄일 수 있습니다.

해결 방법

1. Shared Pool 크기 동적 증가

즉각적인 해결이 필요한 경우, 운영 중 재기동 없이 Shared Pool 크기를 늘릴 수 있습니다.

-- 현재 Shared Pool 크기 확인
SHOW PARAMETER shared_pool_size;

-- SGA 전체 크기 확인
SELECT component, current_size/1024/1024 AS "Size(MB)"
FROM v$sga_dynamic_components
WHERE component = 'shared pool';

-- 동적으로 Shared Pool 크기 변경 (예: 512MB로 증가)
ALTER SYSTEM SET shared_pool_size = 512M SCOPE=BOTH;

-- spfile에 영구 반영 (재기동 후에도 유지)
ALTER SYSTEM SET shared_pool_size = 512M SCOPE=SPFILE;

2. Library Cache 현황 분석 및 Shared Pool Flush

Library Cache 내 메모리 사용 현황을 분석하고, 필요 시 Shared Pool을 정리합니다. (운영 환경에서는 신중히 사용하세요.)

-- Library Cache 히트율 및 리로드 현황 확인
SELECT namespace,
       gets,
       gethits,
       ROUND(gethitratio * 100, 2) AS hit_ratio_pct,
       pins,
       pinhits,
       ROUND(pinhitratio * 100, 2) AS pin_hit_ratio_pct,
       reloads,
       invalidations
FROM v$librarycache
ORDER BY namespace;

-- Shared Pool 내 여유 메모리 확인
SELECT pool,
       name,
       bytes/1024/1024 AS "Free Memory(MB)"
FROM v$sgastat
WHERE name = 'free memory'
  AND pool = 'shared pool';

-- Shared Pool Flush (비상시에만 사용 - 성능 일시 저하 주의)
ALTER SYSTEM FLUSH SHARED_POOL;

3. 리터럴 SQL 찾기 및 바인드 변수 전환 유도

Library Cache를 가장 많이 낭비하는 리터럴 SQL을 찾아내고, CURSOR_SHARING 파라미터로 임시 대응합니다.

-- 유사하지만 다른 SQL로 파싱된 문장들 확인 (리터럴 남발 탐지)
SELECT SUBSTR(sql_text, 1, 60) AS sql_snippet,
       COUNT(*) AS similar_count,
       SUM(sharable_mem) / 1024 / 1024 AS total_mem_mb
FROM v$sql
GROUP BY SUBSTR(sql_text, 1, 60)
HAVING COUNT(*) > 10
ORDER BY similar_count DESC;

-- CURSOR_SHARING 파라미터로 임시 대응 (리터럴을 바인드 변수로 자동 대체)
-- 권장값: FORCE (주의: 일부 쿼리 성능 변화 가능)
ALTER SYSTEM SET cursor_sharing = FORCE SCOPE=BOTH;

-- 근본 해결: 바인드 변수 사용 예시
-- 나쁜 예 (리터럴 사용)
-- SELECT * FROM employees WHERE department_id = 10;
-- SELECT * FROM employees WHERE department_id = 20;

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

4. 대형 PL/SQL 객체 메모리 고정 (KEEP)

자주 사용되는 대형 패키지를 Library Cache에 고정하여 단편화를 방지합니다.

-- 현재 Library Cache에서 메모리를 많이 사용하는 객체 확인
SELECT owner,
       name,
       type,
       sharable_mem / 1024 AS mem_kb,
       loads,
       executions,
       kept
FROM v$db_object_cache
WHERE type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
ORDER BY sharable_mem DESC
FETCH FIRST 20 ROWS ONLY;

-- 자주 사용하는 패키지를 Shared Pool에 고정
EXECUTE DBMS_SHARED_POOL.KEEP('SCOTT.MY_LARGE_PACKAGE', 'P');

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

-- 고정 해제가 필요한 경우
EXECUTE DBMS_SHARED_POOL.UNKEEP('SCOTT.MY_LARGE_PACKAGE', 'P');

예방 방법

• AMM/ASMM 활용 및 정기적 메모리 모니터링

Oracle 11g 이상 환경이라면 자동 메모리 관리(AMM: MEMORY_TARGET) 또는 자동 공유 메모리 관리(ASMM: SGA_TARGET)를 활성화하여 Oracle이 워크로드 변화에 따라 Shared Pool 크기를 자동으로 조절하도록 설정하는 것이 좋습니다. 또한 v$sgastat, v$librarycache, v$shared_pool_advice 뷰를 활용한 정기 모니터링 스크립트를 스케줄링하여, Shared Pool 여유 메모리가 전체의 10% 미만으로 떨어지는 상황을 조기에 감지하고 선제적으로 조치하는 체계를 갖추어야 합니다.

“`sql

— Shared Pool Advice로 적정 크기 추천 확인

SELECT shared_pool_size_for_estimate AS “Pool Size(MB)”,

estd_lc_size AS “Est LC Size(MB)”,

estd_lc_memory_objects,

estd_lc_time_saved_factor AS “Time Saved Factor”

FROM v$shared_pool_advice

ORDER BY shared_pool_size_for_estimate;

“`

• 개발 단계부터 바인드 변수 사용 의무화 및 코드 리뷰 체계화

애플리케이션 개발 표준에 바인드 변수 사용을 필수 규정으로 포함시키고, 코드 리뷰 단계에서 리터럴 SQL을 자동으로 탐지하는 정적 분석 도구를 도입해야 합니다. 개발 및 QA 환경에서 v$sql 뷰를 주기적으로 조회하여 리터럴 SQL 비율(FORCE_MATCHING_SIGNATURE가 같은 SQL 수)을 KPI로 관리하면 운영 환경에서의 문제를 사전에 차단할 수 있습니다.

관련 에러

• ORA-04031: Shared Pool(또는 large pool, java pool 등)에서 연속된 메모리 공간을 할당할 수 없을 때 발생하는 에러로, ORA-04025와 함께 또는 연이어 발생하는 경우가 많습니다. ORA-04025가 Library Cache의 논리적 한계를 다룬다면, ORA-04031은 SGA 메모리 풀의 물리적 할당 실패를 의미합니다.
• ORA-00604: 재귀 SQL 실행 중 에러가 발생했을 때 나타나며, 내부적으로 ORA-04025를 유발하는 트리거 체인에서 함께 나타날 수 있습니다.
• ORA-07445 / ORA-00600: 극단적인 메모리 부족 상황에서 Library Cache 손상이 발생할 경우 내부 에러로 이어질 수 있으며, Alert Log에서 ORA-04025 이후 이 에러들이 연달아 기록되는 패턴을 주의해야 합니다.

---

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