누적 실적 배치가 한 번 더 돌자 6월 주문이 두 번 더해졌다

중급MERGE한빛유통433 / 700

상황

매달 2일 새벽에는 지난달 주문을 고객별 누적 실적(누적 건수, 누적 금액, 마지막 주문일)에 더하는 배치가 돈다. 이번 달에는 스케줄러가 응답 없음으로 판단해 배치를 한 번 더 돌렸고(첫 실행도 끝까지 성공했다), 6월분이 두 번 더해졌다. 정산팀 조민우: "VIP 등급 심사가 이 숫자로 나가요." 오과장은 이참에 고객 2,000명을 27,390번 고치는 루프도 같이 고치자고 한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · 2024년 6월 27,390건
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID). 이 문제에서는 누적 칸 셋(TOT_ORD_CNT, TOT_ORD_AMT, LAST_ORD_DT)이 더 있다. 건수와 금액은 NOT NULL(처음 값 0)이고 LAST_ORD_DT는 NULL일 수 있다.
6월에 주문한 고객은 1,998명이다(한 명에 평균 약 14건).
처리한 달을 적어 두는 테이블은 아직 없다. 만들어도 된다.

원본 SQL

sql

PROCEDURE ADD_MONTHLY(p_ym VARCHAR2) IS          -- p_ym = '202406'
BEGIN
  FOR r IN (SELECT CUS_ID, ORD_DT, ORD_AMT
              FROM T_ORD_BIG
             WHERE ORD_DT >= TO_DATE(p_ym, 'YYYYMM')
               AND ORD_DT <  ADD_MONTHS(TO_DATE(p_ym, 'YYYYMM'), 1)) LOOP
    UPDATE M_CUS
       SET TOT_ORD_CNT = TOT_ORD_CNT + 1,
           TOT_ORD_AMT = TOT_ORD_AMT + r.ORD_AMT,
           LAST_ORD_DT = GREATEST(NVL(LAST_ORD_DT, r.ORD_DT), r.ORD_DT)
     WHERE CUS_ID = r.CUS_ID;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — ADD_MONTHLY('202406') 한 번]
  경과 시간                 0.44 ~ 0.48 초
  recursive calls           27,612
  execute count             27,405
  session logical reads     약 88,300
  db block changes          약 55,100
  redo size                 약 8.6 MB
  undo change vector size   약 3.1 MB

[V$SQL — 루프 안의 UPDATE]
  EXECUTIONS 27,390 · ROWS_PROCESSED 27,390 · BUFFER_GETS 82,170 (한 번에 3블록: PK_M_CUS 1 · M_CUS 2)

[스케줄러가 두 번 돌린 뒤 — 6월에 주문한 고객 1,998명]
  TOT_ORD_CNT 가 6월 건수의 두 배만큼 늘어난 고객     1,998
  누적 금액 증가분 / 6월 주문 금액 합계                 정확히 2.00

문제

이 배치의 일량이 어디서 나오는지 V$SQL과 세션 통계로 설명하시오.
루프를 한 문장으로 바꾸시오. 6월 주문을 그대로 MERGE … USING에 붙이면 왜 안 되는지도 설명하시오.
같은 달을 두 번 돌려도 두 번 더해지지 않게 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 처리한 달을 적는 표 (한 번 만든다)
CREATE TABLE T_CUS_ACC_RUN (
  RUN_YM  VARCHAR2(6) CONSTRAINT PK_T_CUS_ACC_RUN PRIMARY KEY,
  RUN_DT  DATE        NOT NULL,
  CUS_CNT NUMBER
);

PROCEDURE ADD_MONTHLY(p_ym VARCHAR2) IS
  v_cnt NUMBER;
BEGIN
  -- ① 이 달을 처리한다고 먼저 적는다. 같은 달이 또 오면 ORA-00001 로 멈추고 아무것도 더하지 않는다
  INSERT INTO T_CUS_ACC_RUN (RUN_YM, RUN_DT) VALUES (p_ym, SYSDATE);

  -- ② 주문을 고객별로 먼저 묶고(고객 한 행에 원천 한 행) 한 번에 더한다
  MERGE INTO M_CUS c
  USING (SELECT CUS_ID, COUNT(*) AS CNT, SUM(ORD_AMT) AS AMT, MAX(ORD_DT) AS MAX_DT
           FROM T_ORD_BIG
          WHERE ORD_DT >= TO_DATE(p_ym, 'YYYYMM')
            AND ORD_DT <  ADD_MONTHS(TO_DATE(p_ym, 'YYYYMM'), 1)
          GROUP BY CUS_ID) o
     ON (c.CUS_ID = o.CUS_ID)
   WHEN MATCHED THEN UPDATE
        SET c.TOT_ORD_CNT = c.TOT_ORD_CNT + o.CNT,
            c.TOT_ORD_AMT = c.TOT_ORD_AMT + o.AMT,
            c.LAST_ORD_DT = GREATEST(NVL(c.LAST_ORD_DT, o.MAX_DT), o.MAX_DT);
  v_cnt := SQL%ROWCOUNT;

  UPDATE T_CUS_ACC_RUN SET CUS_CNT = v_cnt WHERE RUN_YM = p_ym;
  COMMIT;                                  -- 기록과 누적이 함께 확정되거나 함께 되돌아간다
