에러 로그는 깨끗한데 보정이 170건 모자란다

실전PLSQL한빛유통430 / 700

상황

주간 정산 보정 배치는 월요일 11:00 정산 확정 전에 끝나야 하는데, 지난주에는 10:52에 끝났다. 정산팀 조민우: "보정 건수가 정산 건수보다 적었어요. 에러 로그는 깨끗하대요." 소스에는 주석이 둘 있다. 한 건 때문에 전체를 세우지 않는다 (2019.06 강도현)와 트랜잭션은 짧게 (5월 포스트모템 재발방지)다. 보정률 규칙(등급 A 0.1%, B 0.2%, 그 밖 0.3%)은 정산팀 규정이고 이 배치 안에만 있다.

테이블과 인덱스

T_ORD_STLM(원장): 이번 주 정산 100,020행(P1 적재 10만 + 금액 미확정 등록 20, STLM_AMT NULL) · 약 640블록. STLM_SEQ는 겹치지 않는다.
M_CUS: 1,997행 · PK_M_CUS (CUS_ID). 탈퇴 고객 3명은 개인정보 파기로 마스터에서 지웠고, 그 3명의 정산이 150건이다.
T_STLM_ADJ(보정 이력): PK (STLM_SEQ) · ADJ_RATE · ADJ_AMT NOT NULL · ADJ_DT. 같은 주를 다시 돌리면 UPDATE 쪽으로 간다.
반송함 테이블은 아직 없다. 만들어도 된다.

원본 SQL

sql

PROCEDURE ADJ_WEEKLY IS
  v_gd   M_CUS.CUS_GD%TYPE;
  v_rate NUMBER;
  v_cnt  NUMBER;
  n      PLS_INTEGER := 0;
BEGIN
  FOR r IN (SELECT STLM_SEQ, CUS_ID, STLM_AMT FROM T_ORD_STLM) LOOP
    BEGIN
      SELECT CUS_GD INTO v_gd FROM M_CUS WHERE CUS_ID = r.CUS_ID;                    -- 고객 등급
      v_rate := CASE v_gd WHEN 'A' THEN 0.001 WHEN 'B' THEN 0.002 ELSE 0.003 END;     -- 보정률 (정산팀 규정)
      SELECT COUNT(*) INTO v_cnt FROM T_STLM_ADJ WHERE STLM_SEQ = r.STLM_SEQ;         -- 이미 보정했나
      IF v_cnt = 0 THEN
        INSERT INTO T_STLM_ADJ (STLM_SEQ, ADJ_RATE, ADJ_AMT, ADJ_DT)
        VALUES (r.STLM_SEQ, v_rate, ROUND(r.STLM_AMT * v_rate), SYSDATE);
      ELSE
        UPDATE T_STLM_ADJ
           SET ADJ_RATE = v_rate, ADJ_AMT = ROUND(r.STLM_AMT * v_rate), ADJ_DT = SYSDATE
         WHERE STLM_SEQ = r.STLM_SEQ;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN NULL;                         -- 한 건 때문에 전체를 세우지 않는다 (2019.06 강도현)
    END;
    n := n + 1;
    IF MOD(n, 1000) = 0 THEN COMMIT; END IF;         -- 트랜잭션은 짧게 (5월 포스트모템 재발방지)
  END LOOP;
  COMMIT;
END;

실행계획 실측 · 교재 15화(복사본, 보정률 함수를 루프 안 SELECT로 풀어 적은 판)

xplan

[세션 통계 — ADJ_WEEKLY 한 번 (보정 이력이 빈 첫 판 · 연습 DB 복사본)]
  경과 시간                  2.19 ~ 2.24 초
  CPU used by this session   1.34 ~ 1.42 초
  recursive calls            301,187
  execute count              299,769
  session logical reads      816,425
  redo size                  55.77 MB
  undo change vector size    16.71 MB
  user commits               100

[TKPROF 모양 — 루프 안의 세 문장]
  SELECT CUS_GD FROM M_CUS WHERE CUS_ID = :B1                     Execute 100,020   Fetch 100,020   (한 건 조회 · cr=3)
  SELECT COUNT(*) FROM T_STLM_ADJ WHERE STLM_SEQ = :B1             Execute  99,870   Fetch  99,870
  INSERT INTO T_STLM_ADJ (…) VALUES (:B4, :B3, ROUND(:B2*:B3), SYSDATE)   Execute  99,870   rows 99,850

[끝난 뒤 — 정합성]
  원장 T_ORD_STLM            100,020
  보정 이력 T_STLM_ADJ        99,850
  반송 · 오류 기록                 0
  원장에만 있는 정산             170   (탈퇴 고객 150 · 금액 미확정 20)

문제

