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

[현상]

대량의 데이터를 가져오는 쿼리가있는데 종종 ORA-01555: snapshot tool old 에러가 발생한다는 연락이 왔다.

 

[원인]

snapshot too old에러는 undo segment가 덮어씌워지면서 읽기일관성을 확보하지 못할 때 발생한다.

Update가 발생하면 서버 프로세스가 undo segment를 확보하고 변경 전 내용을 기록한다고 했다.

bbayo.tistory.com/6

SQL실행원리

 

한 transaction이 이 undo segment의 데이터를 읽어와야 할 때 해당 undo segment가 보존되지 못하고 사라지면 (덮어씌워지면) ORA-01555가 발생한다.

 

문제가 되는 쿼리의 수행시간을 물어보니 1300~1500초까지 걸린다고 한다.

 

[해결]

UNDO RETENTION을 확인해보니 900으로 설정되어있어 이를 1800으로 변경해주었다.

--UNDO RETENTION조회
show parameter undo_retention;
SELECT * FROM V$PARAMETER WHERE NAME LIKE '%UNDO_RETENTION%'

--UNDO RETEITON변경
ALTER SYSTEM SET UNDO_RETENTION=1800 [SCOPE=BOTH/MEMORY/SPFILE]

* UNDO_RETENTION: 설정된 시간만큼 Undo segment가 덮어쓰이지않고 보호됨

 

Undo rentention 값을 증가시키면 그 만큼 undo segment를 오래 보관하고 있기 때문에 undo tablespace의 공간도 그에 맞게 늘려줘야 한다.

 

UNDOTBS가 undo retention을 보관할 만큼의 공간이 확보되지 않으면 UNDO_RETENTION에 설정한 시간동안 undo segments 보장되지 않기때문에

UNDOTBS에 autoextend옵션을 걸어주거나 RETENTION을 GUARANTEE상태로 만들어 준다.

--GRARANTEE 상태 조회
select tablespace_name, allocation_type, segment_space_management, contents, retention 
from dba_tablespaces where contents='UNDO';

--RETENTION GUARANTEE 변경
alter tablespace undotbs1 retention guarantee;