ORA-01555: snapshot too old / 원인과 해결방법

Oracle DB 를 운영할 때, 자주 일어나는 오류 중에 하나인 "ORA-01555: snapshot too old" 에 대해서 알아보겠습니다.

 

ORA-01555

 

발생 상황 예시

 

롱런닝 쿼리 실행 중

 

대형 테이블을 스캔하거나 복잡한 조인을 수행하는 긴 쿼리가 실행되는 동안,
실행이 시작된 후 데이터가 변경되고 해당 변경 사항이 커밋되면,
쿼리가 참조해야 할 이전 데이터의 스냅샷이 더 이상 유효하지 않게 됩니다.

 

대량 데이터 처리 중

 

대량의 데이터를 처리하거나 업데이트하는 배치 작업을 수행할 때,
이러한 작업이 긴 시간 동안 실행되고,
동시에 다른 트랜잭션이 데이터를 변경하고 커밋하면,
필요한 이전 버전의 데이터를 롤백 세그먼트에서 찾을 수 없게 됩니다.

 

해결 방안 및 명령어

 

롤백 세그먼트 크기 조정

#UNDO 테이블스페이스 크기 조정
ALTER DATABASE DATAFILE 'undo_tablespace_file_path' RESIZE 2G;

 

이 명령어는 UNDO 테이블스페이스의 데이터 파일 크기를 조정합니다.

undo_tablespace_file_path는 UNDO 데이터 파일의 실제 경로로 대체해야 합니다.

크기(2G)는 필요에 따라 조정할 수 있습니다.

#UNDO 테이블스페이스 추가
ALTER TABLESPACE undo_tablespace_name ADD DATAFILE 'new_datafile_path' SIZE 500M;

추가적인 UNDO 데이터 파일을 UNDO 테이블스페이스에 추가합니다.

undo_tablespace_name은 UNDO 테이블스페이스의 이름이고,
new_datafile_path는 새 데이터 파일의 경로입니다.

파일 크기(500M) 역시 필요에 따라 조정 가능합니다.

 

쿼리 최적화

#Explain Plan 사용
EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE conditions;

이 명령어는 주어진 쿼리의 실행 계획을 분석하는 데 사용됩니다.
쿼리의 성능을 평가하고 최적화할 수 있는 방법을 찾는 데 도움이 됩니다.

 

커밋 빈도 조정

 

대량 데이터 처리 작업을 더 작은 트랜잭션으로 분할하는 것은 SQL 또는 PL/SQL 스크립트의 로직 변경을 통해 이루어집니다.
예를 들어, 반복문 내에서 일정량의 데이터 처리 후 COMMIT;을 실행하는 방식으로 조정할 수 있습니다.

 

자동 UNDO 관리 사용

#자동 UNDO 관리 설정
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=BOTH;

이 명령어는 시스템 수준에서 UNDO 관리를 자동으로 설정합니다. AUTO는 데이터베이스가 자동으로 UNDO 공간을 관리하게 합니다.

 

---

결론

"ORA-01555: snapshot too old" 오류에 대처하는 것은 적절한 데이터베이스 관리와 성능 최적화 전략을 통해 이루어집니다.

위의 명령어와 방법들을 통해 이 오류를 효과적으로 예방하고 해결할 수 있습니다.

데이터베이스의 안정성과 성능을 유지하기 위해서는 이러한 관리 기법들을 정기적으로 검토하고 적용하는 것이 중요합니다.