이 배치의 일량이 어디서 나오는지, 그리고 170건이 어디로 사라졌는지 위 통계로 설명하시오.
같은 보정을 한 문장 중심으로 다시 작성하시오. 보정할 수 없는 정산은 사유와 함께 남기고, "보정 + 반송 = 원장"이 성립하는지 배치가 스스로 확인하게 하시오.
커밋은 언제 하는지, 중간에 실패하면 어떻게 되는지, 다시 돌려도 안전한지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 반송함 (한 번 만든다)
CREATE TABLE T_STLM_ERR (
  RUN_TAG  VARCHAR2(20)  NOT NULL,
  STLM_SEQ NUMBER(18),
  CUS_ID   VARCHAR2(40),
  ERR_CD   VARCHAR2(10)  NOT NULL,
  ERR_MSG  VARCHAR2(200)
);

PROCEDURE ADJ_WEEKLY(p_tag VARCHAR2 DEFAULT TO_CHAR(SYSDATE, 'YYYYMMDD')) IS
  v_src NUMBER;
  v_adj NUMBER;
  v_err NUMBER;
BEGIN
  -- ① 반송함 — 이번 회차 것을 지우고 다시 쓴다 (몇 번을 돌려도 한 벌)
  DELETE FROM T_STLM_ERR WHERE RUN_TAG = p_tag;
  INSERT INTO T_STLM_ERR (RUN_TAG, STLM_SEQ, CUS_ID, ERR_CD, ERR_MSG)
  SELECT p_tag, s.STLM_SEQ, s.CUS_ID,
         CASE WHEN c.CUS_ID IS NULL THEN 'NO_CUS' ELSE 'NO_AMT' END,
         CASE WHEN c.CUS_ID IS NULL THEN '고객 마스터에 없음' ELSE '정산 금액 미확정' END
    FROM T_ORD_STLM s
    LEFT JOIN M_CUS c ON c.CUS_ID = s.CUS_ID
   WHERE c.CUS_ID IS NULL OR s.STLM_AMT IS NULL;
  v_err := SQL%ROWCOUNT;

  -- ② 보정 — 한 문장 (이력에 있으면 UPDATE · 없으면 INSERT). 원천은 ①의 정확한 반대 조건
  MERGE INTO T_STLM_ADJ a
  USING (SELECT s.STLM_SEQ, s.STLM_AMT,
                CASE c.CUS_GD WHEN 'A' THEN 0.001 WHEN 'B' THEN 0.002 ELSE 0.003 END AS ADJ_RATE
           FROM T_ORD_STLM s
           JOIN M_CUS c ON c.CUS_ID = s.CUS_ID
          WHERE s.STLM_AMT IS NOT NULL) x
     ON (a.STLM_SEQ = x.STLM_SEQ)
   WHEN MATCHED THEN UPDATE
        SET a.ADJ_RATE = x.ADJ_RATE, a.ADJ_AMT = ROUND(x.STLM_AMT * x.ADJ_RATE), a.ADJ_DT = SYSDATE
   WHEN NOT MATCHED THEN INSERT (STLM_SEQ, ADJ_RATE, ADJ_AMT, ADJ_DT)
        VALUES (x.STLM_SEQ, x.ADJ_RATE, ROUND(x.STLM_AMT * x.ADJ_RATE), SYSDATE);
  v_adj := SQL%ROWCOUNT;

  -- ③ 대조 — 보정 + 반송 = 원장. 안 맞으면 되돌리고 멈춘다
  SELECT COUNT(*) INTO v_src FROM T_ORD_STLM;
  IF v_adj + v_err <> v_src THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, '보정 대조 불일치: 원장 ' || v_src || ' / 보정 ' || v_adj || ' / 반송 ' || v_err);
  END IF;

  COMMIT;                     -- 한 번
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;                 -- 반쯤 반영을 남기지 않는다
    RAISE;                    -- 삼키지 않는다 — 배치는 실패로 끝나고 그대로 다시 돌린다
END;

개선 후 실행계획 실측 · 교재 15화(복사본, 보정률 함수를 루프 안 SELECT로 풀어 적은 판)

xplan

[세션 통계 — 같은 보정 (교재 15화 실측 [E] · 규칙을 SQL 로 · 반송은 따로)]
  경과 시간                  0.23 ~ 0.32 초
  recursive calls            58
  session logical reads      16,609
  redo size                  11.15 MB
  user commits               1

[정합성]
  원장 100,020 = 보정 99,850 + 반송 170 (NO_CUS 150 · NO_AMT 20) · 겹침 0 · 빠진 것 0
  같은 회차를 다시 돌리면 — 반송함은 회차를 지우고 다시 쓰고, MERGE 는 UPDATE 쪽으로 간다 → 같은 결과

모범 답안 서술

