휴면 예정 안내 배치가 어떤 날은 0통, 어떤 날은 어제 보낸 고객에게 또 보낸다

중급PLSQL이커머스485 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스 개발팀은 매일 02:00에 배치 프레임워크로 「휴면 예정 안내」 잡을 돌린다. 11개월 동안 주문이 없는 고객을 임시 테이블에 담고(①), 메일 발송 대기열에 넣고(②), 고객에 안내일을 표시한다(③). 9월에는 대기열이 매일 0건이었다. 개발자가 임시 테이블을 ON COMMIT PRESERVE ROWS로 바꾼 뒤로는 날마다 건수가 들쭉날쭉하다. 10월 24일 고객센터에 "휴면 안내 메일을 이틀 사이에 두 번 받았다"는 문의가 몰렸다. 개발자: "임시 테이블이 세션마다 따로라던데, 우리 잡은 한 프로그램이 도는 거니까 세션도 하나 아닌가요?"

테이블과 인덱스

customers: 5,230행 · 51블록 · PK customer_id · UQ email. 이 문제에서는 휴면 예정 안내일 dormant_notice_dt DATE 칸이 더 있다(NULL이면 아직 안내 전).
orders: 34,908행 · 193블록 · order_date DATE(시분초 있음) · PK만 있다.
gt_dormant_target(전역 임시 테이블, 이 문제용): 원본 SQL 맨 위의 DDL이다. 9월까지는 ON COMMIT 절이 없었다.
notice_queue(메일 발송 대기열, 이 문제용): 발송 서버가 5분마다 읽어 보낸다. 보낸 메일은 되돌릴 수 없다.
배치 프레임워크: DB 연결 풀 5개(세션은 WAS를 재기동할 때까지 며칠씩 살아 있다). 잡의 단계마다 풀에서 연결을 하나 빌려 SQL을 실행하고 커밋한 뒤 돌려준다. 어느 연결을 빌릴지는 정해져 있지 않다.

원본 SQL

sql

-- 임시 테이블 (지난달 수정: ON COMMIT 절 없음 → PRESERVE ROWS)
CREATE GLOBAL TEMPORARY TABLE gt_dormant_target (
  customer_id   NUMBER,
  email         VARCHAR2(200),
  last_order_dt DATE
) ON COMMIT PRESERVE ROWS;

-- 잡 「휴면 예정 안내」 · 단계마다 풀에서 연결을 빌려 실행하고 커밋한다 (:base_dt = 실행일 0시)
-- step1
INSERT INTO gt_dormant_target (customer_id, email, last_order_dt)
SELECT c.customer_id, c.email, MAX(o.order_date)
  FROM customers c JOIN orders o ON o.customer_id = c.customer_id
 WHERE c.dormant_notice_dt IS NULL
 GROUP BY c.customer_id, c.email
HAVING MAX(o.order_date) < ADD_MONTHS(:base_dt, -11);

-- step2
INSERT INTO notice_queue (customer_id, email, notice_tp, req_dt)
SELECT customer_id, email, 'DORMANT', SYSDATE
  FROM gt_dormant_target;

-- step3
UPDATE customers
   SET dormant_notice_dt = :base_dt
 WHERE customer_id IN (SELECT customer_id FROM gt_dormant_target);

실행계획 학습용 예시

xplan

[잡 실행 기록 — 단계별 처리 건수와 빌려 쓴 세션]
  실행일    step1 (SID · 건수)   step2 (SID · 건수)   step3 (SID · 건수)
  10/21     412 ·  298           412 ·  298           415 ·    0
  10/22     415 ·  305           388 ·    0           412 ·  298
  10/23     412 ·   13           412 ·  311           412 ·  311

[10/24 아침에 센 것]
  이틀 사이 같은 메일을 두 번 받은 고객        298명 (10/21 · 10/23)
  세션 415 의 gt_dormant_target 에 남은 행     305행 (10/22 step1 이 넣은 것)