END;

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |           |      1 |        |      0 |00:00:00.07 |    7896 |          |
|   1 |  MERGE                 | M_CUS     |      1 |        |      0 |00:00:00.07 |    7896 |          |
|   2 |   VIEW                 |           |      1 |        |   1998 |00:00:00.05 |    5856 |          |
|*  3 |    HASH JOIN           |           |      1 |   1998 |   1998 |00:00:00.05 |    5856 | 1532K (0)|
|   4 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   5 |     VIEW               |           |      1 |   1998 |   1998 |00:00:00.05 |    5826 |          |
|   6 |      HASH GROUP BY     |           |      1 |   1998 |   1998 |00:00:00.05 |    5826 | 1321K (0)|
|*  7 |       TABLE ACCESS FULL| T_ORD_BIG |      1 |  27390 |  27390 |00:00:00.02 |    5826 |          |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("C"."CUS_ID"="O"."CUS_ID")
   7 - filter(("ORD_DT">=TO_DATE(:B1,'YYYYMM') AND "ORD_DT"<ADD_MONTHS(TO_DATE(:B1,'YYYYMM'),1)))

[세션 통계]  경과 0.07초 · recursive calls 20 안팎 · 고친 행 1,998 · redo 약 0.9 MB
[같은 달을 한 번 더]  ORA-00001: unique constraint (PK_T_CUS_ACC_RUN) violated → 누적 변화 0

모범 답안 서술

일량은 6월 주문 27,390건마다 UPDATE를 한 번씩 실행하는 데서 나온다(V$SQL EXECUTIONS 27,390). 6월에 주문한 고객은 1,998명뿐이라 같은 고객 행을 평균 14번 되풀이해 고친다. UPDATE 한 번은 PK_M_CUS와 테이블을 합쳐 3블록이지만 27,390번이면 82,170블록이고, 행을 고칠 때마다 redo와 undo가 따로 쌓인다(redo 약 8.6MB, db block changes 약 55,100). 한 문장으로 바꿀 때는 주문을 먼저 고객별로 묶어야 한다(GROUP BY CUS_ID). 6월 주문을 그대로 MERGE … USING에 붙이면 고객 한 행에 원천 행이 평균 14개씩 맞물려 ORA-30926(안정된 행 집합을 얻을 수 없다)으로 멈춘다. 조인 뷰 UPDATE로 쓰면 M_CUS가 키 보존 테이블이 아니어서 ORA-01779가 난다. 고객별로 건수, 금액 합계, 마지막 주문일을 미리 구해 두면 고객 한 행에 원천 한 행이 맞물린다. 고친 행은 27,390 → 1,998, Buffers는 약 88,300 → 7,896, redo는 약 8.6 → 0.9MB로 준다. 두 번 더해진 것은 배치가 '이미 처리한 달'을 모르기 때문이다. 처리한 달을 T_CUS_ACC_RUN(PK RUN_YM)에 같은 트랜잭션으로 적으면, 같은 달을 다시 돌릴 때 PK 위반으로 멈추고 아무것도 더하지 않는다. 처리 중에 실패하면 MERGE와 기록이 함께 되돌아가므로 그대로 다시 돌리면 된다.

해설

다시 계산하는 설계: 누적이 2022년 1월부터이고 그 주문이 모두 T_ORD_BIG에 있다면, 더하는 대신 전체 주문을 고객별로 다시 묶어 덮어쓰는 방법도 있다. FULL 한 번(5,826블록)이면 되고 몇 번을 돌려도 같은 값이 나온다. 이미 두 번 더해진 6월분도 이 방법으로 한 번에 바로잡을 수 있다.
UPDATE M_CUS c SET (…) = (SELECT … FROM T_ORD_BIG WHERE CUS_ID = c.CUS_ID …) 모양의 상관 서브쿼리 UPDATE도 한 문장이다. 하지만 고객 2,000명마다 서브쿼리가 돌고, CUS_ID 인덱스가 없으니 2,000 × 5,826블록이 된다.
같은 고객 행을 27,390번 고치는 루프는 M_CUS 30블록에 변경이 몰려, 같은 시간에 고객을 읽는 화면도 그 블록의 CR 사본을 만들어야 한다. 한 문장은 행마다 한 번만 고친다.
스케줄러 재시도처럼 "성공했는데 한 번 더" 도는 일은 흔하다. 누적형 배치는 처리 기록 없이는 다시 돌려도 안전하지 않다(교재 08화 「정합성 회귀 체크리스트」 H).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황