발주 수량 환산 배치가 500건째에서 멈췄고, 다시 돌리자 수량이 144배가 됐다

중급UPDATE유통437 / 700

상황

협력사 P007이 포장 단위를 박스(12개)에서 낱개로 바꾸면서, 그 협력사의 진행 중 발주상품 수량을 낱개로 환산(12배)하기로 했다. 개발자는 사내 배치 표준의 "트랜잭션은 짧게"에 따라 500건마다 커밋을 넣었다. 07:10 첫 실행은 ORA-01002로 멈췄고, 07:24에 다시 돌렸는데 같은 자리에서 또 멈췄다. 09:30에 구매팀이 전화했다. "P007 발주 수량이 이상해요. 어떤 건 그대로고 어떤 건 엄청 커요."

테이블과 인덱스

CF12 발주: 734행 · 10블록 · PK ORD_NO · P007의 진행 중(ORD_PRGS_CD '10', '20') 발주 139건
CF13 발주상품: 7,102행 · 57블록 · PK (ORD_NO, ITM_NO) · 대상 1,384행. 이 문제에서는 단위 칸 ORD_UNIT_CD('BOX' · 'EA')가 더 있고, 대상은 모두 'BOX'다. 배치는 이 칸을 건드리지 않았다.
CF13_BAK_0610: 운영 표준에 따라 07:09에 떠 둔 CF13 전체 사본(CTAS)이다. 그 뒤로 대상 행을 고친 것은 이 배치뿐이다(감사 로그로 확인).

원본 SQL

sql

DECLARE
  CURSOR c IS
    SELECT d.ORD_QTY
      FROM CF13 d
     WHERE d.ORD_NO IN (SELECT h.ORD_NO FROM CF12 h
                         WHERE h.PTNR_NO = 'P007'
                           AND h.ORD_PRGS_CD IN ('10', '20'))
       FOR UPDATE OF d.ORD_QTY;
  n PLS_INTEGER := 0;
BEGIN
  FOR r IN c LOOP
    UPDATE CF13 SET ORD_QTY = ORD_QTY * 12 WHERE CURRENT OF c;   -- 박스 → 낱개
    n := n + 1;
    IF MOD(n, 500) = 0 THEN COMMIT; END IF;                       -- 트랜잭션은 짧게 (사내 배치 표준)
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[배치 로그]
  07:10:02  시작 · 대상 1,384행
  07:10:03  500행 처리 · COMMIT
  07:10:03  ORA-01002: fetch out of sequence
  07:24:40  재실행 · 대상 1,384행
  07:24:41  500행 처리 · COMMIT
  07:24:41  ORA-01002: fetch out of sequence

[커서의 실행계획 — 첫 실행 (DISPLAY_CURSOR · ALLSTATS LAST)]
-------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |    500 |00:00:00.01 |    1453 |
|   1 |  FOR UPDATE             |      |      1 |        |    500 |00:00:00.01 |    1453 |
|   2 |   BUFFER SORT           |      |      1 |        |   1384 |00:00:00.01 |      67 |
|*  3 |    HASH JOIN RIGHT SEMI |      |      1 |   1402 |   1384 |00:00:00.01 |      67 |
|*  4 |     TABLE ACCESS FULL   | CF12 |      1 |    141 |    139 |00:00:00.01 |      10 |
|   5 |     TABLE ACCESS FULL   | CF13 |      1 |   7102 |   7102 |00:00:00.01 |      57 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."ORD_NO"="H"."ORD_NO")
   4 - filter(("H"."PTNR_NO"='P007' AND ("H"."ORD_PRGS_CD"='10' OR "H"."ORD_PRGS_CD"='20')))

[09:30 — P007 진행 중 발주상품 1,384행을 사본 CF13_BAK_0610 과 대조]
  ORD_QTY = 사본 × 144     500행
  ORD_QTY = 사본 × 1       884행
  ORD_QTY = 사본 × 12        0행
  ORD_UNIT_CD             1,384행 모두 'BOX'

문제

배치가 500행째에서 ORA-01002로 멈춘 이유를 커서와 커밋의 관계로 설명하시오.
다시 돌렸더니 500행이 144배가 된 이유를 설명하시오.
지금 상태를 바로잡는 SQL과, 앞으로 몇 번을 돌려도 안전한 환산 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 바로잡기 — 배치 직전 사본에서 원래 수량을 가져와 1,384 행을 한 문장으로 (단위 칸도 같이)
MERGE INTO CF13 d
USING (SELECT b.ORD_NO, b.ITM_NO, b.ORD_QTY
         FROM CF13_BAK_0610 b
        WHERE b.ORD_NO IN (SELECT h.ORD_NO FROM CF12 h
                            WHERE h.PTNR_NO = 'P007'
                              AND h.ORD_PRGS_CD IN ('10', '20'))) s
   ON (d.ORD_NO = s.ORD_NO AND d.ITM_NO = s.ITM_NO)
 WHEN MATCHED THEN UPDATE
      SET d.ORD_QTY = s.ORD_QTY * 12, d.ORD_UNIT_CD = 'EA'
    WHERE d.ORD_UNIT_CD = 'BOX';
-- SQL%ROWCOUNT = 1,384 인지 보고
COMMIT;

