POS 장애로 밀린 일주일 치를 05:00 전에 마감해야 하는데, 하루 치 체인을 일곱 번 돌리면 108분이 넘는다

실전PLSQL버거495 / 700

상황

POS 중계 서버 장애로 12월 23일 ~ 29일 일주일 치 주문이 본사에 들어오지 않다가, 12월 30일 03:10에 업체가 한꺼번에 다시 보내 ORD와 ORD_ITEM에 다 들어갔다. 일 마감 체인은 기준일 하루를 처리하도록 짜여 있고 평소에는 04:00에 전날분을 한 번 돈다. 05:00에 매장 발주 자동 생성이 최근 7일의 매장 일 집계와 메뉴 순위를 읽으므로 그 전에 일곱 날을 모두 마감해야 한다. 03:30부터 시작할 수 있다. 당직 개발자는 기준일만 바꿔 체인을 일곱 번 돌리겠다고 한다. 오과장: "한 번 도는 데 15분 30초니까 108분이 넘어요. 날짜 범위로 한 번에 돌리면 안 되나요?" 개발자: "WHERE만 범위로 바꾸면 되긴 하는데요…"

테이블과 인덱스

ORD 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · ORD_ITEM 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 12/23 ~ 12/29 주문 9,604건, 주문상품 24,017행(테이블의 약 1%)
SHOP_DAY(매장 일 집계) · SHOP_ITEM_RANK(매장 메뉴 일 순위, 매장마다 날마다 상위 10개) · ITEM_MON(메뉴 월 누계): 모두 이 문제용이며 기준일 단위로 지우고 다시 넣거나 다시 계산해 덮는다(다시 돌려도 같다).
P_ITEM_MON_REBUILD(ym): 그 달 1일부터 지금까지를 ORD_ITEM에서 다시 합산해 덮는다. P_RPT_CACHE_REBUILD: 최근 13개월 매장 × 메뉴 × 월 요약을 통째로 다시 만든다. P_DW_DAY_FILE(d): DW가 기다리는 '기준일 하루 = 파일 하나'를 만들어 보낸다.
본사 DB는 공유 스토리지라 큰 테이블 전체 스캔이 느리다(ORD_ITEM 한 번에 약 17초).

원본 SQL

sql

PROCEDURE P_DAY_CLOSE(p_d VARCHAR2) IS              -- 매일 04:00 · p_d = 전날 'YYYYMMDD'
BEGIN
  -- S1 매장 일 집계
  DELETE FROM SHOP_DAY WHERE ORD_DT = p_d;
  INSERT INTO SHOP_DAY (ORD_DT, SHOP_NO, ORD_CNT, SALE_QTY)
  SELECT o.ORD_DT, o.SHOP_NO, COUNT(DISTINCT o.ORD_NO), SUM(i.ORD_ITEM_QTY)
    FROM ORD o, ORD_ITEM i
   WHERE o.ORD_DT = p_d AND i.ORD_DT = p_d AND i.ORD_NO = o.ORD_NO
   GROUP BY o.ORD_DT, o.SHOP_NO;
  COMMIT;

  -- S2 매장 메뉴 일 순위 (매장마다 상위 10개)
  DELETE FROM SHOP_ITEM_RANK WHERE ORD_DT = p_d;
  INSERT INTO SHOP_ITEM_RANK (ORD_DT, SHOP_NO, ITEM_ID, SALE_QTY, RNK)
  SELECT p_d, SHOP_NO, ITEM_ID, QTY, RNK
    FROM (SELECT o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS QTY,
                 ROW_NUMBER() OVER (PARTITION BY o.SHOP_NO
                                    ORDER BY SUM(i.ORD_ITEM_QTY) DESC, i.ITEM_ID) AS RNK
            FROM ORD o, ORD_ITEM i
           WHERE o.ORD_DT = p_d AND i.ORD_DT = p_d AND i.ORD_NO = o.ORD_NO
           GROUP BY o.SHOP_NO, i.ITEM_ID)
   WHERE RNK <= 10;
  COMMIT;

  P_ITEM_MON_REBUILD(SUBSTR(p_d, 1, 6));                                       -- S3 메뉴 월 누계
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD',      estimate_percent => 100);     -- S4 통계 수집
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD_ITEM', estimate_percent => 100);
  P_RPT_CACHE_REBUILD;                                                         -- S5 본사 리포트 캐시
  P_DW_DAY_FILE(p_d);                                                          -- S6 DW 일 파일
END;

실행계획 학습용 예시

xplan

[12월 22일 평소 실행 — 단계별]
  단계                     경과        하는 일                                      읽는 양
  S1 매장 일 집계           0분 06초    그날 주문 · 주문상품 (인덱스 범위)            약 4,600블록
  S2 매장 메뉴 일 순위       0분 04초    그날 주문 · 주문상품 (인덱스 범위)            약 4,600블록
  S3 메뉴 월 누계           1분 50초    그 달 1일 ~ 그날 ORD_ITEM 전체 스캔 · 해시 집계   55,265블록
  S4 통계 수집              6분 40초    ORD · ORD_ITEM 전체 · 100% 표본               77,687블록 + 정렬
  S5 본사 리포트 캐시        5분 10초    13개월 ORD · ORD_ITEM 전체 스캔 · TEMP 사용     77,687블록
  S6 DW 일 파일             1분 40초    그날 집계로 파일 생성 0분 40초 + 전송 연결 1분
  합계                     15분 30초

