지역을 나눠 두 사람이 동시에 돌린 정산 명세에서 지역 1 ~ 4가 통째로 빠지고 5 ~ 8은 두 벌이 됐다

기초PLSQL한빛유통484 / 700

상황

정산팀의 「고객별 정산 명세 생성」 프로시저는 원래 매달 1일 밤에 전 지역을 한 번에 돌렸다. 이번 달부터 고객센터가 월초 오전에 명세를 빨리 달라고 해서, 조민우와 동료 한 명이 화면에서 지역 묶음을 나눠(1 ~ 4, 5 ~ 8) 동시에 돌리기로 했다. 11월 1일 09:00에 두 실행이 모두 '완료'로 끝났는데, 고객센터에서 지역 1 ~ 4 고객의 명세가 하나도 없고 지역 5 ~ 8 고객은 명세가 두 장씩 나온다는 연락이 왔다. 이튿날 다시 돌리자 이번에는 한쪽이 ORA-00054로 실패했다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · 10월 정산 전체 · 인덱스 없음
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · RGN_ID 8개 지역. 지역 1 ~ 4 고객 1,012명(정산 50,410건), 지역 5 ~ 8 고객 988명(정산 49,590건)
W_STMT(작업 테이블, 이 문제용): 보통의 영구 테이블 · LOGGING · 인덱스 없음 · 명세 머리와 명세 줄 두 단계가 함께 읽는다.
T_STMT(명세 머리: 고객별) · T_STMT_DAY(명세 줄: 고객 × 정산일) · 둘 다 이 문제용 테이블이다.
운영은 ARCHIVELOG다. 다른 배치는 이 시간에 W_STMT를 쓰지 않는다.

원본 SQL

sql

