[Oracle] ORA-01555 Snapshot too old 에러 원인과 해결 방법

 

[Oracle] ORA-01555 Snapshot too old 에러
원인과 해결 방법

 

 

오라클 ORA-01555 에러

ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 %s 수에 "%s" 이름으로 된 것이 너무 작습니다

ORA-01555: snapshot too old:rollback segment number %s with name "%s" too small

 

위와 같이 메시지가 출력되며 ORA-01555는 데이터 훼손이나 데이터 손실과는 아무 관련이 없습니다. 그런 점에서 안전한 오류이고, 단지 이 오류를 만나는 순간 쿼리를 계속 진행할 수 없습니다. 최근 이 에러를 맞닥뜨리게 되어 원인 및 해결방법을 공유하고자 합니다.

 

 

에러 발생 시나리오

먼저 ORA-01555가 발생할 수 있는 시나리오의 예를 타임라인을 통해 보겠습니다. UNDO 세그먼트, 일관적인 읽기 등의 용어에 대해 익숙치 않은 경우에도 우선 다음 내용을 읽어보시길 바랍니다.

 

블록이 1,000,000개가 있는 테이블이 있다. 이에 대해 느린 쿼리를 실행하고 ORA-01555 에러가 발생하는 타임라인의 예이다.

 

 

시간(분:초)	진행사항
0:00	쿼리 시작
0:01	다른 세션이 블록 1,000,000을 UPDATE 한다. 이 블록에 대한 UNDO 정보는 UNDO 세그먼트에 기록한다.
0:01	이 UPDATE 세션이 COMMIT 한다. 이 세션이 생성한 UNCO 데이터는 아직도 그대로 남아 있지만, 이후로 공간이 필요해지는 순간 언제든 덮어 쓰일 수 있다.
1:00	쿼리 진행이 블록 200,000에 도달했다.
1:01	많은 활동 이 계속 진행되어 그 만큼 많은 UNDO 데이터를 생성 했다.
3:00	쿼리 진행이 블록 600,000에 도달했다.
4:00	UNDO 세그먼트가 덮여 쓰여지기 시작 하고 쿼리를 시작할 때 활성화되어 있던( 트랜잭션이 진행 중이던) 공간을 재사용한다. 특히, 0:01에 블록 1,000,000 에 대한 UPDATE 시 사용되었던 UNDO 세그먼트 공간을 방금 재사용했다.
5:00	쿼리가 블록 1,000,000에 도달했다. 쿼리가 시작한 이후로 그 블록이 변경되었음을 발견한다. 일관적인 읽기를 위해 UNDO 세그먼트로 가서 그 블록에 대한 UNDO 를 찾으려고 시도한다. 이 시점에, 필요한 UNDO 정보가 더 이상 존재하지 않음 을 발견하고 ORA-01555 에러와 함께 쿼리가 실패한다.

 

반응형

 

원인

이 오류의 원인을 한 줄로 정의하면 다음과 같습니다.

 

ORA-01555 에러는 쿼리를 수행하는 동안 발생하는 다른 Transaction들에 의해 UNDO 세그먼트(롤백 세그먼트) 덮어씌워져서 발생한다.

 

 

1. UNDO 세그먼트
- 언두 세그먼트는 첫째, 실패한 트랜잭션을 롤백하고 둘째, 읽기 일관성을 제공하는 두 가지 용도를 가진다. 이를 위해 트랜잭션에 대한 UNDO 정보/UNDO 데이터를 UNDO 세그먼트에 기록한다.
- 이 UNDO 세그먼트는 만료되거나 공간 재사용에 의해 덮어씌워질 여지가 있다.
- 이로 인해 해당 쿼리 결과에 대한 읽기 일관성이 유효하지 않게 되는 것이 ORA-01555 에러의 핵심이다.

 

