고객 2,000명 등급 재산정에 3분 38초, 문자 발송 탓이라는데

실전PLSQL한빛유통441 / 700

상황

매달 1일 새벽에 고객 등급 재산정 배치가 돈다. 최근 12개월 주문 금액으로 등급(300만 원 이상 A, 100만 원 이상 B, 그 밖 C)을 다시 매기고, 등급이 바뀐 고객은 이력을 남기고 문자를 보낸다. 1월 1일 실행은 3분 38초가 걸렸고 바뀐 고객은 214명이었다. 박팀장: "고객이 2,000명뿐인데 왜 이렇게 오래 걸립니까? 문자 발송 때문인가요?" 마케팅팀은 발송 패키지는 게이트웨이 계약 때문에 손댈 수 없다고 한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 2024년 주문 333,612건. 인덱스는 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) 하나다(CUS_ID 인덱스 없음).
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD. 1년 동안 주문이 없는 고객도 있을 수 있다(그 고객은 C).
T_CUS_GD_HIST(등급 이력, 이 문제용 테이블): CUS_ID · CHG_DT · OLD_GD · NEW_GD. 회차 번호 BAT_ID와 발송 여부 SENT_YN 칸을 더해도 된다.
PKG_NOTI.SEND_GRADE_MSG(p_cus_id, p_new_gd): 마케팅팀 패키지. 발송 대기열 T_MSG_OUT에 한 행을 넣고 UTL_HTTP로 문자 게이트웨이를 부른다(한 번 약 35ms). 안에서 커밋하지 않는다. 보낸 문자는 되돌릴 수 없고, 같은 요청이 두 번 오면 게이트웨이는 두 번 보낸다.

원본 SQL

sql

PROCEDURE REGRADE(p_base DATE) IS              -- p_base = DATE '2025-01-01' (이번 달 1일)
  v_amt NUMBER;
  v_new M_CUS.CUS_GD%TYPE;
BEGIN
  FOR c IN (SELECT CUS_ID, CUS_GD FROM M_CUS) LOOP
    -- ① 최근 12개월 실적
    SELECT NVL(SUM(ORD_AMT), 0) INTO v_amt
      FROM T_ORD_BIG
     WHERE CUS_ID = c.CUS_ID
       AND ORD_DT >= ADD_MONTHS(p_base, -12)
       AND ORD_DT <  p_base;
    -- ② 등급 판정과 갱신
    v_new := CASE WHEN v_amt >= 3000000 THEN 'A'
                  WHEN v_amt >= 1000000 THEN 'B' ELSE 'C' END;
    IF v_new <> c.CUS_GD THEN
      UPDATE M_CUS SET CUS_GD = v_new WHERE CUS_ID = c.CUS_ID;
      INSERT INTO T_CUS_GD_HIST (CUS_ID, CHG_DT, OLD_GD, NEW_GD)
      VALUES (c.CUS_ID, p_base, c.CUS_GD, v_new);
      -- ③ 알림
      PKG_NOTI.SEND_GRADE_MSG(c.CUS_ID, v_new);
    END IF;
    COMMIT;                                     -- 고객마다 (보낸 문자는 되돌릴 수 없으니까)
  END LOOP;
END;

실행계획 학습용 예시

xplan

[세션 통계 — REGRADE(DATE '2025-01-01') 한 번 (운영 복제본)]
  경과 시간                 3분 38초
  session logical reads     약 11,659,000
  recursive calls           약 4,900
  user commits              2,000

[V$SQL — 루프 안의 문장]
  SQL_TEXT                                                          EXECUTIONS   BUFFER_GETS    ELAPSED
  SELECT NVL(SUM(ORD_AMT), 0) FROM T_ORD_BIG WHERE CUS_ID = :B3 …        2,000    11,652,000   209.6 초
  UPDATE M_CUS SET CUS_GD = :B2 WHERE CUS_ID = :B1                         214           642     0.01 초
  INSERT INTO T_CUS_GD_HIST (…) VALUES (…)                                  214           236     0.01 초
  (PKG_NOTI.SEND_GRADE_MSG 214번 · 그 안의 UTL_HTTP 대기 7.4초)

[루프 안 SELECT 한 번의 실행계획 — DISPLAY_CURSOR (ALLSTATS LAST)]
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.10 |    5826 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.10 |    5826 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |    167 |    166 |00:00:00.10 |    5826 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("CUS_ID"=:B3 AND "ORD_DT">=ADD_MONTHS(:B2,-12) AND "ORD_DT"<:B1))

문제

