1,000건마다 커밋했는데 ORA-01555로 죽었다

중급PLSQL유통498 / 700

상황

본사 레거시 유통에서 3월 1일 적용 판매가(CF05) 일부가 잘못 등록돼 있어 정정했고, 이어서 판매상품(CF11)의 판매 단가를 정정 단가로 다시 맞추는 배치를 돌렸다. 평소라면 새벽에 돌릴 일을 매장 정산 마감 때문에 오전 10:00에 돌렸다. 매장 POS는 영업 중이었다. 10:03:52에 배치가 ORA-01555로 죽었다. 본사 전산 담당자가 물었다. "언두가 모자라지 않게 1,000건마다 커밋했는데요. 그냥 다시 돌리면 되나요?"

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 2023-03-01 이후 판매분 약 46만 행 · 영업 중에는 POS가 반품이나 수량 정정으로 계속 UPDATE하고 COMMIT한다
CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SLE_DT VARCHAR2(8)
CF05 상품가격: 157행 · 5블록 · PK (ITM_NO, UPRC_KN_CD, APL_DT) · 정정한 3월 1일 판매가(UPRC_KN_CD = '01')는 109개 상품 모두 있다 · 그 뒤 판매가 변경은 없다
언두 테이블스페이스 200MB 고정(AUTOEXTEND OFF, 설치 때 그대로) · undo_retention 900

원본 SQL

sql

DECLARE
  v_uprc CF05.ITM_UPRC%TYPE;
  n      PLS_INTEGER := 0;
BEGIN
  FOR r IN (SELECT d.ROWID AS rid, d.ITM_NO
              FROM CF10 h, CF11 d
             WHERE d.SLE_NO = h.SLE_NO
               AND h.SLE_DT >= '20230301') LOOP
    SELECT ITM_UPRC INTO v_uprc
      FROM CF05
     WHERE ITM_NO = r.ITM_NO AND UPRC_KN_CD = '01' AND APL_DT = '20230301';

    UPDATE CF11 SET SLE_UPRC = v_uprc WHERE ROWID = r.rid;

    n := n + 1;
    IF MOD(n, 1000) = 0 THEN COMMIT; END IF;     -- 언두가 모자라지 않게 (본사 전산 규칙)
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[시간표 — 오전 · 매장 영업 중]
시각        세션 A — 단가 정정 배치 (SID 131)                          세션 B · C — 매장 POS (SID 47 · 62)
10:00:00    OPEN 커서 — 읽기 시점(SCN)이 여기서 고정된다
            CF10 ⋈ CF11 을 판매번호 순으로 흘려 읽는다 (정렬 없음)
10:00:01 ~  FETCH → 단가 조회 → UPDATE CF11 → 1,000 건마다 COMMIT          반품 · 수량 정정 UPDATE CF11 → COMMIT (분당 수십 건)
            (COMMIT 마다 그 1,000 건의 언두가 '덮어써도 되는' 칸이 된다)
10:02 ~     커서가 OPEN 뒤에 바뀐 블록에 닿을 때마다 언두로 10:00 의 과거 사본(CR)을 만들어 읽는다
10:03:52    FETCH 287,001 번째 → ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_2180427191$" too small
            → 배치 종료. 287,000 건은 이미 COMMIT · 나머지 약 17만 건은 옛 단가 그대로
(B · C 는 기다리지 않는다 — 블로킹이 아니라 '과거가 사라진' 장애다)

[알림 로그]
  ORA-01555 caused by SQL statement below (SQL ID: 9xw2k4bq1m7dc, Query Duration=232 sec, SCN: 0x0000000000a3c1f2):
  SELECT D.ROWID AS RID, D.ITM_NO FROM CF10 H, CF11 D WHERE D.SLE_NO = H.SLE_NO AND H.SLE_DT >= '20230301'

[V$UNDOSTAT — 10:00 ~ 10:10 구간]
  UNDOBLKS   TXNCOUNT   MAXQUERYLEN   TUNED_UNDORETENTION   UNXPSTEALCNT   SSOLDERRCNT   NOSPACEERRCNT
    24,310      1,962           232                   418             37             1               0

문제

시간표와 알림 로그, V$UNDOSTAT을 근거로 무엇이 '너무 오래되어' 없어졌는지, 1,000건마다 한 커밋이 왜 이를 막지 못했는지(오히려 거들었는지) 설명하시오.
배치를 고치시오. "커밋을 5,000건마다로 늘리자", "언두를 늘리자"는 의견에도 답하시오.
지금 287,000건이 고쳐진 채 남아 있다. 다시 돌려도 되는지, 고친 판이 중간에 죽으면 어떻게 되는지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 한 문장 — 읽기와 쓰기가 한 시점(SCN)에서 같이 끝난다 · 커밋은 한 번
MERGE INTO CF11 d
USING (SELECT x.SLE_NO, x.ITM_NO, p.ITM_UPRC
         FROM CF10 h
         JOIN CF11 x ON x.SLE_NO = h.SLE_NO
         JOIN CF05 p ON p.ITM_NO = x.ITM_NO
                    AND p.UPRC_KN_CD = '01'
                    AND p.APL_DT = '20230301'
        WHERE h.SLE_DT >= '20230301') s
   ON (d.SLE_NO = s.SLE_NO AND d.ITM_NO = s.ITM_NO)
 WHEN MATCHED THEN UPDATE
      SET d.SLE_UPRC = s.ITM_UPRC
    WHERE d.SLE_UPRC <> s.ITM_UPRC;        -- 이미 맞는 행(앞서 고친 287,000 건)은 건너뛴다
