ORA-01555 Snapshot Too Old 에러, UNDO_RETENTION부터 쿼리 튜닝까지: 완벽 해결책

ORA-01555 Snapshot Too Old 에러, UNDO_RETENTION부터 쿼리 튜닝까지: 완벽 해결책

 

ORA-01555 Snapshot Too Old 에러는 Oracle 데이터베이스에서 발생하는 가장 일반적이면서도 복잡한 오류 중 하나로, UNDO_RETENTION 설정 최적화와 체계적인 쿼리 튜닝을 통해 완전히 해결할 수 있습니다.

---

Oracle ORA-01555 에러의 핵심 개념과 발생 원인

 

ORA-01555 에러는 장시간 실행되는 쿼리가 오래된 버전의 데이터 블록에 접근하려 할 때, 필요한 언두 데이터가 새로운 트랜잭션에 의해 덮어쓰여진 경우에 발생합니다.

Oracle의 읽기 일관성(Read Consistency) 메커니즘은 쿼리가 시작된 시점의 데이터 스냅샷을 유지하기 위해 UNDO 세그먼트를 사용합니다.

쿼리가 실행되는 동안 다른 세션에서 데이터를 수정하면, Oracle은 UNDO 데이터를 사용하여 쿼리 시작 시점의 원본 데이터를 재구성합니다.

하지만 UNDO 테이블스페이스는 유한한 자원이기 때문에, 새로운 트랜잭션이 필요한 공간을 확보하기 위해 기존의 UNDO 데이터를 덮어쓸 수 있습니다.

---

ORA-01555 해결법의 주요 원인 분석

1. 부족한 UNDO_RETENTION 설정

UNDO_RETENTION 파라미터는 커밋된 UNDO 데이터를 보존해야 하는 최소 시간을 초 단위로 지정합니다.

기본값은 900초(15분)이지만, 긴 쿼리 오류 방지를 위해서는 최장 실행 쿼리 시간의 1.5배 이상으로 설정하는 것이 권장됩니다.

 

2. 작은 UNDO 테이블스페이스 크기

UNDO_RETENTION이 적절하게 설정되어 있어도 UNDO 테이블스페이스가 너무 작으면 Oracle이 만료되지 않은 UNDO 데이터를 덮어쓸 수 밖에 없습니다.

3. 잘못된 쿼리 패턴

긴 쿼리 오류 방지를 위해서는 다음과 같은 쿼리 패턴을 피해야 합니다:

• 커서 루프에서 행별 FETCH 처리
• 대용량 테이블의 전체 스캔
• 적절한 인덱스가 없는 JOIN 연산

---

Oracle UNDO_RETENTION 설정방법과 최적화

UNDO_RETENTION 동적 설정

-- 현재 UNDO_RETENTION 값 확인
SELECT value FROM v$parameter WHERE name = 'undo_retention';

-- UNDO_RETENTION 1시간(3600초)로 설정
ALTER SYSTEM SET undo_retention = 3600 SCOPE = MEMORY;

-- 영구 적용을 위한 설정
ALTER SYSTEM SET undo_retention = 3600 SCOPE = BOTH;

UNDO 테이블스페이스 자동 확장 설정

-- UNDO 테이블스페이스 자동 확장 활성화
ALTER TABLESPACE undotbs1 
  MODIFY DATAFILE 'undotbs01.dbf' 
  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

최적 UNDO_RETENTION 값 계산

최적의 UNDO_RETENTION 시간을 계산하기 위해서는 다음 공식을 사용할 수 있습니다

UNDO RETENTION TIME = (UNDO SIZE) / (DB BLOCK SIZE × UNDO BLOCK PER SECOND)

 

실제 계산을 위한 SQL

SELECT 
  d.undo_size/(1024*1024) as UNDO_SIZE_MB,
  SUBSTR(e.value,1,25) as CURRENT_UNDO_RETENTION,
  (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) as NEEDED_UNDO_SIZE_MB
