ROLLBACK했다는 일 마감 배치의 숫자가 본사 대시보드에 그대로 떠 있다

기초PLSQL버거602 / 700

상황

버거 본사의 「일 매출 마감」 배치는 매일 02:10에 전날 주문을 매장별로 세어 SHOP_DAY_SUM에 넣는다. 매장 POS가 마감 때 보낸 주문 수(SHOP_CLOSE)와 한 곳이라도 다르면 전부 되돌리고, POS가 다시 보내면 재실행한다. 지난달 조민우가 "적재 직후 통계 수집" 원칙을 따라 INSERT 바로 뒤에 통계 수집을 넣었다. 8월 6일 새벽 로그에는 마감 불일치 1곳 → ROLLBACK이 남았는데, 아침 대시보드에는 SH0137의 어제 주문이 3건으로 떠 있었다(POS 보고는 9건).

테이블과 인덱스

SHOP_DAY_SUM 매장 일 매출(이 문제용): SHOP_NO · SUM_DT · ORD_CNT · 하루 200행
SHOP_CLOSE 매장 마감 보고(이 문제용): SHOP_NO · CLOSE_DT · ORD_CNT · POS가 마감 때 보낸다
ORD: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 하루 약 1,370건(매장당 약 7건) · ORD_X01 (ORD_DT, ORD_HMS)
PKG_LOG.WRITE는 자율 트랜잭션으로 로그만 남긴다. 배치 본문은 자율 트랜잭션이 아니다.

원본 SQL

sql

PROCEDURE P_DAY_CLOSE(p_dt VARCHAR2) IS          -- p_dt = '20130805'
  v_diff NUMBER;
BEGIN
  DELETE FROM SHOP_DAY_SUM WHERE SUM_DT = p_dt;

  INSERT INTO SHOP_DAY_SUM (SHOP_NO, SUM_DT, ORD_CNT)
  SELECT SHOP_NO, p_dt, COUNT(*)
    FROM ORD
   WHERE ORD_DT = p_dt
   GROUP BY SHOP_NO;

  -- 2013-07 조민우 : 적재 직후 통계 수집
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'SHOP_DAY_SUM');

  SELECT COUNT(*) INTO v_diff
    FROM SHOP_DAY_SUM s, SHOP_CLOSE c
   WHERE s.SUM_DT = p_dt AND c.CLOSE_DT = p_dt
     AND c.SHOP_NO = s.SHOP_NO
     AND c.ORD_CNT <> s.ORD_CNT;

  IF v_diff > 0 THEN
    ROLLBACK;
    PKG_LOG.WRITE('P_DAY_CLOSE', '마감 불일치 ' || v_diff || '곳 → ROLLBACK · 재실행 대기');
    RETURN;
  END IF;

  COMMIT;
END;

실행계획 학습용 예시

xplan

[배치 로그 — 8월 6일 새벽]
 02:10:01  P_DAY_CLOSE('20130805') 시작
 02:10:02  DELETE 0 · INSERT 200
 02:10:02  GATHER_TABLE_STATS SHOP_DAY_SUM 완료 (0.4초)
 02:10:03  마감 불일치 1곳 → ROLLBACK · 재실행 대기
 09:20:00  SH0137 POS 재전송 도착 (재실행 전)

[08:30 — SHOP_DAY_SUM 의 20130805 행]
 COUNT(*) 200 · SH0137 ORD_CNT 3 (SHOP_CLOSE.ORD_CNT 9)
 SCN_TO_TIMESTAMP(ORA_ROWSCN) = 02:10:02      ← 이 행들을 확정한 커밋의 시각

문제

ROLLBACK을 했는데도 200행이 남은 이유를 로그의 시각으로 설명하시오.
"적재 직후 수집"의 뜻을 살리면서 배치를 고치시오. 통계 수집을 자율 트랜잭션 프로시저로 빼는 안이 왜 틀린지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_DAY_CLOSE(p_dt VARCHAR2) IS
  v_diff NUMBER;
