ORA-01555: snapshot too old 에러 해결

1. 문제 상황

최근 개발하던 시스템의 오픈을 앞두면서 운영 로그를 어느 때보다 신경 써서 모니터링하고 있다. 그전까진 나를 비롯한 팀원들 그리고 소수의 공사 인원들만 접근해서 별다른 특이사항이 없었지만, 근래에 각 지사 인원의 시스템 교육 기간이 시작되고 이용자가 늘어나자 개발 단계에서 발생하지 않았던 에러들이 하나 둘 발생하기 시작했다. 오늘은 그중 해결하면서 가장 애먹었던 에러를 해결하는 과정을 기록하고자 한다.

 

현재 우리 시스템은 한 대의 WAS 컨테이너에서 postgres 1대, oracle 2대의 데이터베이스 서버를 혼용하여 사용 중인데, WAS 컨테이너에서 오라클 DB에 접근하는 쿼리를 실행하던 중  간혈적으로 ORA-01555: snapshot too old 에러가 발생하는 것을 확인할 수 있었다.

 

---

 

2. 해결 과정

우선, 검색을 통해 해당 에러는 장시간 실행되는 쿼리가 필요로 하는 Undo 데이터가 이미 덮어써져서(overwritten) 읽을 수 없게 된 경우 발생한다는 것을 알게 되었다. (Undo는 변경 전 데이터의 백업을 말하며, 트랜잭션의 롤백이나 읽기 일관성 위해 일정 시간 동안 DB에서 보관하는 것을 의미한다.)

 

이에 찾아본 ORA-01555 에러의 일반적인 해결방법은 다음과 같았다.

 

1. 장시간 실행되는 쿼리가 있는지 확인하고 최적화한다. 

2. oracle db의 UNDO 관련 설정을 조정한다.

 

2.1. 장시간 실행되는 쿼리 확인

그동안 대부분의 에러 로그는 서버 관련 오류가 많았기에 우선적으로 에러 로그의 앞에 오래 실행되고 있는 쿼리가 있는지 확인하였다. 하지만 사용하고 있는 쿼리들은 모두 채 0.5초도 걸리지 않는 응답시간이 빠른 쿼리들이었고, update 작업이 많아 데이터가 자주 변경될 여지도 많은 쿼리들도 아니었다. 또한, 특정 쿼리에 의해 발생하는 현상도 아니었다. 따라서, 자연스럽게 다음 해결방법을 찾아보게 되었다.

 

2.2. db의 UNDO 관련 설정 조정

확인해야될 UNDO 관련 설정은 다음과 같다.

• UNDO 테이블스페이스 크기가 충분한지 (백업 데이터를 보관할 저장공간이 충분한지)
• UNDO Retention 시간이 충분한지 (UNDO 데이터를 최소 얼마나 오래 보관할지)

 

V$UNDOSTAT은 oracle에서 UNDO 테이블스페이스의 사용 통계를 10분 간격으로 기록하는 동적 성능 뷰를 가리키는데, 쿼리를 통해 다음의 수치들을 확인할 수 있다.

• BEGIN_TIME -- 통계 수집 시작 시간
• END_TIME -- 통계 수집 종료 시간
• MAXQUERYLEN -- 이 기간 동안 가장 긴 쿼리 실행 시간 (초)
• MAXQUERYSQLID -- 가장 긴 쿼리의 SQL 
• SSOLDERRCNT -- ORA-01555: snapshot too old 에러의 발생 횟수 
• NOSPACEERRCNT -- ORA-30036: unable to extend segment 에러의 발생 횟수
• UNXPSTEALCNT -- Retention 시간 안 된 UNDO가 재사용된 횟수 (비정상)
• EXPSTEALCNT -- Retention 시간 지난 UNDO가 재사용된 횟수 (정상)

 

사용한 쿼리는 다음과 같다.

SELECT BEGIN_TIME,
       MAXQUERYLEN,
       MAXQUERYID,
       SSOLDERRCNT,
       UNXPSTEALCNT
FROM V$UNDOSTAT
ORDER BY BEGIN_TIME DESC;

 

확인 결과, NOSPACEERRCNT가 집계된 경우는 없었지만, SSOLDERRCNT가 지속적으로 집계된 것을 확인할 수 있었다. 따라서, UNDO 테이블스페이스 저장 공간의 문제가 아닌 UNDO Retention의 문제임을 알 수 있었다.

 

SELECT BEGIN_TIME,
       MAXQUERYLEN,
       MAXQUERYID
FROM V$UNDOSTAT
WHERE SSOLDERRCNT > 0 
ORDER BY BEGIN_TIME DESC;

SELECT 
    *
FROM V$PARAMETER
WHERE NAME = 'undo_retention';

 

이번 조회 결과를 통해 특정 쿼리들의 실행시간이 2000 ~ 3000초에 육박하는 것을 확인할 수 있었는데, 반면 시스템에 설정된 UNDO Retention 시간은 900초에 불과하였다. 그래서 대체 어떤 쿼리가 이렇게 오래 걸리는 건지 확인해 보았는데.. 

 

SELECT longname FROM javasnm$ WHERE short = :1

SELECT 1 FROM obj$ WHERE name='DBA_QUEUE_SCHEDULES'

 

이처럼 웹 어플리케이션에서 사용하는 쿼리가 아닌 Oracle의 내부 시스템 딕셔너리 조회 쿼리가 ORA-01555 에러를 유발하고 있었다. 문제가 되는 쿼리가 만약 애플리케이션에서 사용되는 쿼리를 최적화하여 해결할 수 있는 것이 아니었기에, UNDO Retention 수치를 다음과 같이 이전보다 높이기로 하였다.

 

ALTER SYSTEM 
SET UNDO_RETENTION = 3600 
SCOPE=BOTH;

(SCOPE=BOTH는 현재 실행 중인 인스턴스와 서버 파라미터 파일 둘다에 적용하는 옵션을 의미한다.)

 

---

 

3. 결과

다행히 해당 옵션을 적용한지 2주가 지난 지금까지 더 이상 V$UNDOSTAT에서 ORA-01555 에러는 집계되지 않았다.

 

그동안 만났던 대부분의 에러는 개발자의 실수나 무지에서 비롯된 경우가 많았다. 하지만 이번처럼 순수하게 인프라 문제로 인한 에러는 처음 겪어보는 상황이라 상당히 삽질을 많이 하였다. 그래도 나에게 애플리케이션 수준이 아닌 전체 시스템 수준에서 문제 원인을 파악하고 해결하는 시야를 넓힐 수 있었던 여러모로 뜻깊은 경험이었다.