[9월 — ON COMMIT 절이 없던 때 (매일 같은 모양)]
  step1 290 ~ 300 · step2 0 · step3 0

[딕셔너리]
  SELECT temporary, duration FROM user_tables WHERE table_name = 'GT_DORMANT_TARGET';
  Y   SYS$SESSION

문제

사흘 동안 단계마다 건수가 서로 어긋난 이유를 세션 번호로 설명하시오. ON COMMIT 절이 없던 9월에 step2와 step3이 늘 0건이던 이유도 쓰시오.
단계가 어느 연결에서 돌든 결과가 같고, 다시 돌려도 같은 고객에게 두 번 보내지 않도록 배치를 고치시오.
고친 판으로 바꾸기 전에 할 일(남아 있는 임시 테이블 행)과, 이미 두 번 보낸 298명을 어떻게 다룰지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 임시 테이블은 '한 트랜잭션 안의 작업장'으로 되돌린다 (WAS 를 재기동해 풀 세션을 비운 뒤에 바꾼다)
DROP TABLE gt_dormant_target;
CREATE GLOBAL TEMPORARY TABLE gt_dormant_target (
  customer_id   NUMBER,
  email         VARCHAR2(200),
  last_order_dt DATE
) ON COMMIT DELETE ROWS;

-- ② 세 단계를 프로시저 하나로 · 잡은 이 프로시저를 한 번만 부른다 → 세션 하나 · 트랜잭션 하나
CREATE OR REPLACE PROCEDURE p_dormant_notice(p_base_dt DATE) IS
  v_tgt PLS_INTEGER;
  v_q   PLS_INTEGER;
  v_upd PLS_INTEGER;
BEGIN
  INSERT INTO gt_dormant_target (customer_id, email, last_order_dt)
  SELECT c.customer_id, c.email, MAX(o.order_date)
    FROM customers c JOIN orders o ON o.customer_id = c.customer_id
   WHERE c.dormant_notice_dt IS NULL                       -- 이미 안내한 고객은 다시 담지 않는다
   GROUP BY c.customer_id, c.email
  HAVING MAX(o.order_date) < ADD_MONTHS(p_base_dt, -11);
  v_tgt := SQL%ROWCOUNT;

  INSERT INTO notice_queue (customer_id, email, notice_tp, req_dt)
  SELECT customer_id, email, 'DORMANT', SYSDATE
    FROM gt_dormant_target;
  v_q := SQL%ROWCOUNT;

  UPDATE customers c
     SET c.dormant_notice_dt = p_base_dt
   WHERE c.customer_id IN (SELECT t.customer_id FROM gt_dormant_target t)
     AND c.dormant_notice_dt IS NULL;
  v_upd := SQL%ROWCOUNT;

  IF v_q <> v_tgt OR v_upd <> v_tgt THEN                  -- 담은 수 = 대기열 = 표시
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, '휴면 안내 건수 불일치 ' || v_tgt || '/' || v_q || '/' || v_upd);
  END IF;
  COMMIT;   -- 대기열과 표시가 함께 확정된다 · 이 순간 임시 테이블도 비워진다
EXCEPTION
  WHEN OTHERS THEN ROLLBACK; RAISE;
END;

-- ③ 잡 설정: step1 ~ step3 을 지우고 한 단계로   { call p_dormant_notice(?) }

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

xplan

[고친 뒤 — 잡 실행 기록]
  실행일    호출 (SID)   담음   대기열   표시   커밋
  10/25     388          9      9        9      1
  10/26     412          8      8        8      1
  10/26 재실행(시험)     0      0        0      1     ← 표시된 고객은 담지 않는다
  단계 사이에 다른 연결을 빌리는 일이 없다 · 커밋 뒤 임시 테이블에 남는 행 0

모범 답안 서술