1-1. 읽기 일관성
읽기 일관성이란 쿼리 결과는 미리 확정돼있고 오라클이 첫 번째 행을 조회하러 가기도 전에 결과가 잘 정의되어 있다는 의미이다. 오라클은 쿼리가 수행된 이후로 변경된 블록을 롤백하기 위해 언두 세그먼트를 사용하여 DB 스냅샷을 일관적인 시점을 기준으로 제공한다. 테이블을 조회하는 모든 쿼리는 읽기 일관성을 이용한다.

 

2. UNDO 기록이 만료되거나 덮어씌워지는 이유
2-1. 만료
- 현재 시점에서 행의 커밋시간을 뺀 시간이 UNDO_RETENTION 보다 크다는 것을 의미

 

2-2. Overwrite
- UNDO 테이블스페이스가 가득 차서 'UNEXPIRED' 언두 레코드가 덮어씌워지고 있는 상황
- 해당 UNDO 테이블스페이스에 대해 RETENTION GUARANTEE 가 활성화 되지 않음
- 쿼리를 실행하는 동안 덮여 쓰여질 가능성이 있는 크기로 언두 세그먼트를 설정한 상태에서 변경될 수도 있는 데이터를 그 쿼리가 액세스할 때

 

2-2. 작업량에 비해 작은 언두 세그먼트
2-3. 쿼리 수행 시간이 오래 걸릴 경우
쿼리를 실행하는 데 장시간이 소요되면 실행하는 동안 다른 트랜잭션에 의해 롤백 세그머트 정보가 덮어 쓰여질 것이므로, 쿼리를 시작한 이후로 생성된 언두 정보가 없어질 수 있다.

2-4. 데이터를 fetch하는 중간중간에 commit(fetch across commit)하도록 프로그래밍했다

 

즉 작업량에 비해 UNDO 세그먼트가 작거나 쿼리 수행 시간이 오래 걸리는 것과 같은 이유로 인해 현재 쿼리를 실행하는 동안 다른 트랜잭션에 의해 UNDO 세그먼트 정보가 Overwrite 되어 읽기 일관성이 유효하지 않아 발생한다고 정리할 수 있습니다.

 

 

 

해결 방법

1. 쿼리 튜닝을 통한 수행 시간 단축
- ORA-01555 오류의 핵심 원인을 방지하는 가장 최선의 방법이며 가장 먼저 시도해야 한다.
- 쿼리 수행 시간 단축은 더 큰 언두 세그먼트에 대한 필요성을 줄여준다.

 

2. 적절한 UNDO 세그먼트 사이즈의 구성
쿼리를 실행하는 동안 덮여 쓰여질 가능성이 있는 크기로 UNDO 세그먼트를 설정한 상태에서 변경될 수도 있는 데이터를 그 쿼리가 액세스한다면, ORA-01555 에러가 발생할 수 있다.

 

2-1. UNDO_RETENTION 파라미터를 적절하게 설정한다
- 가장 오래 수행하는 트랜잭션에 걸리는 시간보다 많게 설정한다.
- V$UNDOSTAT는 장시간 수행되는 쿼리에 걸리는 시간을 확인하는 데 사용될 수 있다.
- 요청한 UNDO_RETENTION을 근거로 필요한 만큼 UNDO 세그먼트 크기를 증가시킬 수 있도록 디스크에 충분한 공간을 확보해야 한다.

 

2-2. 수동 UNDO 관리를 사용할 때는 UNDO 세그먼트를 늘리거나 좀 더 많은 UNDO 세그먼트를 추가한다.
- 그러나 그리 권장할 만한 방법은 아니며, 자동 UNDO 관리가 추천된다.

 

 

저 같은 경우에는 쿼리 튜닝(Hint 추가)를 통해 해당 에러를 해결할 수 있었습니다. 운영중인 시스템에서 어느날 갑자기 UNDO 세그먼트의 사이즈가 변경될 일은 거의 없으니, 갑자기 이 에러가 발생하는 경우에는 우선 쿼리 변경 이력이 있는지 검토하고 이력이 있을 경우 쿼리의 성능 분석 및 튜닝을 먼저 시도해보시기를 추천합니다.