3분 38초가 어디서 나오는지 V$SQL과 실행계획으로 설명하시오.
①과 ②를 집합 처리로 다시 작성하고, ③은 왜 한 문장으로 바꿀 수 없는지 쓴 뒤 어떻게 처리할지 SQL로 작성하시오.
커밋을 어디에 둘지, ③ 도중에 실패했다가 다시 돌릴 때 같은 고객에게 문자가 두 번 가지 않게 하는 방법을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE REGRADE(p_base DATE, p_bat NUMBER) IS
  TYPE t_rid IS TABLE OF ROWID INDEX BY PLS_INTEGER;
  TYPE t_cus IS TABLE OF T_CUS_GD_HIST.CUS_ID%TYPE INDEX BY PLS_INTEGER;
  TYPE t_gd  IS TABLE OF T_CUS_GD_HIST.NEW_GD%TYPE INDEX BY PLS_INTEGER;
  v_rid t_rid;  v_cus t_cus;  v_gd t_gd;
BEGIN
  -- ①② 실적과 등급 — T_ORD_BIG 을 한 번 읽어 고객별로 묶고, 바뀐 고객만 이력에 남긴다
  INSERT INTO T_CUS_GD_HIST (BAT_ID, CUS_ID, CHG_DT, OLD_GD, NEW_GD, SENT_YN)
  SELECT p_bat, CUS_ID, p_base, CUS_GD, NEW_GD, 'N'
    FROM (SELECT c.CUS_ID, c.CUS_GD,
                 CASE WHEN NVL(o.AMT, 0) >= 3000000 THEN 'A'
                      WHEN NVL(o.AMT, 0) >= 1000000 THEN 'B'
                      ELSE 'C' END AS NEW_GD
            FROM M_CUS c
            LEFT OUTER JOIN (SELECT CUS_ID, SUM(ORD_AMT) AS AMT
                               FROM T_ORD_BIG
                              WHERE ORD_DT >= ADD_MONTHS(p_base, -12)
                                AND ORD_DT <  p_base
                              GROUP BY CUS_ID) o
              ON o.CUS_ID = c.CUS_ID)
   WHERE NEW_GD <> CUS_GD;

  MERGE INTO M_CUS c
  USING (SELECT CUS_ID, NEW_GD FROM T_CUS_GD_HIST WHERE BAT_ID = p_bat) h
     ON (c.CUS_ID = h.CUS_ID)
   WHEN MATCHED THEN UPDATE SET c.CUS_GD = h.NEW_GD;
  COMMIT;                                      -- ①② 는 한 번에: 반쯤 바뀐 등급표가 남지 않는다

  -- ③ 알림 — 외부 발송이라 한 문장으로 못 바꾼다. 보낼 고객을 먼저 배열에 담고(커서를 닫고) 보낸다
  SELECT ROWID, CUS_ID, NEW_GD
    BULK COLLECT INTO v_rid, v_cus, v_gd
    FROM T_CUS_GD_HIST
   WHERE BAT_ID = p_bat AND SENT_YN = 'N';

  FOR i IN 1 .. v_rid.COUNT LOOP
    PKG_NOTI.SEND_GRADE_MSG(v_cus(i), v_gd(i));
    UPDATE T_CUS_GD_HIST SET SENT_YN = 'Y' WHERE ROWID = v_rid(i);
    COMMIT;                                    -- 되돌릴 수 없는 발송 한 건마다 '보냈다'를 확정한다
  END LOOP;
END;

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

xplan

[①② 한 문장 — INSERT … SELECT (바뀐 고객만 이력에) · DISPLAY_CURSOR (ALLSTATS LAST)]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |               |      1 |        |      0 |00:00:00.21 |    5868 |          |
|   1 |  LOAD TABLE CONVENTIONAL| T_CUS_GD_HIST |      1 |        |      0 |00:00:00.21 |    5868 |          |
|*  2 |   HASH JOIN OUTER       |               |      1 |    100 |    214 |00:00:00.21 |    5856 | 1590K (0)|
|   3 |    TABLE ACCESS FULL    | M_CUS         |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   4 |    VIEW                 |               |      1 |   2000 |   2000 |00:00:00.20 |    5826 |          |
|   5 |     HASH GROUP BY       |               |      1 |   2000 |   2000 |00:00:00.20 |    5826 | 1394K (0)|
|*  6 |      TABLE ACCESS FULL  | T_ORD_BIG     |      1 |    333K|    333K|00:00:00.08 |    5826 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"(+)="C"."CUS_ID")
       filter(CASE WHEN NVL("O"."AMT",0)>=3000000 THEN 'A' WHEN NVL("O"."AMT",0)>=1000000 THEN 'B' ELSE 'C' END <>"C"."CUS_GD")
   6 - filter(("ORD_DT">=ADD_MONTHS(:B2,-12) AND "ORD_DT"<:B1))