BEGIN
  DELETE FROM SHOP_DAY_SUM WHERE SUM_DT = p_dt;

  INSERT INTO SHOP_DAY_SUM (SHOP_NO, SUM_DT, ORD_CNT)
  SELECT SHOP_NO, p_dt, COUNT(*)
    FROM ORD
   WHERE ORD_DT = p_dt
   GROUP BY SHOP_NO;

  SELECT COUNT(*) INTO v_diff
    FROM SHOP_DAY_SUM s, SHOP_CLOSE c
   WHERE s.SUM_DT = p_dt AND c.CLOSE_DT = p_dt
     AND c.SHOP_NO = s.SHOP_NO
     AND c.ORD_CNT <> s.ORD_CNT;

  IF v_diff > 0 THEN
    ROLLBACK;
    PKG_LOG.WRITE('P_DAY_CLOSE', '마감 불일치 ' || v_diff || '곳 → ROLLBACK · 재실행 대기');
    RETURN;
  END IF;

  COMMIT;
  -- '적재 직후' = 검증을 통과해 COMMIT 한 직후 (DBMS_STATS 는 시작할 때와 끝날 때 스스로 커밋한다)
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SHOP_DAY_SUM');
END;

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

xplan

[고친 뒤 · 같은 날을 다시 재현]
 02:10:02  DELETE 0 · INSERT 200
 02:10:03  마감 불일치 1곳 → ROLLBACK · 재실행 대기
 08:30     SHOP_DAY_SUM 의 20130805 행 0 (되돌려졌다) · 대시보드는 '마감 전'으로 표시
 09:25     재실행 · 불일치 0 → COMMIT → GATHER_TABLE_STATS (0.4초)

모범 답안 서술

행을 확정한 커밋의 시각(ORA_ROWSCN 02:10:02)이 통계 수집 시각과 같다. DBMS_STATS.GATHER_TABLE_STATS는 DDL처럼 시작할 때와 끝날 때 스스로 커밋한다(암묵적 커밋). 그래서 INSERT 바로 뒤에 둔 통계 수집이 DELETE와 INSERT를 그 자리에서 확정했다. 그 뒤의 ROLLBACK은 되돌릴 것이 없었고, SH0137의 3건을 포함한 200행이 그대로 남아 대시보드에 떴다. 지운 행(재실행이었다면 앞선 마감 결과)도 이미 사라진 뒤라, 전부 되돌린다는 배치의 원자성이 깨졌다. "적재 직후 수집"의 뜻은 "확정된 데이터를 다음 단계가 쓰기 전에 모은다"다. 이 배치에서 적재가 확정되는 때는 검증을 통과해 COMMIT한 직후다. 그래서 통계 수집을 COMMIT 뒤로 옮긴다. 불일치로 되돌리는 날에는 모을 것도 없다. 통계 수집을 자율 트랜잭션 프로시저로 빼면 본 트랜잭션은 커밋되지 않지만, 자율 트랜잭션 안에서는 본 트랜잭션이 아직 커밋하지 않은 행이 보이지 않는다. 그래서 지우기 전의 행이나 비어 있는 상태처럼 엉뚱한 통계를 모으게 된다. 통계는 확정된 데이터만 셀 수 있다.

해설

배치 안의 DDL은 트랜잭션 경계다. TRUNCATE, CREATE INDEX, ALTER …, DBMS_STATS의 수집과 설정 프로시저는 앞뒤로 커밋한다. 되돌릴 수 있어야 하는 구간 안에 두지 않는다.
하루 200행이 쌓이는 테이블이면 매일 모을 일인지도 따져 본다. 다음 단계의 SQL이 이 테이블을 크게 읽지 않는다면 자동 수집(STALE 10%)으로 충분하다. 통계 수집을 넣는 이유를 정책서에 적어 두면 이런 판단을 다음 사람이 할 수 있다.
이 실수는 성능이 아니라 정합성을 깼다. 튜닝이 정합성을 깨는 것도 13화에서 본 '안전 조치의 대가'와 같은 모양이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황