-- ② 앞으로의 환산 — 커서도 중간 커밋도 없이 한 문장. 단위 칸이 조건이자 표시라서 다시 돌리면 0 행
UPDATE CF13 d
   SET d.ORD_QTY = d.ORD_QTY * 12, d.ORD_UNIT_CD = 'EA'
 WHERE d.ORD_UNIT_CD = 'BOX'
   AND d.ORD_NO IN (SELECT h.ORD_NO FROM CF12 h
                     WHERE h.PTNR_NO = :ptnr
                       AND h.ORD_PRGS_CD IN ('10', '20'));
COMMIT;

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

xplan

[① 바로잡기 — DISPLAY_CURSOR (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT          |               |      1 |        |      0 |00:00:00.02 |    1537 |
|   1 |  MERGE                   | CF13          |      1 |        |      0 |00:00:00.02 |    1537 |
|   2 |   VIEW                   |               |      1 |        |   1384 |00:00:00.01 |     124 |
|*  3 |    HASH JOIN             |               |      1 |   1402 |   1384 |00:00:00.01 |     124 |
|*  4 |     HASH JOIN RIGHT SEMI |               |      1 |   1402 |   1384 |00:00:00.01 |      67 |
|*  5 |      TABLE ACCESS FULL   | CF12          |      1 |    141 |    139 |00:00:00.01 |      10 |
|   6 |      TABLE ACCESS FULL   | CF13_BAK_0610 |      1 |   7102 |   7102 |00:00:00.01 |      57 |
|   7 |     TABLE ACCESS FULL    | CF13          |      1 |   7102 |   7102 |00:00:00.01 |      57 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."ORD_NO"="B"."ORD_NO" AND "D"."ITM_NO"="B"."ITM_NO")
   4 - access("B"."ORD_NO"="H"."ORD_NO")
   5 - filter(("H"."PTNR_NO"='P007' AND ("H"."ORD_PRGS_CD"='10' OR "H"."ORD_PRGS_CD"='20')))

[결과]  갱신 1,384행 · 1,384행 모두 사본 × 12 · ORD_UNIT_CD 'EA' · undo 약 0.1 MB
[② 를 다시 돌리면]  0행 (ORD_UNIT_CD = 'BOX' 인 대상이 없다)

모범 답안 서술

ORA-01002: fetch out of sequence는 FOR UPDATE 커서를 연 채로 커밋한 뒤 다음 행을 가져오려 해서 났다. FOR UPDATE 커서는 커서를 여는 순간 대상 1,384행을 모두 잠그는데(실행계획 1번 줄), 커밋하면 그 트랜잭션의 잠금이 모두 풀리고 커서도 더는 쓸 수 없다. 그래서 500행째에서 커밋한 뒤 501번째 행을 가져오려는 순간 멈췄다. 커밋된 500행만 12배가 되고 나머지 884행은 그대로인 반쯤 반영 상태가 남았다. 다시 돌리자 커서는 같은 조건으로 1,384행을 다시 골랐고, 이미 12배가 된 앞의 500행을 또 12배로 만든 뒤 같은 자리에서 멈췄다. 그래서 500행은 144배가 됐다. 배치가 '이미 환산했다'를 어디에도 표시하지 않아서(ORD_UNIT_CD가 그대로 'BOX') 다시 돌리는 것이 안전하지 않았다. 바로잡기는 배치 직전 사본 CF13_BAK_0610에서 원래 수량을 가져와, 1,384행 모두를 사본의 12배로, 단위를 'EA'로 MERGE 한 문장에 고친다. 앞으로의 환산은 커서도 중간 커밋도 없는 UPDATE 한 문장으로 하고, ORD_UNIT_CD = 'BOX'를 조건으로 삼아 'EA'로 같이 바꾼다. 그러면 두 번째 실행부터는 0행이다. 1,384행의 undo는 0.1MB 남짓이라 중간 커밋이 필요 없고, 끝에 한 번 커밋하므로 실패하면 전부 되돌아간다.

해설

함정: "트랜잭션은 짧게"는 언두가 모자랄 만큼 큰 트랜잭션에 쓰는 처방이다. 1,384행에 중간 커밋을 넣으면 얻는 것은 없고, FOR UPDATE 커서와 만나면 에러가 되며, 그렇지 않아도 반쯤 반영과 이중 처리를 부른다(교재 13화 「커밋 주기 기준」).
사본이 없었다면 AS OF TIMESTAMP(플래시백 쿼리)로 07:09 시점 값을 읽는 방법이 있다. 언두가 그때까지 남아 있어야 하므로 시간이 지날수록 어렵다. 변경 전 사본을 뜨는 운영 표준이 이번에 값을 했다.
사본과 맞춰 고치기 전에 사본을 뜬 뒤 대상 행을 고친 다른 작업이 없는지 확인한다(이번에는 감사 로그). 다른 칸이 바뀌었다면 ORD_QTY만 사본에서 가져오는 지금 모양이 맞다.
행마다 바꿔야 할 값이 정말로 다르고 계산이 복잡해 한 문장이 어렵다면, 커서로 대상 키를 먼저 다 읽어 배열에 담고(BULK COLLECT) 커서를 닫은 뒤 FORALL로 고친다. 커밋이 커서를 깨뜨리지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황