2022년 주문을 5,000건씩 보관 DB로 옮긴 작업은 '완료'인데 보관본이 27,816건 모자란다

중급INSERT한빛유통490 / 700

상황

한빛유통은 2022년 주문을 보관 DB로 옮긴 뒤 운영에서 지우기로 했다. DB 링크가 가끔 끊기므로 개발자는 5,000건씩 옮기고, 어디까지 옮겼는지를 체크포인트 테이블에 적어 끊기면 그다음부터 다시 돌게 했다. 일요일 밤 작업은 62회차 만에 '완료'로 끝났다. 다음 단계(운영 원본 삭제)는 건수 대조에서 멈췄다. 원본은 333,245건인데 보관본은 305,429건이었다. 박팀장: "에러도 없고 링크도 한 번도 안 끊겼다는데, 2만 8천 건이 어디로 갔습니까?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT는 2022-01-01 ~ 2024-12-31이고 전부 자정이다(시분초 없음, 하루 약 913건) · 2022년 333,245건 · ORD_SEQ는 값이 유일하다(인덱스 없음)
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)
T_ORD_ARC@ARC: 보관 DB의 보관 테이블(같은 컬럼). 작업 전에는 비어 있었고, 보관 DB를 쓰는 다른 작업은 없다.
T_MIG_CKPT (JOB_NM, LAST_DT): 체크포인트 테이블(이 문제용). 작업 시작 전 LAST_DT는 2021-12-31이었다.

원본 SQL

sql

-- 일요일 22:00 · 2022년 주문 보관 이관 (끊기면 같은 블록을 다시 실행하면 이어서 돈다)
DECLARE
  v_last DATE;
  v_rows NUMBER;
BEGIN
  LOOP
    SELECT LAST_DT INTO v_last FROM T_MIG_CKPT WHERE JOB_NM = 'ORD_ARC_2022';

    INSERT INTO T_ORD_ARC@ARC (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
    SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
      FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
              FROM T_ORD_BIG
             WHERE ORD_DT > v_last
               AND ORD_DT < DATE '2023-01-01'
             ORDER BY ORD_DT)
     WHERE ROWNUM <= 5000;
    v_rows := SQL%ROWCOUNT;
    EXIT WHEN v_rows = 0;

    UPDATE T_MIG_CKPT                                   -- 이번에 옮긴 마지막 주문일
       SET LAST_DT = (SELECT MAX(ORD_DT) FROM T_ORD_ARC@ARC)
     WHERE JOB_NM = 'ORD_ARC_2022';
    COMMIT;
  END LOOP;
END;

실행계획 학습용 예시

xplan

[회차별 기록 — 체크포인트와 넣은 행]
  회차   넣은 행   이번 회차의 마지막 ORD_DT (LAST_DT)
    1     5,000    2022-01-06
    2     5,000    2022-01-12
    3     5,000    2022-01-17
    …       …      …
   61     5,000    2022-12-26
   62       429    2022-12-31
   63         0    → 끝 · 스케줄러 상태 SUCCEEDED · 링크 끊김 0번

[대조 — 원본 삭제 단계가 멈춘 이유]
  원본 2022년 333,245 · 보관본 305,429 · 차이 27,816 → ORA-20010 (삭제 단계 대조 불일치)
  하루 치가 일부만 들어간 날 61일 · 예) 2022-01-06 원본 911행, 보관본 433행