[리허설 — S1, S2의 WHERE만 7일 범위로 바꿔 한 번 돌려 봄]
  S1   0분 26초 · SHOP_DAY 1,400행 (200매장 × 7일) — 날마다 돌린 결과와 같다
  S2   0분 24초 · SHOP_ITEM_RANK 2,000행 (200매장 × 10개) — 날마다 돌리면 14,000행이어야 한다

문제

체인을 기준일마다 일곱 번 돌리면 몇 분이 걸리고 몇 시에 끝나는지 계산하시오. 단계마다 비용이 '처리하는 날 수'에 비례하는지 '실행 횟수'에 비례하는지 가르시오.
일곱 날을 한 번에 처리하도록 체인을 고치시오. 창을 넓혀도 결과가 같은 단계와 그대로 넓히면 결과가 틀어지는 단계(리허설의 S2 2,000행)를 가르고, 고친 체인이 몇 분에 끝나는지 계산하시오.
이번에 S4(통계 수집)를 어떻게 할지 근거와 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_DAY_CLOSE_RANGE(p_from VARCHAR2, p_to VARCHAR2, p_stats VARCHAR2 DEFAULT 'N') IS
  v_d DATE;
BEGIN
  -- S1 날짜 범위 한 문장 · 날짜가 GROUP BY 에 있어 넓혀도 날마다 돌린 결과와 같다
  DELETE FROM SHOP_DAY WHERE ORD_DT BETWEEN p_from AND p_to;
  INSERT INTO SHOP_DAY (ORD_DT, SHOP_NO, ORD_CNT, SALE_QTY)
  SELECT o.ORD_DT, o.SHOP_NO, COUNT(DISTINCT o.ORD_NO), SUM(i.ORD_ITEM_QTY)
    FROM ORD o, ORD_ITEM i
   WHERE o.ORD_DT BETWEEN p_from AND p_to
     AND i.ORD_DT BETWEEN p_from AND p_to
     AND i.ORD_NO = o.ORD_NO
   GROUP BY o.ORD_DT, o.SHOP_NO;
  COMMIT;

  -- S2 순위는 날짜마다 따로 매긴다 (PARTITION BY 에 ORD_DT · 결과에도 ORD_DT)
  DELETE FROM SHOP_ITEM_RANK WHERE ORD_DT BETWEEN p_from AND p_to;
  INSERT INTO SHOP_ITEM_RANK (ORD_DT, SHOP_NO, ITEM_ID, SALE_QTY, RNK)
  SELECT ORD_DT, SHOP_NO, ITEM_ID, QTY, RNK
    FROM (SELECT o.ORD_DT, o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS QTY,
                 ROW_NUMBER() OVER (PARTITION BY o.ORD_DT, o.SHOP_NO
                                    ORDER BY SUM(i.ORD_ITEM_QTY) DESC, i.ITEM_ID) AS RNK
            FROM ORD o, ORD_ITEM i
           WHERE o.ORD_DT BETWEEN p_from AND p_to
             AND i.ORD_DT BETWEEN p_from AND p_to
             AND i.ORD_NO = o.ORD_NO
           GROUP BY o.ORD_DT, o.SHOP_NO, i.ITEM_ID)
   WHERE RNK <= 10;
  COMMIT;

  -- S3 · S5 는 마지막 날 기준으로 한 번 (결과는 '지금까지 전체'라 날마다 할 필요가 없다)
  P_ITEM_MON_REBUILD(SUBSTR(p_to, 1, 6));            -- 범위가 달을 넘으면 달마다 한 번
  P_RPT_CACHE_REBUILD;

  -- S4 는 따로 요청할 때만 (평소에는 밤 자동 통계 수집 창에 맡긴다)
  IF p_stats = 'Y' THEN
    DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD');
    DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD_ITEM');
  END IF;

  -- S6 DW 는 '기준일 하루 = 파일 하나'를 기다린다 → 이것만 날마다
  v_d := TO_DATE(p_from, 'YYYYMMDD');
  WHILE v_d <= TO_DATE(p_to, 'YYYYMMDD') LOOP
    P_DW_DAY_FILE(TO_CHAR(v_d, 'YYYYMMDD'));
    v_d := v_d + 1;
  END LOOP;
END;
-- 12/30 03:30 : P_DAY_CLOSE_RANGE('20131223', '20131229')  · 04:00 정기 실행(12/29)은 오늘 하루 멈춘다
-- 평소 04:00 : P_DAY_CLOSE_RANGE(전날, 전날) · 하루 처리도 같은 코드

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

xplan