일량은 정산마다 SQL로 세 번 건너가는 데서 나온다. 등급 조회, COUNT(), INSERT(또는 UPDATE)를 100,020건마다 하니 recursive calls 30만, 논리 읽기 82만, redo 55.8MB가 쌓인다. SQL 한 문장 한 문장은 3블록이라 'SQL은 다 빠르다'는 말이 맞는데도 배치는 느리다. TKPROF에서 등급 조회의 Execute = Fetch = 100,020인 것이 행마다 호출한 한 건 조회의 흔적이다. 170건이 사라진 것은 WHEN OTHERS THEN NULL 때문이다. 탈퇴 고객 150건은 등급 조회에서 NO_DATA_FOUND로 실패했고(그래서 COUNT는 99,870번), 금액 미확정 20건은 ADJ_AMT NOT NULL에 걸려 ORA-01400으로 실패했다. 둘 다 예외 블록이 삼켜서 보정도 기록도 없이 넘어갔다. 정산 확정 화면은 그 170건을 '보정 0원'으로 보여 준다. 고친 배치의 중심은 MERGE 한 문장이다. 정산과 고객을 해시 조인해 등급별 보정률을 CASE로 붙이고, 이력에 있으면 UPDATE, 없으면 INSERT한다. COUNT()로 묻던 일은 MERGE의 ON이 대신한다. 보정할 수 없는 정산(고객 없음, 금액 미확정)은 MERGE의 원천에서 빼고, 정확히 반대 조건으로 반송함 T_STLM_ERR에 사유와 함께 넣는다. 논리 읽기는 816,425 → 16,609, redo는 55.8 → 11.2MB, 경과 시간은 2.2 → 0.3초로 준다(15화 실측). 정합성은 숫자로 확인한다. 보정 SQL%ROWCOUNT와 반송 SQL%ROWCOUNT를 더한 값이 원장 COUNT(*)와 같은지(99,850 + 170 = 100,020) 보고, 맞지 않으면 ROLLBACK한 뒤 에러로 멈춘다. 모르는 실패도 WHEN OTHERS에서 ROLLBACK한 뒤 RAISE로 다시 던진다. 예외를 삼키지 않는다. 커밋은 끝에 한 번 한다. 1,000건 커밋은 '트랜잭션은 짧게'라는 그때의 처방이었지만, 중간에 죽으면 반쯤 반영된 보정이 남는다. 한 문장 MERGE의 언두는 수 MB라서 언두가 모자랄 규모가 아니고, 읽는 원장을 고치지 않으니 fetch across commit에도 해당하지 않는다. 실패하면 아무것도 남지 않으므로 그대로 다시 돌린다. MERGE는 같은 주를 다시 돌려도 같은 값으로 덮고, 반송함은 회차(RUN_TAG)를 지우고 다시 쓰므로 몇 번을 돌려도 결과가 같다.

해설

규칙이 공유 함수라면(교재 15화의 실제 상황): 보정률이 정산팀 소유 함수에 있고 화면도 그 함수를 쓴다면, MERGE에 함수를 넣는 순간 함수가 100,020번 호출되어 루프가 문장 안에 숨는다. 함수 안의 NO_DATA_FOUND는 SQL 안에서 에러 없이 NULL이 된다. 그때는 BULK COLLECT … LIMIT과 FORALL, 고객마다 한 번 호출하는 등급표, SAVE EXCEPTIONS와 반송함을 함께 쓰는 것이 맞다(15화 rev01, 27 ~ 28cs).
LOG ERRORS로 반송을 대신할 수도 있지만, 오류가 0건이어도 집합 처리가 행 단위로 바뀐다(15화 실측 23 → 105 ~ 117cs, redo 11 → 56MB). 아는 실패는 조건으로 먼저 거른다.
대조의 기준은 원장이다. 직전 판(rev00)의 결과와 맞춰 보면 둘 다 170건을 빠뜨렸으므로 99,850 = 99,850으로 '통과'해 버린다(교재 08화 체크리스트 A). 건수뿐 아니라 보정액 합계도 규정대로 직접 계산한 값과 맞춰 본다.
원천이 겹치면: STLM_SEQ가 원장에서 겹치면(교재 12화 MAX+1 채번의 100001 × 3) MERGE는 ORA-30926으로 멈춘다. 멈추는 것이 맞다. 예외를 삼키면 다시 '깨끗한 에러 로그'가 된다.
언두가 정말 모자라는 규모라면 커밋 주기로 나누지 말고 키 범위(STLM_SEQ 구간)로 나눈 여러 문장으로 쪼개고, 회차마다 어디까지 했는지 적는다(교재 13화 「커밋 주기 기준」).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 15화(복사본, 보정률 함수를 루프 안 SELECT로 풀어 적은 판) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 15화(복사본, 보정률 함수를 루프 안 SELECT로 풀어 적은 판) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황