[회차 하나의 SELECT — DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST')]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |   5000 |00:00:00.04 |    5031 |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |   5000 |00:00:00.04 |    5031 |
|   2 |   VIEW                         |                   |      1 |   5000 |   5000 |00:00:00.04 |    5031 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |   5000 |   5000 |00:00:00.03 |    5031 |
|*  4 |     INDEX RANGE SCAN           | IX_ORD_BIG_DT_AMT |      1 |   5000 |   5000 |00:00:00.01 |      18 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5000)
   4 - access("ORD_DT">:B1 AND "ORD_DT"<TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

문제

링크가 한 번도 끊기지 않았는데 27,816건이 빠진 이유를 회차별 기록과 ORD_DT의 분포로 설명하시오.
빠짐없이 옮기고, 끊긴 뒤 다시 돌려도 빠지거나 겹치지 않도록 이관을 다시 작성하시오. 체크포인트에 무엇을 적어야 하는지도 쓰시오.
이번 사고에서 원본을 지키는 데 실제로 효과가 있었던 장치와, 고친 판에서도 남겨야 할 확인을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만: 체크포인트는 '끝낸 날'을 적는 표로 (날마다 한 행)
CREATE TABLE T_MIG_DONE (
  JOB_NM    VARCHAR2(30),
  DONE_DT   DATE,
  ROWS_DONE NUMBER,
  DONE_AT   DATE,
  CONSTRAINT PK_T_MIG_DONE PRIMARY KEY (JOB_NM, DONE_DT)
);

-- 보관본을 비우고 다시 옮긴다 (보관 DB 를 쓰는 다른 작업은 없다)
DELETE FROM T_ORD_ARC@ARC WHERE ORD_DT >= DATE '2022-01-01' AND ORD_DT < DATE '2023-01-01';
COMMIT;

DECLARE
  v_d    DATE;
  v_rows NUMBER;
BEGIN
  -- 끝낸 날의 다음 날부터 (처음이면 1월 1일) · 끊긴 뒤 다시 돌려도 같은 블록을 그대로 실행한다
  SELECT NVL(MAX(DONE_DT) + 1, DATE '2022-01-01') INTO v_d
    FROM T_MIG_DONE WHERE JOB_NM = 'ORD_ARC_2022';

  WHILE v_d < DATE '2023-01-01' LOOP
    INSERT INTO T_ORD_ARC@ARC (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
    SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
      FROM T_ORD_BIG
     WHERE ORD_DT >= v_d AND ORD_DT < v_d + 1;      -- 하루를 통째로: 같은 날이 두 청크에 걸치지 않는다
    v_rows := SQL%ROWCOUNT;

    INSERT INTO T_MIG_DONE (JOB_NM, DONE_DT, ROWS_DONE, DONE_AT)
    VALUES ('ORD_ARC_2022', v_d, v_rows, SYSDATE);
    COMMIT;                                         -- 원격 INSERT 와 체크포인트가 한 트랜잭션 (2단계 커밋)
    v_d := v_d + 1;
  END LOOP;
END;

-- 끝에 대조: 날마다 원본 건수 = 체크포인트 건수, 합계 333,245 = 보관본 건수
SELECT o.ORD_DT, o.CNT, d.ROWS_DONE
  FROM (SELECT ORD_DT, COUNT(*) AS CNT FROM T_ORD_BIG
         WHERE ORD_DT >= DATE '2022-01-01' AND ORD_DT < DATE '2023-01-01'
         GROUP BY ORD_DT) o
  LEFT JOIN T_MIG_DONE d ON d.JOB_NM = 'ORD_ARC_2022' AND d.DONE_DT = o.ORD_DT
 WHERE d.ROWS_DONE IS NULL OR d.ROWS_DONE <> o.CNT;          -- 0 행이어야 원본 삭제로 간다

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

xplan

[청크 하나 — 2022-01-06 하루]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |    911 |00:00:00.01 |     917 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |    913 |    911 |00:00:00.01 |     917 |
|*  2 |   INDEX RANGE SCAN           | IX_ORD_BIG_DT_AMT |      1 |    913 |    911 |00:00:00.01 |       6 |
----------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT">=:B2 AND "ORD_DT"<:B1)

[작업 전체]
  청크 365개(하루씩) · 보관본 333,245 = 원본 333,245 · 대조 불일치 0행
  중간에 링크를 끊어 본 시험: 끊긴 날은 롤백되어 체크포인트에도 없고, 다시 돌리면 그날부터 이어서 옮긴다

모범 답안 서술

T_ORD_BIG의 ORD_DT는 전부 자정이라 같은 날의 주문 약 913건이 모두 같은 값이다. 원본은 ORDER BY ORD_DT로 5,000건을 자르는데, 5,000번째 행은 대개 어느 날의 한가운데에서 끝난다. 1회차는 1월 1 ~ 5일을 다 옮기고 1월 6일의 911건 중 433건까지만 옮겼다. 체크포인트에는 마지막 주문일 2022-01-06이 적혔고, 2회차는 ORD_DT > '2022-01-06'으로 1월 7일부터 읽었다. 1월 6일의 나머지 478건은 아무 회차도 읽지 않았다. 회차가 바뀔 때마다 끊긴 날의 나머지(평균 약 456건)가 이렇게 빠져서 61번의 경계에서 27,816건이 사라졌다. 에러도 링크 끊김도 없었고, 마지막 회차가 0행이어서 작업은 '완료'로 끝났다. 문제는 경계를 '마지막으로 본 값'으로 잡은 데 있다. 그 값이 유일하지 않으면 >는 같은 값의 남은 행을 건너뛰고, >=로 바꾸면 이미 옮긴 행을 다시 옮겨 겹친다. 고친 판은 청크를 하루 단위 범위(ORD_DT >= :d AND ORD_DT < :d + 1)로 잡는다. 같은 날이 두 청크에 걸치지 않으므로 빠지지도 겹치지도 않는다. 체크포인트에는 '다 옮긴 날'을 한 행씩 적고, 다시 시작할 때는 마지막으로 끝낸 날의 다음 날부터 돈다. 원격 INSERT와 체크포인트 INSERT를 같은 트랜잭션에서 커밋하므로 DB 링크 너머까지 2단계 커밋으로 함께 확정된다. 링크가 그날 도중에 끊기면 그날의 원격 INSERT도, 체크포인트도 롤백되므로 '옮겼는데 기록이 없는' 날이나 '기록만 있는' 날이 생기지 않는다. 날마다 넣은 행 수를 체크포인트에 같이 적어 두면 끝에 원본의 일별 건수와 맞춰 볼 수 있다. 이번에 원본을 지킨 것은 삭제 단계 앞의 건수 대조였다. 그것이 없었다면 2만 8천 건을 보관본에도 없이 지웠을 것이다. 고친 판도 날마다 건수를 적고, 끝에 일별로 원본과 대조해 0행일 때만 원본 삭제로 넘어간다.

해설

유일하지 않은 키로 이어 읽기: 화면의 키 기반 페이징에서 정렬 키가 유일하지 않으면 다음 페이지에서 행이 빠지거나 겹치는 것과 같은 원리다(SORT 단원). 배치에서는 그 결과가 데이터 유실이 된다. 이어 읽을 키는 유일해야 하고, 유일하지 않으면 (ORD_DT, ORD_SEQ)처럼 유일해질 때까지 컬럼을 더하거나 범위를 통째로 끊는다.
'완료'의 기준: 마지막 회차 0행은 '더 읽을 것이 없다'일 뿐 '다 옮겼다'가 아니다. 이관의 완료 조건은 원본과 사본의 건수(가능하면 합계까지) 대조로 정한다(교재 08화 체크리스트 A).
다른 정답: ORD_SEQ에 인덱스를 만들고 ORD_SEQ > :last ORDER BY ORD_SEQ로 이어 읽어도 된다(유일한 키). 하루 단위 범위는 지금 있는 인덱스로 되고 일별 대조와 맞물려 이 작업에 더 맞는다. ROWID 범위(DBMS_PARALLEL_EXECUTE)로 나눠도 빠지지 않지만, 2022년이 테이블 곳곳에 흩어져 있다면 청크마다 다른 해의 행까지 읽는다.
원본 삭제는 이관과 다른 트랜잭션이다. 이관이 끝나고 대조가 통과한 뒤 따로 지우되, 지우는 방법(DELETE와 바꿔 끼우기)은 지울 비율로 고른다(DML-044).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황