[12/30 03:30 — 일곱 날을 한 번에 (리허설과 단계 경과로 계산)]
  S1 7일 범위        0분 26초   SHOP_DAY 1,400행
  S2 7일 범위        0분 24초   SHOP_ITEM_RANK 14,000행 (날짜마다 매장마다 10개 · 날마다 돌린 결과와 MINUS 0행)
  S3 한 번           1분 50초
  S5 한 번           5분 10초
  S4                 건너뜀 (일주일 치 9,604건 = ORD 의 약 1%)
  S6 일곱 번        11분 40초   파일 일곱 개
  합계              19분 30초 · 03:49:30 끝 · 05:00 마감까지 여유 70분

[비교 — 기준일마다 일곱 번]
  고정 13분 40초 × 7 + 날마다 1분 50초 × 7 = 108분 30초 · 05:18:30 끝 · 마감 18분 30초 넘김

모범 답안 서술

일 마감 체인의 비용은 두 종류로 나뉜다. S1, S2, S6은 처리하는 날의 주문만 다루므로 날 수에 비례한다(하루 0분 06초 + 0분 04초 + 1분 40초 = 1분 50초). S3(그 달을 ORD_ITEM에서 다시 합산), S4(두 테이블 전체 통계), S5(13개월 요약을 통째로)는 기준일이 하루든 일곱 날이든 같은 양을 읽는다. 실행할 때마다 치르는 고정 비용이고 합쳐서 13분 40초다. 체인을 기준일마다 일곱 번 돌리면 고정 비용이 일곱 번 곱해져 13분 40초 × 7 + 1분 50초 × 7 = 108분 30초가 걸리고, 03:30에 시작해 05:18:30에 끝나 마감을 18분 30초 넘긴다. 그중 82분은 같은 결과를 여섯 번 더 만드는 일이다. 한 번에 처리하려면 창을 넓혀도 결과가 같은지 단계마다 따져야 한다. S1은 날짜가 GROUP BY에 있어 범위로 넓혀도 날마다 돌린 결과와 같다(리허설 1,400행). S3과 S5는 결과가 '지금까지 전체'라 마지막에 한 번만 하면 된다. S2는 다르다. PARTITION BY o.SHOP_NO만 두고 WHERE를 넓히면 일곱 날의 판매가 한데 섞여 매장마다 일주일 합계로 순위 10개만 남는다(리허설 2,000행, 맞는 값은 14,000행). 그래서 PARTITION BY와 GROUP BY에 ORD_DT를 넣고 결과에도 날짜를 남긴다. S6은 DW가 '기준일 하루 = 파일 하나'를 기다리므로 범위로 묶지 않고 날마다 돌린다. 고친 체인 P_DAY_CLOSE_RANGE는 S1과 S2를 범위 한 문장으로, S3과 S5를 한 번, S6을 일곱 번 돈다. 0분 26초 + 0분 24초 + 1분 50초 + 5분 10초 + 1분 40초 × 7 = 19분 30초로, 108분 30초 → 19분 30초가 되고 03:49:30에 끝난다. 평소에는 같은 프로시저를 하루 범위로 부르므로 코드가 두 벌이 되지 않는다. 오늘 04:00 정기 실행(12/29분)은 이 실행이 대신하므로 하루 멈춘다. S4는 이번에 건너뛴다. 일주일 치 주문 9,604건은 ORD 100만 행의 약 1%로, 통계가 낡았다고 보는 기본 기준(10%)에 한참 못 미친다. 분포가 바뀐 것도 아니라서 지금 모아도 실행계획이 달라질 일이 없고 6분 40초만 쓴다. 필요하면 밤의 자동 통계 수집이 알아서 모은다. 평소 체인에서도 매일 100% 표본으로 두 테이블을 모으는 S4는 따로 줄일 거리다.

해설

비용을 고정과 비례로 나눠 적는다: T3 리포트 3번 칸의 단계별 경과에 '무엇에 비례하는가'를 한 줄 더하면, 밀린 날 처리나 데이터가 늘었을 때의 시간을 곱셈으로 바로 계산할 수 있다(교재 14화 T3 배치 SLA 리포트 8번 칸).
창을 넓힐 때 깨지는 것들: 날마다 매기는 순위와 상위 N, '그날 첫 주문' 같은 표시, 그날 안에서의 중복 제거, '하루 = 파일 하나' 같은 외부 약속이다. 날짜를 PARTITION BY와 GROUP BY에 넣었는지, 결과에 날짜가 남는지를 본다. 날마다 돌린 결과와 MINUS로 대조하면 바로 드러난다.
다른 정답: 고정 비용 단계만 끝에 한 번 돌리고 S1, S2, S6을 기준일마다 일곱 번 돌려도 마감 안에 든다(1분 50초 × 7 + 7분 = 19분 50초 안팎). 기존 하루 코드를 그대로 쓸 수 있어 위험이 작다. 대신 S1과 S2의 SQL이 일곱 번씩 실행된다.
고정 비용을 줄일 수 있다면: S3이 '그 달 전체를 다시 합산'하는 대신 일 집계(SHOP_DAY)에서 그 달을 합산하면 55,265블록이 수천 블록으로 준다. 고정 비용이 작을수록 날마다 돌리든 한 번에 돌리든 차이가 줄어든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황