임시 테이블의 행은 '프로그램'이 아니라 행을 넣은 DB 세션에서만 보인다. 그런데 프레임워크는 단계마다 풀에서 연결을 하나씩 빌리므로, 한 잡의 세 단계가 서로 다른 세션에서 돌 수 있다. 10/21에는 step1과 step2가 같은 세션 412를 빌려 298명을 대기열에 넣었지만, step3은 세션 415를 빌려 빈 임시 테이블을 읽고 0명을 표시했다. 10/22에는 step2가 빈 세션 388을 빌려 0건을 넣었고, step3은 세션 412를 빌렸다. PRESERVE ROWS의 행은 세션이 끝날 때까지 남으므로 412에는 10/21의 298행이 그대로 있었고, step3은 어제의 대상 298명을 표시했다. 10/23에는 세 단계 모두 412를 빌렸는데, 412에는 10/21의 298행에 오늘 담은 13행이 더해져 311행이 있었다. 그래서 10/21에 메일을 받은 298명이 10/23에 또 받았다. 세션 415에는 지금도 10/22에 담은 305행이 남아 있다. 9월에 step2와 step3이 늘 0건이던 것은 ON COMMIT 절이 없으면 DELETE ROWS이기 때문이다. 프레임워크가 단계마다 커밋하므로 step1의 행은 step1 끝의 커밋에 지워졌다. 어느 쪽이든 '단계 사이에 임시 테이블로 결과를 넘긴다'는 설계가 이 실행 환경과 맞지 않는다. 고친 판은 세 단계를 프로시저 하나로 묶고 잡은 그 프로시저를 한 번만 부른다. 세션이 하나, 트랜잭션이 하나이므로 임시 테이블은 DELETE ROWS로 되돌려도 되고, 커밋에서 저절로 비워져 남는 행이 없다. 대기열 적재와 안내일 표시가 같은 대상으로 한 트랜잭션에서 확정되고, 담은 수와 대기열 수와 표시 수가 다르면 롤백하고 멈춘다. step1이 표시된 고객을 거르므로 다시 돌려도 같은 고객을 다시 담지 않는다. 바꾸기 전에 WAS를 재기동해 풀 세션을 모두 닫는다. 세션 415처럼 행을 쥔 세션이 있으면 임시 테이블을 DROP할 수 없고(ORA-14452), 그 행은 세션이 닫힐 때 사라진다. 이미 두 번 보낸 298명은 되돌릴 수 없으므로 고객센터에 명단을 넘기고, 10/23에 표시까지 끝났으니 다시 보내지는 않는다.

해설

회차 번호 방식: 프레임워크 구조상 단계를 꼭 나눠야 한다면 중간 결과를 영구 테이블에 run_id(잡 실행 번호)와 함께 넣고, 단계마다 WHERE run_id = :run으로 읽는다. 어느 연결에서 돌든 같은 행을 보고 재시작도 할 수 있다. 대신 행마다 redo가 생기고, 끝난 회차를 지우는 단계가 따로 든다.
세션 상태에 기대는 것은 임시 테이블만이 아니다: 패키지 변수, ALTER SESSION으로 바꾼 설정, DBMS_SESSION.SET_CONTEXT 값도 풀 연결에서는 다음에 빌리는 쪽에 남거나 사라진다. 연결 풀 아래에서는 '한 단계 = 한 호출 안에서 끝나는 일'로 설계한다.
발송과 표시: 실제 발송은 발송 서버가 대기열을 읽어서 한다. 대기열 적재와 표시를 한 트랜잭션에 묶어 두면 '대기열에 있는데 표시가 없는' 상태가 생기지 않는다. 외부 발송을 DB 루프 안에서 직접 부르는 경우의 재처리 설계는 DML-016을 참고한다.
이 잡은 orders 193블록과 customers 51블록을 한 번 해시 조인한다. 성능이 아니라 실행 환경과 임시 테이블 수명의 문제다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황