FROM 
  (SELECT SUM(a.bytes) undo_size 
   FROM v$datafile a, v$tablespace b, dba_tablespaces c 
   WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' 
   AND b.name = c.tablespace_name AND a.ts# = b.ts#) d,
  v$parameter e,
  v$parameter f,
  (SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec 
   FROM v$undostat) g
WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

---

Oracle 언두 테이블스페이스 확장 전략

자동 확장 vs 고정 크기 비교표

구분	자동 확장	고정 크기
장점	유연한 공간 관리, ORA-01555 감소	예측 가능한 성능, 공간 제어
단점	디스크 공간 부족 위험	주기적 크기 조정 필요
권장 환경	개발/테스트 환경	운영 환경
모니터링	디스크 사용량 집중	UNDO 사용률 집중

UNDO 테이블스페이스 모니터링 쿼리

-- UNDO 사용률 모니터링
SELECT 
  tablespace_name,
  file_name,
  autoextensible,
  bytes/1024/1024 AS SIZE_MB,
  maxbytes/1024/1024 AS MAX_SIZE_MB
FROM dba_data_files 
WHERE tablespace_name LIKE 'UNDO%';

-- UNDO 통계 확인
SELECT 
  begin_time,
  end_time,
  undoblks,
  unxpstealcnt,
  ssolderrcnt
FROM v$undostat 
ORDER BY begin_time DESC;

---

롱 러닝 쿼리 튜닝과 읽기 일관성 유지

쿼리 최적화 기법

1. 적절한 인덱스 활용

-- 실행 계획 확인
EXPLAIN PLAN FOR 
SELECT * FROM large_table WHERE date_column BETWEEN sysdate-30 AND sysdate;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 필요한 인덱스 생성
CREATE INDEX idx_large_table_date ON large_table(date_column);

 

2. 배치 처리 최적화

-- BULK COLLECT 사용으로 성능 향상
DECLARE
  TYPE emp_array_type IS TABLE OF employees%ROWTYPE;
  emp_array emp_array_type;
  CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor BULK COLLECT INTO emp_array LIMIT 1000;

    -- 배치 처리 로직
    FORALL i IN 1..emp_array.COUNT
      INSERT INTO employees_backup VALUES emp_array(i);

    EXIT WHEN emp_cursor%NOTFOUND;
  END LOOP;
  CLOSE emp_cursor;
  COMMIT;
END;
/

 

3. 파티션 활용

-- 날짜 기반 파티셔닝으로 스캔 범위 축소
CREATE TABLE sales_partitioned (
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2024_q1 VALUES LESS THAN (DATE '2024-04-01'),
  PARTITION p_2024_q2 VALUES LESS THAN (DATE '2024-07-01'),
  PARTITION p_2024_q3 VALUES LESS THAN (DATE '2024-10-01'),
  PARTITION p_2024_q4 VALUES LESS THAN (DATE '2025-01-01')
);

---

LOB 관련 ORA-01555 특별 대응법

LOB 세그먼트와 관련된 ORA-01555 에러는 일반적인 UNDO 관리와는 다른 메커니즘을 사용하므로 별도의 접근법이 필요합니다.

LOB RETENTION 설정

Oracle 11g 이전 버전 (PCTVERSION 사용)

-- 기존 LOB 세그먼트 PCTVERSION 확인
SELECT table_name, column_name, pctversion, retention 
FROM dba_lobs 
WHERE table_name = 'YOUR_TABLE_NAME';

-- PCTVERSION 조정
ALTER TABLE your_table 
MODIFY LOB (lob_column) (PCTVERSION 20);

 

Oracle 11g 이후 버전 (RETENTION 사용)

-- LOB RETENTION 설정
ALTER TABLE your_table 
MODIFY LOB (lob_column) (RETENTION);

-- UNDO_RETENTION 변경사항을 LOB에 적용하기 위한 임시 변환
ALTER SYSTEM SET undo_retention = 7200;
ALTER TABLE your_table MODIFY LOB (lob_column) (PCTVERSION 10);
ALTER TABLE your_table MODIFY LOB (lob_column) (RETENTION);

SecureFile LOB 최적화

-- SecureFile LOB으로 변환하여 자동 RETENTION 관리
ALTER TABLE your_table 
MODIFY LOB (lob_column) (
  STORE AS SECUREFILE (
    RETENTION AUTO,
    NOCACHE,
    DISABLE STORAGE IN ROW
  )
);

---

Datapump 중 오류 해결 및 예방

Datapump Export 최적화

# 병렬 처리로 Export 시간 단축
expdp userid/password@database \
  tables=large_table \
  directory=DATA_PUMP_DIR \
  dumpfile=export_%U.dmp \
  parallel=4 \
  cluster=N

# 압축 사용으로 I/O 부하 감소
expdp userid/password@database \
  full=Y \
  directory=DATA_PUMP_DIR \
  dumpfile=full_export.dmp \
  compression=ALL

 

Export 전 사전 준비사항

-- UNDO 사용량 모니터링 활성화
ALTER SYSTEM SET timed_statistics = TRUE;

-- 대용량 테이블 통계 업데이트
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', CASCADE=>TRUE);

-- Export 전 UNDO_RETENTION 임시 증가
ALTER SYSTEM SET undo_retention = 10800; -- 3시간

---

실시간 모니터링과 예방 전략

자동 알림 시스템 구축

-- UNDO 사용률 85% 초과 시 알림
CREATE OR REPLACE PROCEDURE check_undo_usage
IS
  v_undo_usage NUMBER;
BEGIN
  SELECT ROUND((used_space/total_space)*100, 2) INTO v_undo_usage
  FROM (
    SELECT 
      SUM(bytes) total_space,
      SUM(CASE WHEN status = 'ACTIVE' THEN bytes ELSE 0 END) used_space
    FROM dba_undo_extents
  );

  IF v_undo_usage > 85 THEN
    -- 알림 로직 (이메일, SMS 등)
    DBMS_OUTPUT.PUT_LINE('UNDO 사용률 경고: ' || v_undo_usage || '%');
  END IF;
END;
/

-- 스케줄러로 주기적 실행
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'UNDO_MONITOR_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN check_undo_usage; END;',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled => TRUE
  );
