[오라클 에러] ORA-04031 unable to allocate shared memory - 5가지 원인과 해결방법 (단편화 진단 포함)
테스트 환경: Oracle 11g / 12c / 19c / 21c
새벽 3시. 운영 DB 모니터링 알람이 울립니다. 알람 내용은 "ORA-04031: unable to allocate XXXX bytes of shared memory". 애플리케이션이 멈췄고, 신규 접속도 안 됩니다. DB가 사실상 frozen 상태입니다.
ORA-04031은 DBA가 운영 환경에서 가장 두려워하는 에러 중 하나입니다. 단순히 "메모리 부족"이라고 생각하면 절대 풀리지 않습니다. SGA를 64GB로 늘렸는데도 며칠 후 재발하는 경우가 흔합니다.
이번 글에서는 ORA-04031의 진짜 정체를 짚고, 단편화와 단순 부족을 구분해서 진단하는 방법, 임시 처치와 근본 해결까지 정리했습니다.
급하게 운영 DB를 살려야 한다면 긴급 응급 처치부터 보세요.
에러 메시지 전문과 해석
ORA-04031: unable to allocate 4096 bytes of shared memory
("shared pool","SELECT * FROM orders WHERE...","sql area","KGL heap")
이 메시지는 진단의 시작점입니다. 4가지 정보가 들어있어요.
항목 위치 의미
| 요청 크기 | 4096 bytes | 오라클이 할당하려던 메모리 크기 |
| 메모리 풀 | "shared pool" | 어느 메모리 영역에서 실패했는지 |
| 객체 타입 | "SELECT * FROM..." | 무엇을 위한 할당이었는지 (SQL 텍스트 일부) |
| 힙 타입 | "sql area","KGL heap" | 내부 메모리 구조 (KGL = Kernel Generic Library) |
메모리 풀이 어디인지가 가장 중요합니다. 풀별로 원인과 해결법이 완전히 다릅니다.
풀 용도 흔한 발생 시점
| shared pool | SQL/PLSQL 파싱, 라이브러리 캐시 | OLTP 운영 중 (가장 흔함) |
| large pool | RMAN 백업, 병렬 쿼리 | 백업 작업 중 |
| java pool | Java 저장 프로시저 | Java 기반 작업 |
| streams pool | GoldenGate, Streams 복제 | 복제 환경 |
ORA-04031의 진짜 본질 - 단편화 vs 단순 부족 ★
다른 블로그가 잘 다루지 않는 부분이지만, 이게 ORA-04031 진단의 핵심입니다.
흔한 오해
"메모리 부족이니까 SGA를 늘리면 된다"
이건 절반만 맞습니다. 실제로는 메모리에 여유 공간이 있는데도 ORA-04031이 발생하는 경우가 매우 많습니다.
단편화의 비유
shared pool을 책장이라고 생각해 보세요.
- 책장에 빈 공간이 총 1GB 남아있지만
- 그 1GB가 1MB 단위 1024개로 쪼개져 있고
- 새로 들어와야 할 책이 500MB짜리라면?
빈 공간 총량은 충분하지만 연속된 큰 공간이 없어서 책을 못 꽂습니다. 이게 단편화입니다.
ORA-04031의 70% 이상이 이 패턴입니다. SGA를 늘려도 일정 시간 후 다시 단편화가 진행되어 재발합니다.
단편화의 진짜 원인
대부분은 하드 파싱(Hard Parse) 폭증입니다.
-- 리터럴 SQL (매번 새로운 SQL로 인식됨 - 단편화 주범)
SELECT * FROM orders WHERE customer_id = 1001;
SELECT * FROM orders WHERE customer_id = 1002;
SELECT * FROM orders WHERE customer_id = 1003;
-- ... 매 customer_id마다 별도 SQL로 저장됨
-- 바인드 변수 SQL (한 번만 파싱됨 - 단편화 방지)
SELECT * FROM orders WHERE customer_id = :cust_id;
-- 모든 customer_id가 이 한 SQL로 처리됨
리터럴 SQL이 초당 수백~수천 개씩 발생하면 shared pool에 매번 새로운 chunk가 할당되고, 오래된 것은 제거되면서 메모리 공간이 잘게 쪼개집니다.
긴급 응급 처치 (1분 안에)
운영 DB가 멈춰서 당장 살려야 한다면 다음을 먼저 시도하세요.
Step 1: shared pool 플러시 (가장 빠른 임시 해결)
ALTER SYSTEM FLUSH SHARED_POOL;
이 명령으로 shared pool의 SQL 캐시가 비워지면서 단편화가 해소됩니다. 대부분의 ORA-04031이 즉시 풀립니다.
주의: 이건 응급 처치일 뿐 근본 해결이 아닙니다. 같은 원인이 남아있으면 몇 시간~며칠 안에 재발합니다.
Step 2: 접속도 안 되면 인스턴스 재기동
플러시 명령조차 실행이 안 될 정도로 심한 상태라면 DB 재기동이 필요합니다.
# SQL*Plus로 sysdba 접속
sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
재기동은 마지막 수단입니다. 가능하면 플러시로 해결하세요.
Step 3: 근본 원인 진단 시작
응급 조치 후 반드시 다음 진단을 진행해야 합니다. 그렇지 않으면 무조건 재발합니다.
원인 1: 하드 파싱 폭증 (가장 흔함, 60~70%)
리터럴 SQL이 너무 많아서 shared pool이 폭증하는 케이스입니다.
진단 방법
1) 라이브러리 캐시 적중률 확인
SELECT namespace,
gethitratio AS hit_ratio,
pinhitratio AS pin_hit_ratio,
reloads,
invalidations
FROM v$librarycache
WHERE namespace IN ('SQL AREA', 'TABLE/PROCEDURE');
hit_ratio가 0.95(95%) 이하면 하드 파싱이 많은 것입니다.
2) 하드 파싱 발생 횟수 확인
SELECT name, value
FROM v$sysstat
WHERE name IN ('parse count (hard)', 'parse count (total)', 'execute count');
(parse count (hard) / parse count (total)) × 100이 5%를 넘으면 하드 파싱 비율이 너무 높습니다.
3) 단편화된 SQL TOP 발견
SELECT sql_text, executions, parse_calls,
sharable_mem, persistent_mem
FROM v$sqlarea
WHERE parse_calls > 100
AND executions < 10
ORDER BY sharable_mem DESC
FETCH FIRST 20 ROWS ONLY;
executions가 적은데 parse_calls가 많은 SQL은 매번 다른 SQL로 인식되는 것입니다. 단편화의 주범입니다.
해결 방법
방법 1: 바인드 변수 사용 (근본 해결, 권장)
애플리케이션 코드에서 리터럴을 바인드 변수로 변경합니다.
// ❌ 리터럴
String sql = "SELECT * FROM orders WHERE customer_id = " + customerId;
// ✅ 바인드 변수
String sql = "SELECT * FROM orders WHERE customer_id = ?";
ps.setInt(1, customerId);
방법 2: CURSOR_SHARING 파라미터 (임시 해결)
코드 수정이 불가능한 경우 오라클이 자동으로 리터럴을 바인드 변수로 변환하도록 설정합니다.
ALTER SYSTEM SET CURSOR_SHARING = 'FORCE' SCOPE=BOTH;
값 의미
| EXACT | 기본값. SQL 텍스트가 완전히 같아야 재사용 |
| FORCE | 모든 리터럴을 바인드 변수로 자동 변환 |
| SIMILAR | 11g 이후 deprecated. 사용 금지 |
주의: FORCE 설정은 실행 계획에 영향을 줄 수 있습니다. 운영 환경에 적용 전 충분한 테스트 필수입니다.
원인 2: SGA 크기 자체가 부족
데이터베이스 워크로드 대비 SGA 크기가 실제로 부족한 경우입니다. ORA-04031의 20~30%에 해당합니다.
진단 방법
1) 현재 SGA 사용 상황
SELECT pool, name, ROUND(bytes/1024/1024, 2) AS mb
FROM v$sgastat
WHERE pool IS NOT NULL
ORDER BY pool, bytes DESC;
2) shared pool 자유 메모리 확인
SELECT name, ROUND(bytes/1024/1024, 2) AS free_mb
FROM v$sgastat
WHERE name = 'free memory';
총 shared pool의 10% 이상 자유 공간이 있어야 안정적입니다.
3) 오라클 자동 조언 활용
SELECT shared_pool_size_for_estimate AS pool_mb,
estd_lc_size AS estd_lib_cache_mb,
estd_lc_time_saved_factor,
estd_lc_load_time_factor
FROM v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;
estd_lc_time_saved_factor가 1에 가까운 첫 크기가 권장값입니다.
해결 방법
ASMM 환경 (자동 메모리 관리)
-- sga_target 증가
ALTER SYSTEM SET sga_target = 8G SCOPE=BOTH;
-- 또는 메모리 자동 관리 (11g+)
ALTER SYSTEM SET memory_target = 12G SCOPE=BOTH;
수동 메모리 관리 환경
ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;
SGA 변경은 SPFILE 사용 시 즉시 반영됩니다. PFILE 사용 시 재기동 필요.
원인 3: large pool / java pool / streams pool 부족
shared pool이 아닌 다른 풀에서 에러가 발생한 경우입니다. 에러 메시지를 다시 확인하세요.
ORA-04031: unable to allocate XX bytes of shared memory ("large pool",...)
자주 발생하는 시나리오
- large pool: RMAN 백업 중, 병렬 쿼리(PARALLEL 힌트) 사용 중
- java pool: Java 저장 프로시저 호출 시
- streams pool: GoldenGate, Oracle Streams 복제 환경
진단 방법
-- 풀별 사용량 확인
SELECT pool, ROUND(SUM(bytes)/1024/1024, 2) AS total_mb
FROM v$sgastat
WHERE pool IS NOT NULL
GROUP BY pool;
-- 풀별 자유 메모리
SELECT pool, name, ROUND(bytes/1024/1024, 2) AS mb
FROM v$sgastat
WHERE name = 'free memory';
해결 방법
-- large pool 증가 (RMAN/병렬 쿼리)
ALTER SYSTEM SET large_pool_size = 512M SCOPE=BOTH;
-- java pool 증가
ALTER SYSTEM SET java_pool_size = 256M SCOPE=BOTH;
-- streams pool 증가 (복제 환경)
ALTER SYSTEM SET streams_pool_size = 512M SCOPE=BOTH;
ASMM 환경(sga_target 설정됨)에서는 오라클이 풀 간 메모리를 자동 조정하지만, 명시적 최소값을 지정하면 보호됩니다.
원인 4: 메모리 누수 (오라클 버그 또는 특정 패키지)
흔하지는 않지만 특정 버전·패치에서 메모리 누수 버그가 알려진 경우가 있습니다.
증상
- 운영 시작 직후엔 안정적
- 시간이 지날수록 free memory가 점진적으로 감소
- 특정 작업 또는 패키지 호출 후 메모리 회수 안 됨
진단 방법
1) 시간대별 자유 메모리 추이 모니터링
-- AWR 스냅샷 기반 추이 (Diagnostics Pack 필요)
SELECT snap_id,
TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
ROUND(bytes/1024/1024, 2) AS free_mb
FROM dba_hist_sgastat sg, dba_hist_snapshot s
WHERE sg.snap_id = s.snap_id
AND sg.name = 'free memory'
AND sg.pool = 'shared pool'
ORDER BY snap_id DESC
FETCH FIRST 48 ROWS ONLY;
자유 메모리가 시간에 따라 단조 감소하면 메모리 누수 의심.
2) 특정 객체 메모리 점유 확인
SELECT name, type, sharable_mem
FROM v$db_object_cache
WHERE sharable_mem > 1048576 -- 1MB 이상
ORDER BY sharable_mem DESC
FETCH FIRST 20 ROWS ONLY;
해결 방법
- 현재 PSU/RU 패치 적용 상태 확인 (Oracle Support Doc ID 146599.1)
- 특정 패키지가 의심된다면 DBMS_SHARED_POOL.KEEP으로 PIN
EXEC DBMS_SHARED_POOL.KEEP('SCHEMA.PACKAGE_NAME', 'P');
- My Oracle Support의 자동 진단 도구(AHF) 활용
19c 이상에서는 AHF(Autonomous Health Framework)가 ORA-04031 자동 진단을 지원합니다.
tfactl diagcollect -ora-04031
원인 5: session_cached_cursors 설정 과다 (드물지만 강력함)
이 케이스는 다른 한국어 블로그에서 거의 다루지 않는 영역입니다.
무엇이 문제인가
session_cached_cursors가 너무 높게 설정되면, 각 세션이 캐시하는 커서가 누적되어 shared pool 내 KGLH0 영역이 폭증합니다.
기본값은 50인데, 일부 환경에서 500~1000으로 설정한 경우 ORA-04031의 원인이 됩니다.
진단 방법
SHOW PARAMETER session_cached_cursors
-- KGLH0 영역 사용량 확인
SELECT name, ROUND(bytes/1024/1024, 2) AS mb
FROM v$sgastat
WHERE name = 'KGLH0'
AND pool = 'shared pool';
KGLH0가 SGA stat에서 TOP 5 안에 들어가면 의심하세요.
해결 방법
-- 적정 수준으로 조정 (보통 50~200)
ALTER SYSTEM SET session_cached_cursors = 100 SCOPE=SPFILE;
-- 재기동 필요
재발 방지 - 운영 환경 모니터링 (★ 실무 핵심)
ORA-04031은 한 번 발생하면 운영 사고로 직결되므로, 사전 모니터링이 필수입니다.
일일 점검 쿼리
-- 1. 풀별 자유 메모리 비율
WITH pool_stats AS (
SELECT pool,
SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) AS free_bytes,
SUM(bytes) AS total_bytes
FROM v$sgastat
WHERE pool IS NOT NULL
GROUP BY pool
)
SELECT pool,
ROUND(total_bytes/1024/1024, 2) AS total_mb,
ROUND(free_bytes/1024/1024, 2) AS free_mb,
ROUND(free_bytes/total_bytes * 100, 2) AS free_pct
FROM pool_stats
ORDER BY pool;
-- 2. 하드 파싱 비율
SELECT ROUND(
(SELECT value FROM v$sysstat WHERE name = 'parse count (hard)') /
(SELECT value FROM v$sysstat WHERE name = 'parse count (total)') * 100,
2) AS hard_parse_pct
FROM dual;
자유 메모리 비율이 10% 미만이거나 하드 파싱 비율이 5%를 넘으면 경고 알림을 발송하도록 자동화하세요.
권장 운영 표준
항목 권장값 이유
| shared_pool 자유 메모리 | 15% 이상 유지 | 단편화 여유 |
| 하드 파싱 비율 | 5% 미만 | 단편화 방지 |
| cursor_sharing | EXACT (개발 정상), FORCE (레거시 임시) | - |
| session_cached_cursors | 50~200 | KGLH0 폭증 방지 |
| 주기적 shared pool flush | ❌ 권장하지 않음 | 근본 해결 우선 |
그래도 안 풀린다면
위 5가지로도 해결되지 않는 드문 케이스:
- HugePages 설정 누락: Linux 환경에서 SGA가 크면 HugePages 설정이 거의 필수
- NUMA 환경 최적화 미적용: 멀티 소켓 서버에서 메모리 액세스 비효율 발생
- RAC 환경 특화 이슈: 노드 간 라이브러리 캐시 동기화 문제 (gc 대기 이벤트)
- AMM/ASMM 충돌: memory_target과 sga_target 동시 설정 시 예측 불가 동작
이 영역은 Oracle Support와 협업 또는 AHF 자동 진단이 가장 빠른 길입니다.
마무리
ORA-04031은 "메모리 부족"이라는 표면적 메시지와 달리, 실제로는 단편화 문제인 경우가 70% 이상입니다. SGA를 늘리는 것은 임시 대응에 불과하고, 하드 파싱을 줄이는 것이 진짜 해결입니다.
운영 환경에서 ORA-04031이 발생했다면 즉시 ALTER SYSTEM FLUSH SHARED_POOL로 시스템을 복구한 후, 24시간 안에 반드시 근본 원인을 진단하세요. 그렇지 않으면 며칠 안에 더 큰 장애로 돌아옵니다.
가장 좋은 예방은 개발 단계에서 바인드 변수를 표준화하는 것입니다. 운영에 들어간 후 리터럴 SQL을 잡는 건 매우 어렵습니다. 신규 시스템 구축 시 코드 리뷰 항목에 반드시 포함시키세요.
비슷한 케이스를 겪으셨거나, 위 방법으로도 해결되지 않은 상황이 있다면 댓글로 공유해 주세요. 함께 진단해 보겠습니다.