[단계별 — 같은 운영 복제본]
  단계                          경과       Buffers     커밋
  ①② INSERT … SELECT            0.21초       5,868
  ①② MERGE M_CUS (214행)        0.01초      약 700       1
  ③ 발송 214건                   7.5초      약 1,100     214
  합계                           약 7.8초    약 7,700     215    (원본 3분 38초 · 약 1,166만 · 2,000)

모범 답안 서술

3분 38초 중 3분 30초(209.6초)가 ①의 실적 SELECT에서 나온다. 문자 발송(7.4초)이 원인이 아니다. 이 SELECT는 고객마다 한 번씩 2,000번 실행되는데, T_ORD_BIG에 CUS_ID 인덱스가 없어 실행할 때마다 100만 행을 FULL로 읽고 그 고객 것 166행만 남긴다(실행계획 2번 줄 5,826블록). 2,000 × 5,826 = 11,652,000블록이다. 같은 테이블을 고객 수만큼 다시 읽은 것이 원인이다. ①과 ②는 한 문장으로 바꾼다. T_ORD_BIG을 한 번 FULL로 읽어 고객별로 묶고(GROUP BY CUS_ID), M_CUS에 아우터 조인해 1년 동안 주문이 없는 고객도 0원으로 남긴 뒤(LEFT OUTER JOIN, NVL), CASE로 새 등급을 매겨 바뀐 고객 214명만 이력에 넣는다. 그 이력으로 M_CUS를 MERGE한다. Buffers는 11,652,000 → 약 6,600, ①②의 시간은 3분 30초 → 0.2초로 준다. 이 둘은 끝에 한 번 커밋한다. 등급과 이력이 함께 바뀌거나 함께 되돌아가므로 반쯤 바뀐 등급표가 남지 않는다. 고객마다 하던 커밋 2,000번은 없앤다. ③은 한 문장으로 바꿀 수 없다. PKG_NOTI는 UTL_HTTP로 외부 게이트웨이를 부르는 프로시저라서 SQL 안에서 부를 수 없고, 보낸 문자는 롤백으로 되돌릴 수 없다. 그래서 루프로 남기되 바뀐 고객 214명에게만 돈다. 보낼 대상을 먼저 BULK COLLECT로 배열에 담고 커서를 닫은 뒤 보내므로, 읽는 테이블을 고치며 커밋하는 모양(fetch across commit)도 생기지 않는다. ③에서는 건마다 커밋하는 것이 맞다. 발송은 되돌릴 수 없으므로 한 건을 보낼 때마다 SENT_YN = 'Y'를 확정해 둔다. ③ 도중에 실패하면 같은 회차로 다시 돌리면 된다. ①②는 이미 커밋되어 등급이 새 값과 같으므로 이력이 새로 생기지 않고, ③은 SENT_YN = 'N'인 고객만 다시 고르므로 이미 보낸 고객에게 두 번 보내지 않는다. 문자 214건(7.5초)은 외부 시간이라 그대로 남고, 전체는 3분 38초 → 약 7.8초가 된다.

해설

함정: "문자 발송이 느리다"는 짐작이다. V$SQL의 ELAPSED를 보면 209.6초가 실적 SELECT이고 발송은 7.4초다. 외부 시스템을 탓하기 전에 숫자부터 본다.
인덱스로 루프를 살리는 안: (CUS_ID, ORD_DT, ORD_AMT) 인덱스를 만들면 실적 SELECT 한 번이 약 5블록이 되어 2,000번이어도 1만 블록 남짓이다. 하지만 SQL 2,000번과 커밋 2,000번이 남고, 100만 행 테이블에 인덱스를 하나 더 유지해야 한다(재적재 비용, 교재 14화). 한 달에 한 번 도는 배치를 위해 만들 인덱스는 아니다.
두 번 가는 문자: 발송은 성공했는데 SENT_YN을 커밋하기 전에 세션이 죽으면, 그 한 건은 다시 돌릴 때 또 간다. 건마다 커밋하면 이 틈이 한 건으로 좁아진다. 완전히 막으려면 게이트웨이에 요청 키(회차와 고객 번호)를 넘겨 게이트웨이가 중복을 거르게 한다.
원본은 고객 2,000명 모두에게 루프를 돌면서 바뀌었는지를 행마다 따졌다. 집합 처리로 대상을 먼저 214명으로 줄이고, 루프는 꼭 필요한 일(외부 호출)에만 남긴다(교재 15화 「한 문장인가 PL/SQL인가」).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황