COMMIT;

개선 후 실행계획 학습용 예시

xplan

[고친 판 — 학습용 예시]
  한 문장 · 해시 조인 한 번 (CF10 4,583 + CF11 8,204 블록) · 경과 20초 안팎 · 커밋 1
  고친 행 약 17만 (287,000 건은 값이 이미 같아 건너뜀) · undo 수십 MB — 문장이 끝날 때까지 '활성' 이라 덮이지 않는다
  문장 시간(20초) < 언두가 버틴 시간(TUNED_UNDORETENTION 418초) — 과거 사본을 만들 언두가 남아 있다

모범 답안 서술

커서의 결과는 OPEN한 순간(10:00:00의 SCN)의 데이터로 고정된다. 그 뒤 POS와 배치 자신이 CF11 블록을 고치므로, 커서가 그런 블록에 닿으면 언두로 10:00의 과거 사본(CR)을 만들어 읽어야 한다. 알림 로그의 Query Duration=232 sec가 그 커서이고, 232초 전의 과거를 만들 언두가 이미 덮여 있어서 ORA-01555가 났다. V$UNDOSTAT의 MAXQUERYLEN 232도 같은 커서다. UNXPSTEALCNT 37은 언두 공간이 모자라서 보존 기간 안의 언두까지 빼앗아 썼다는 뜻이다. TUNED_UNDORETENTION은 418초로 내려가 있었고, SSOLDERRCNT 1이 바로 이 장애다. 1,000건마다 한 커밋은 이 장애를 막지 못하고 오히려 거들었다. COMMIT하면 그 1,000건의 언두는 '덮어써도 되는' 칸이 된다. 배치는 그 칸을 곧바로 자기 다음 언두로 쓰면서, 같은 배치의 커서가 읽어야 할 과거를 스스로 지운다. 커서를 연 채 그 커서가 읽는 테이블을 고치며 커밋하는 이 모양을 fetch across commit이라고 한다. 커밋 주기를 바꾸는 것은 답이 아니다. 5,000건으로 늘려도 커서가 열려 있는 한 과거는 계속 풀린다. 끝에 한 번만 커밋하면 이번에는 활성 언두가 쌓여 ORA-30036 쪽 벽에 부딪힌다. 언두를 늘리면 01555를 미룰 수는 있어도 구조는 그대로다. 고친 판은 MERGE 한 문장이다. 읽기와 쓰기가 한 문장 안에서 같은 시점으로 끝나고(20초 안팎), 문장이 끝날 때까지 자기 언두가 활성 상태라 덮이지 않는다. 커밋은 한 번만 한다. 지금 287,000건은 고쳐진 채 커밋돼 있어서 반쯤만 반영된 상태다. 이 배치는 '정정 단가로 맞추는' 일이라 다시 돌려도 같은 값이 되지만(멱등), 원본은 287,000건을 또 고친다. 고친 판은 값이 이미 같은 행을 WHEN MATCHED의 WHERE로 건너뛰므로 남은 약 17만 건만 고친다. 또 중간에 실패하면 문장 전체가 되돌려지므로 반쯤 반영된 상태가 남지 않는다. 그러니 그대로 다시 돌리면 된다.

해설

다른 정답은 읽고 → 닫고 → 쓰는 순서다. 고칠 목록(ROWID, 새 단가)을 먼저 BULK COLLECT로 다 읽거나 작업 테이블에 CTAS로 담고, 커서를 닫은 뒤 FORALL로 나눠 쓰며 커밋한다. 쓰는 동안 열린 커서가 없으니 지킬 과거도 없다. 목록이 크면 키 범위(SLE_NO 구간)로 쪼개 조각마다 짧은 문장으로 처리한다(교재 13화). 다만 FETCH … LIMIT 루프 안에서 커밋하면 다시 fetch across commit이 된다.
커밋만 루프 밖으로 빼면 01555는 덜 나지만, 46만 건의 행 락을 몇 분 동안 쥐고 있어서 그 행을 고치려는 POS 반품이 TX 6으로 줄을 선다. 한 문장으로 처리하면 그 시간이 수십 초로 준다.
UNDO_RETENTION을 늘리거나 RETENTION GUARANTEE를 걸면 01555는 미룰 수 있지만, 공간이 모자랄 때 DML 쪽이 ORA-30036으로 실패한다. 그래서 언두 공간과 함께 계획해야 한다.
새벽엔 괜찮았다는 것은 근거가 되지 못한다. 단일 세션이 '자기 발등을 찍는' 01555는 데이터 순서에 따라 나기도 하고 안 나기도 하며, 동시 커밋이 있는 낮에 터진다(교재 13화 §5).
정정은 값을 '되돌리기'(× 1.1, ÷ 10 같은 상대 계산)가 아니라 '기준에 맞추기'로 써야 반쯤 반영된 뒤 다시 돌려도 안전하다. 상대 계산이면 287,000건이 두 번 고쳐진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황