야간에 도는 집계 배치가 하나 있다. 하루치 처리 이력을 읽어서 통계 테이블에 쌓고, 처리한 행에 완료 플래그를 찍는 단순한 잡이다. 평소 2~3시간이면 끝났고 몇 달간 조용했다. 어느 날 아침 출근해 보니 잡이 실패해 있었다.
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_..." too small증상
이상한 점이 세 가지였다. 첫째, 같은 프로시저를 낮에 수동으로 돌리면 멀쩡히 끝난다. 둘째, 야간에도 매번 실패하는 게 아니라 3~4일에 한 번꼴로 터진다. 셋째, 실패 지점이 항상 시작 후 2시간 30분에서 2시간 50분 사이다.
재현이 안 되는 장애의 전형적인 모양새다. 그래서 처음에는 UNDO 테이블스페이스가 작아서 그런가 싶어 크기부터 늘렸다. 다음 주에 또 터졌다.
원인 분석
ORA-01555는 디스크가 부족하다는 에러가 아니다. 읽기 일관성(read consistency)을 지킬 수 없다는 에러다.
오라클은 커서를 연 시점의 SCN을 기준으로 데이터를 보여준다. 커서가 열린 뒤 다른 세션이 그 블록을 바꾸면, 오라클은 UNDO에 남아 있는 이전 이미지를 찾아 그 시점의 모습을 복원해서 넘겨준다. 그런데 그 UNDO 블록이 이미 다른 트랜잭션에게 재사용돼 사라졌다면 복원할 방법이 없다. 그때 나오는 게 1555다.
즉 이 에러의 본질은 커서가 너무 오래 열려 있다는 것이고, 실패 지점이 항상 2시간 반 근처였던 건 우연이 아니었다.
먼저 UNDO 쪽 실적을 확인했다.
select begin_time, end_time, undoblks, maxquerylen, ssolderrcnt
from v$undostat
order by begin_time desc
fetch first 24 rows only;ssolderrcnt가 0이 아닌 구간이 실패 시각과 정확히 겹쳤고, maxquerylen이 9000초를 넘고 있었다. 그런데 undoblks 자체는 여유가 있었다. 공간 문제가 아니라는 확증이었다.
select name, value from v$parameter
where name in ('undo_retention','undo_tablespace','undo_management');UNDO_RETENTION은 900초로 잡혀 있었다. 2시간 반을 도는 커서 입장에서는 없는 것이나 마찬가지다. 게다가 이 값은 약속이 아니라 희망사항이다. 테이블스페이스에 여유가 없으면 오라클은 retention을 무시하고 UNDO를 덮어쓴다. 강제하려면 별도 설정이 필요하다.
alter tablespace undotbs1 retention guarantee;하지만 진짜 범인은 따로 있었다. 배치 코드를 열어 보니 이런 모양이었다.
declare
cursor c1 is select seq_no, amt, work_dt from tx_daily where stat_yn = 'N';
begin
for r in c1 loop
insert into tx_stat (work_dt, amt) values (r.work_dt, r.amt);
update tx_daily set stat_yn = 'Y' where seq_no = r.seq_no;
commit;
end loop;
end;
/fetch across commit. 커서로 읽고 있는 바로 그 테이블을 같은 루프 안에서 수정하고 커밋한다. 커밋할 때마다 내 트랜잭션이 쓴 UNDO는 재사용 대상이 되는데, 정작 아직 살아 있는 c1 커서는 그 UNDO를 필요로 한다. 스스로 자기 발밑을 파고 있었던 셈이다.
자주 커밋해야 UNDO를 아낀다는 통념이 정확히 반대로 작동하는 지점이 여기다. 처리 건수가 늘어나면서 루프가 길어졌고, 임계점을 넘긴 날 터진 것이다.
해결
커밋 주기를 조정하는 선에서 끝내고 싶은 유혹이 있었지만 그건 터지는 날짜를 미루는 것에 불과하다. 루프 자체를 없애는 쪽으로 갔다.
merge into tx_stat t
using (select work_dt, sum(amt) amt from tx_daily where stat_yn = 'N' group by work_dt) s
on (t.work_dt = s.work_dt)
when matched then update set t.amt = t.amt + s.amt
when not matched then insert (work_dt, amt) values (s.work_dt, s.amt);
update tx_daily set stat_yn = 'Y' where stat_yn = 'N';
commit;행 단위 루프가 사라지니 커서 수명 문제도 같이 사라졌다. 부수 효과로 2시간 40분 걸리던 잡이 4분대로 줄었다. 애초에 성능 문제이기도 했던 것이다.
건수가 너무 커서 한 번에 못 도는 경우라면, 최소한 읽는 커서와 쓰는 대상을 분리하는 형태로 가야 한다.
declare
type t_seq is table of number;
v_seq t_seq;
cursor c1 is select seq_no from tx_daily where stat_yn = 'N';
begin
open c1;
loop
fetch c1 bulk collect into v_seq limit 5000;
exit when v_seq.count = 0;
forall i in 1 .. v_seq.count
update tx_daily set stat_yn = 'Y' where seq_no = v_seq(i);
commit;
end loop;
close c1;
end;
/이 형태도 엄밀히 말하면 fetch across commit이지만, 커서가 인덱스만 훑고 실제 변경은 다른 경로로 일어나기 때문에 위험이 크게 줄어든다. 그래도 근본 해법은 아니다. 가능하면 처리 대상 키 목록을 먼저 임시 테이블에 확정해 두고, 그 목록을 기준으로 도는 편이 안전하다.
남은 교훈
ORA-01555를 만나면 UNDO 크기부터 늘리고 싶어진다. 나도 그렇게 했고 일주일을 버렸다. 이 에러는 저장 공간 에러가 아니라 시간 에러다. 봐야 할 것은 테이블스페이스 크기가 아니라 왜 이 커서가 이렇게 오래 열려 있나다.
그리고 배치에서 루프 안 커밋은 대부분 잘못된 습관이다. 트랜잭션을 잘게 쪼개면 안전해진다는 감각은, 읽기 일관성이 개입하는 순간 정반대로 뒤집힌다. 건수가 적을 때는 문제가 드러나지 않다가 데이터가 쌓인 어느 날 새벽에 조용히 터진다.
지금은 신규 배치를 리뷰할 때 루프 안에 commit이 있는지부터 본다. 있으면 일단 이유를 묻는다. 대부분은 특별한 이유가 없다.