PROCEDURE P_STMT_BUILD(p_ym VARCHAR2, p_rgn_from VARCHAR2, p_rgn_to VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE W_STMT';                          -- ① 작업 테이블을 비운다

  INSERT INTO W_STMT (STLM_SEQ, CUS_ID, RGN_ID, STLM_DT, STLM_AMT)    -- ② 이번 지역 묶음의 정산
  SELECT s.STLM_SEQ, s.CUS_ID, c.RGN_ID, s.STLM_DT, s.STLM_AMT
    FROM T_ORD_STLM s, M_CUS c
   WHERE c.CUS_ID = s.CUS_ID
     AND c.RGN_ID BETWEEN p_rgn_from AND p_rgn_to;
  COMMIT;

  INSERT INTO T_STMT (STMT_YM, CUS_ID, RGN_ID, STLM_CNT, STLM_AMT)      -- ③ 명세 머리
  SELECT p_ym, CUS_ID, RGN_ID, COUNT(*), SUM(STLM_AMT)
    FROM W_STMT
   GROUP BY CUS_ID, RGN_ID;

  INSERT INTO T_STMT_DAY (STMT_YM, CUS_ID, STLM_DT, STLM_CNT, STLM_AMT) -- ④ 명세 줄
  SELECT p_ym, CUS_ID, TRUNC(STLM_DT), COUNT(*), SUM(STLM_AMT)
    FROM W_STMT
   GROUP BY CUS_ID, TRUNC(STLM_DT);
  COMMIT;
END;

실행계획 학습용 예시

xplan

[11월 1일 — 두 세션의 실행 기록]
  시각         세션             단계
  09:00:02.1   A (지역 1 ~ 4)   ① TRUNCATE W_STMT
  09:00:02.6   A                ② W_STMT 50,410행 · COMMIT
  09:00:03.0   B (지역 5 ~ 8)   ① TRUNCATE W_STMT
  09:00:03.4   B                ② W_STMT 49,590행 · COMMIT
  09:00:03.9   A                ③ T_STMT 988행 · ④ T_STMT_DAY 21,874행 · COMMIT · 완료
  09:00:04.0   B                ③ T_STMT 988행 · ④ T_STMT_DAY 21,874행 · COMMIT · 완료

[T_STMT — 10월분 대조]
  지역 1 ~ 4   고객 1,012명   명세 머리      0행
  지역 5 ~ 8   고객   988명   명세 머리  1,976행 (고객마다 두 행)

[11월 2일 — 다시 돌렸을 때]
  09:10:01.2   A   ① TRUNCATE · ② INSERT 시작
  09:10:01.5   B   ① TRUNCATE W_STMT
                   ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

[세션 통계 — 실행 한 번 (참고)]
  redo size   약 5.1 MB (그중 W_STMT 적재 약 4.6 MB)

문제

지역 1 ~ 4의 명세가 0행이고 지역 5 ~ 8이 두 벌이 된 이유와, 11월 2일 ORA-00054가 난 이유를 실행 기록으로 설명하시오.
두 사람이 동시에 돌려도 서로 섞이지 않도록 고치시오. 고친 방법에서 작업 테이블의 행이 언제 사라지는지와, 같은 달을 다시 돌려도 명세가 두 벌이 되지 않게 하는 방법도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만 (배포 때): 작업 테이블을 세션마다 따로 보이는 임시 테이블로
CREATE GLOBAL TEMPORARY TABLE GT_STMT (
  STLM_SEQ NUMBER,
  CUS_ID   VARCHAR2(40),
  RGN_ID   VARCHAR2(10),
  STLM_DT  DATE,
  STLM_AMT NUMBER
) ON COMMIT DELETE ROWS;          -- 이 트랜잭션이 끝나면 비워진다 · 다른 세션은 처음부터 못 본다

PROCEDURE P_STMT_BUILD(p_ym VARCHAR2, p_rgn_from VARCHAR2, p_rgn_to VARCHAR2) IS
BEGIN
  -- ② 이번 지역 묶음의 정산 (TRUNCATE 가 필요 없다: 새 트랜잭션의 임시 테이블은 비어 있다)
  INSERT INTO GT_STMT (STLM_SEQ, CUS_ID, RGN_ID, STLM_DT, STLM_AMT)
  SELECT s.STLM_SEQ, s.CUS_ID, c.RGN_ID, s.STLM_DT, s.STLM_AMT
    FROM T_ORD_STLM s, M_CUS c
   WHERE c.CUS_ID = s.CUS_ID
     AND c.RGN_ID BETWEEN p_rgn_from AND p_rgn_to;

  -- 같은 달 같은 지역을 다시 돌려도 한 벌만 남도록 먼저 지운다
  DELETE FROM T_STMT_DAY d
   WHERE d.STMT_YM = p_ym
     AND d.CUS_ID IN (SELECT c.CUS_ID FROM M_CUS c WHERE c.RGN_ID BETWEEN p_rgn_from AND p_rgn_to);
  DELETE FROM T_STMT
   WHERE STMT_YM = p_ym
     AND RGN_ID BETWEEN p_rgn_from AND p_rgn_to;

  INSERT INTO T_STMT (STMT_YM, CUS_ID, RGN_ID, STLM_CNT, STLM_AMT)
  SELECT p_ym, CUS_ID, RGN_ID, COUNT(*), SUM(STLM_AMT)
    FROM GT_STMT
   GROUP BY CUS_ID, RGN_ID;

  INSERT INTO T_STMT_DAY (STMT_YM, CUS_ID, STLM_DT, STLM_CNT, STLM_AMT)
  SELECT p_ym, CUS_ID, TRUNC(STLM_DT), COUNT(*), SUM(STLM_AMT)
    FROM GT_STMT
   GROUP BY CUS_ID, TRUNC(STLM_DT);

  COMMIT;            -- 한 번 · 이 순간 GT_STMT 의 이 세션 행이 비워진다
EXCEPTION
  WHEN OTHERS THEN ROLLBACK; RAISE;
END;

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

xplan

[11월 1일 다시 — 두 세션 동시 실행]
  A (지역 1 ~ 4)   GT_STMT 50,410행 → T_STMT 1,012행 · COMMIT
  B (지역 5 ~ 8)   GT_STMT 49,590행 → T_STMT   988행 · COMMIT
  서로 기다린 시간 0 (TRUNCATE 없음 · 서로 다른 행만 지우고 넣는다)
  같은 지역을 한 번 더 돌린 뒤에도 T_STMT 10월분 2,000행 (고객마다 한 행)

[세션 통계 — 실행 한 번 (참고)]
  redo size   약 5.1 MB → 약 1.2 MB (임시 테이블 블록은 redo 를 남기지 않는다 · undo 의 redo 만 남는다)

모범 답안 서술

W_STMT는 보통의 영구 테이블이라 모든 세션이 같은 행을 본다. A가 ②에서 지역 1 ~ 4의 정산 50,410행을 넣고 커밋한 직후(09:00:02.6), B가 ①의 TRUNCATE로 테이블을 비웠다. TRUNCATE는 행을 고르지 않고 세그먼트를 통째로 비우므로 A가 커밋해 둔 지역 1 ~ 4가 날아갔다. 이어서 B가 지역 5 ~ 8의 49,590행을 넣고 커밋했고, A의 ③④는 그 행을 읽어 지역 5 ~ 8 고객 988명의 명세를 만들었다. B도 같은 행으로 같은 명세를 만들었으니 지역 5 ~ 8은 두 벌, 지역 1 ~ 4는 0행이 됐다. 두 실행 모두 에러 없이 '완료'다. 11월 2일의 ORA-00054는 TRUNCATE가 DDL이기 때문이다. DDL은 테이블에 배타 잠금을 잡아야 하는데, A의 ② INSERT가 아직 커밋되지 않아 TM 잠금을 쥐고 있었다. DDL은 기다리지 않고 바로 실패한다. 실행 시각이 조금만 어긋나면 첫날처럼 남의 행을 지우고, 겹치면 실패하는 구조다. 고친 판은 작업 테이블을 전역 임시 테이블 GT_STMT로 바꾼다. 임시 테이블의 행은 넣은 세션에만 보이므로 두 세션이 같은 이름을 써도 서로의 행을 보지도, 지우지도 못한다. ON COMMIT DELETE ROWS로 만들고 커밋을 끝에 한 번만 하므로, 행은 이 트랜잭션이 커밋(또는 롤백)할 때 사라진다. 비우는 TRUNCATE가 필요 없어져 ORA-00054도 없어진다. 테이블 블록에 redo를 남기지 않아 실행 한 번의 redo도 약 5.1 → 1.2MB로 준다. 같은 달을 다시 돌려도 두 벌이 되지 않도록, 넣기 전에 그 달 그 지역 명세를 DELETE로 지운다. 지우기와 넣기가 한 트랜잭션이라 화면은 커밋 전까지 옛 명세를 보고, 실패하면 옛 명세가 그대로 남는다.

해설

PRESERVE ROWS가 필요한 경우: 단계 사이에 꼭 커밋해야 한다면 ON COMMIT PRESERVE ROWS로 만든다. 행은 세션이 끝날 때까지 남으므로 시작할 때 TRUNCATE TABLE GT_STMT(임시 테이블에는 자기 세션의 행만 비우는 동작이다)로 비운다. 커밋과 임시 테이블의 수명 관계는 DML-058을 참고한다.
회차 번호 방식: 영구 테이블에 RUN_ID를 두고 단계마다 WHERE RUN_ID = :run으로 읽는 방법도 섞이지 않는다. 다른 세션이나 다음 날에도 중간 결과를 볼 수 있어 재시작에 유리하지만, 행마다 redo가 생기고 끝난 회차를 지우는 일이 따로 든다.
작업 테이블이 꼭 있어야 하나: ③과 ④는 둘 다 T_ORD_STLM 640블록과 M_CUS를 조인해서 바로 집계할 수도 있다. 두 번 읽어도 1,300블록 남짓이다. 작업 테이블은 중간 결과를 만드는 일이 비쌀 때 값을 한다.
동시에 돌릴 수 있는가부터 묻는다: 혼자 도는 것을 전제로 짠 배치(작업 테이블 TRUNCATE, 지역 조건 없는 집계)를 동시에 돌리면 에러 없이 결과가 섞인다. 배치 머리에 '동시 실행 가능 여부'를 적어 두는 것이 T3 리포트 7번 칸의 일이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황