END;
/

성능 기준선 설정

-- 기준선 UNDO 통계 수집
CREATE TABLE undo_baseline AS
SELECT 
  SYSDATE baseline_date,
  MAX(undoblks) max_undo_blocks,
  AVG(undoblks) avg_undo_blocks,
  MAX(unxpstealcnt) max_steal_count
FROM v$undostat
WHERE begin_time >= SYSDATE - 7;

---

환경별 맞춤 설정 가이드

개발 환경 설정

-- 개발 환경: 유연성 중심
ALTER SYSTEM SET undo_retention = 1800; -- 30분
ALTER TABLESPACE undotbs1 
  MODIFY DATAFILE 'undotbs01.dbf' 
  AUTOEXTEND ON NEXT 50M MAXSIZE 2G;

운영 환경 설정

-- 운영 환경: 안정성 중심
ALTER SYSTEM SET undo_retention = 7200; -- 2시간
ALTER TABLESPACE undotbs1 
  MODIFY DATAFILE 'undotbs01.dbf' 
  AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

-- RETENTION GUARANTEE 활성화 (중요한 시스템에서만)
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

 

배치 처리 환경 최적화

-- 야간 배치 전 UNDO 파라미터 조정
ALTER SYSTEM SET undo_retention = 14400; -- 4시간

-- 배치 완료 후 원복
ALTER SYSTEM SET undo_retention = 3600;

---

문제 해결 체크리스트

즉시 대응 단계

1. 현재 UNDO 상태 확인

   SELECT tablespace_name, status, 
          bytes/1024/1024 AS size_mb,
          maxbytes/1024/1024 AS max_size_mb
   FROM dba_data_files 
   WHERE tablespace_name LIKE 'UNDO%';

2. 활성 트랜잭션 모니터링

   SELECT s.sid, s.serial#, s.username, t.used_ublk
   FROM v$session s, v$transaction t
   WHERE s.saddr = t.ses_addr
   ORDER BY t.used_ublk DESC;

3. 임시 UNDO_RETENTION 증가

   ALTER SYSTEM SET undo_retention = 10800; -- 3시간

근본 원인 분석

1. ORA-01555 발생 빈도 확인

   SELECT 'ORA-01555 에러 발생 횟수: ' || SUM(ssolderrcnt) error_count
   FROM v$undostat;

2. 쿼리 실행 시간 분석

   SELECT sql_id, elapsed_time/1000000 elapsed_seconds
   FROM v$sql
   WHERE elapsed_time > 1800000000 -- 30분 이상
   ORDER BY elapsed_time DESC;

장기 해결 방안

1. 쿼리 튜닝 우선순위 선정
2. UNDO 테이블스페이스 크기 재검토
3. 배치 작업 스케줄 최적화
4. 모니터링 시스템 강화

---

추가 참고 자료

Oracle Database 관리와 관련된 더 자세한 정보는 다음 공식 문서를 참고하시기 바랍니다:

• Oracle Database Administrator's Guide
• Oracle Database Performance Tuning Guide
• Oracle Database Backup and Recovery User's Guide

ORA-01555 에러는 복잡한 원인을 가지고 있지만, 체계적인 접근과 적절한 모니터링을 통해 예방 가능한 문제입니다.

무엇보다 중요한 것은 환경에 맞는 UNDO_RETENTION 설정과 지속적인 성능 모니터링입니다.

---

마무리

 

ORA-01555 Snapshot Too Old 에러 해결의 핵심은 적절한 UNDO_RETENTION 설정, 충분한 UNDO 테이블스페이스 크기 확보, 그리고 효율적인 쿼리 튜닝에 있습니다. 이러한 요소들을 종합적으로 관리하면 안정적인 Oracle 데이터베이스 운영이 가능합니다.

---

같이 읽으면 좋은 글

ORA-01652: Oracle TEMP 공간 부족 에러 원인과 해결 방법 가이드

TNS-12154: Oracle 데이터베이스 접속 오류 원인과 실전 해결법 가이드

ORA-02292: 오라클 무결성 제약조건 위배(자식 레코드 존재) 에러 완전 정복

ORA-01017: 사용자명/비밀번호 불일치 오류 원인과 해결법 – Oracle 접속 장애 실전 트러블슈팅

오라클 ORA-00018: 최대 세션 수를 초과했습니다

ORA-12519 에러 원인과 해결법 – Oracle Listener 에러 실전 트러블슈팅 가이드