POS 업체가 12월분을 통째로 다시 보내자 적재에 7초가 걸리고 redo가 91MB 쌓였다

실전PLSQL버거672 / 700

상황

2013년 12월 26일부터 사흘 동안 POS 중계 서버 장애로 일부 매장의 주문상품이 본사에 들어오지 않았다. POS 업체는 어느 건이 빠졌는지 몰라 12월분 전체(103,046행)를 다시 보냈다. 본사 적재 프로그램은 한 행씩 넣고, 이미 있는 행은 예외로 잡아 넘긴다. 돌려 보니 7.2초가 걸렸고 redo가 91MB 쌓였는데, 새로 들어간 행은 4,312행뿐이었다. 오과장이 시험 DB에서 세 가지 안을 재 왔다. 장애가 나면 같은 재전송이 또 온다. 적재 규칙은 2019년부터 그대로다. "한 행이 실패해도 나머지는 넣는다. 실패한 행은 사유와 함께 남긴다. 이미 있는 행은 실패가 아니다."

테이블과 인덱스

ORD_ITEM_RCV(POS 수신 테이블, 이 문제용): ORD_ITEM과 같은 컬럼 · 12월 재전송분 103,046행 · 2,301블록 · 인덱스 없음
ORD_ITEM 주문상품: 적재 전 2,468,405행(장애로 4,312행이 빠진 상태) · 적재 뒤 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) 리프 6,870블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
재전송분 103,046행 가운데 98,734행은 이미 ORD_ITEM에 있고, 4,312행이 빠져 있었다.
ORD_ITEM_REJ(반송함): ORD_NO · ITEM_ID · ERR_CD · ERR_MSG · 이번에는 0행
재전송은 야간에 한 세션만 적재한다. 낮에는 POS가 같은 테이블에 실시간으로 넣는다.

원본 SQL

sql

PROCEDURE LOAD_POS_RESEND IS
  v_ok   PLS_INTEGER := 0;
  v_dup  PLS_INTEGER := 0;
  v_cd   PLS_INTEGER;
  v_msg  VARCHAR2(200);
BEGIN
  FOR r IN (SELECT * FROM ORD_ITEM_RCV) LOOP
    BEGIN
      INSERT INTO ORD_ITEM VALUES r;
      v_ok := v_ok + 1;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        v_dup := v_dup + 1;                                -- 이미 있음
      WHEN OTHERS THEN
        v_cd  := SQLCODE;
        v_msg := SUBSTR(SQLERRM, 1, 200);
        INSERT INTO ORD_ITEM_REJ (ORD_NO, ITEM_ID, ERR_CD, ERR_MSG)
        VALUES (r.ORD_NO, r.ITEM_ID, v_cd, v_msg);
    END;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[지금 판 — 12월 재전송분 103,046행 (V$MYSTAT 증가분)]
  경과 시간                 7.2 초
  recursive calls           104,129
  execute count             103,072
  session logical reads     792,416
  redo size                 91.3 MB
  undo change vector size   27.6 MB
  결과                      신규 4,312 · 이미 있음 98,734 · 반송 0

[TKPROF — 루프 안의 INSERT]
INSERT INTO ORD_ITEM VALUES (:B1 , :B2 , :B3 , …)

call     count       cpu    elapsed       disk      query    current        rows
------- -------  -------- ---------- ---------- ---------- ----------  ----------
Execute  103046      4.61       4.83          0       3118     781944        4312
(103,046번 가운데 98,734번은 ORA-00001로 실패했고, 실패할 때마다 넣던 행을 되돌렸다)

[오과장의 세 안 — 같은 재전송분 · 시험 DB]
  안                                                                  경과    recursive calls   논리 읽기    redo       결과
  ① BULK 1000 + FORALL … SAVE EXCEPTIONS                              5.6 초          251       786,902    90.8 MB   같음 · 예외 98,734건을 BULK_EXCEPTIONS로 받음
  ② INSERT … SELECT … LOG ERRORS REJECT LIMIT UNLIMITED               6.8 초           38     1,108,551   128.4 MB   같음 · 오류 기록표에 98,734행
  ③ INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(ORD_ITEM, ORD_ITEM_PK) */
       … SELECT                                                       5.9 초           31       790,377    90.1 MB   같음 · 이미 있음 건수는 따로 세야 함

문제

지금 판의 일량이 어디서 나오는지 TKPROF와 세션 통계로 설명하시오. ①로 바꾸면 recursive calls는 크게 주는데 시간과 redo는 왜 거의 그대로인지도 쓰시오.
세 안을 비교하고, 적재 규칙을 지키면서 일량을 줄이는 적재 프로그램을 작성하시오.
고친 적재의 전제 조건과 부작용을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE LOAD_POS_RESEND IS
  CURSOR c IS                                   -- 이미 있는 행은 넣어 보기 전에 거른다 (해시 안티 조인 한 번)
    SELECT r.*
      FROM ORD_ITEM_RCV r
     WHERE NOT EXISTS (SELECT 1
                         FROM ORD_ITEM i
                        WHERE i.ORD_NO  = r.ORD_NO
                          AND i.ITEM_ID = r.ITEM_ID);
  TYPE t_rows IS TABLE OF ORD_ITEM%ROWTYPE;
  v        t_rows;
  v_tot    PLS_INTEGER;
  v_new    PLS_INTEGER := 0;
  v_rej    PLS_INTEGER := 0;
  v_ix     PLS_INTEGER;
  v_cd     PLS_INTEGER;
  v_msg    VARCHAR2(200);
  bulk_err EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_err, -24381);
BEGIN
  SELECT COUNT(*) INTO v_tot FROM ORD_ITEM_RCV;
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 1000;
    EXIT WHEN v.COUNT = 0;
    BEGIN
      FORALL i IN 1 .. v.COUNT SAVE EXCEPTIONS    -- 모르는 실패(동시 적재의 중복 · 제약 위반)를 받는 그물
        INSERT INTO ORD_ITEM VALUES v(i);
      v_new := v_new + v.COUNT;
    EXCEPTION
      WHEN bulk_err THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
          v_ix  := SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;   -- 배열에 구멍이 없으므로 반복 순번 = 첨자
          v_cd  := SQL%BULK_EXCEPTIONS(j).ERROR_CODE;
          v_msg := SUBSTR(SQLERRM(-v_cd), 1, 200);
          INSERT INTO ORD_ITEM_REJ (ORD_NO, ITEM_ID, ERR_CD, ERR_MSG)
          VALUES (v(v_ix).ORD_NO, v(v_ix).ITEM_ID, v_cd, v_msg);
        END LOOP;
        v_new := v_new + v.COUNT - SQL%BULK_EXCEPTIONS.COUNT;
        v_rej := v_rej + SQL%BULK_EXCEPTIONS.COUNT;
    END;
  END LOOP;
  CLOSE c;
  -- 재전송 = 신규 + 이미 있음 + 반송 (103,046 = 4,312 + 98,734 + 0)
  DBMS_OUTPUT.PUT_LINE('total ' || v_tot || ' / new ' || v_new
                       || ' / exists ' || (v_tot - v_new - v_rej) || ' / rej ' || v_rej);
  COMMIT;
END;

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

xplan

[구동 커서 — DISPLAY_CURSOR (ALLSTATS LAST)]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |   4312 |00:00:00.24 |    9171 |          |
|*  1 |  HASH JOIN ANTI       |              |      1 |   5152 |   4312 |00:00:00.24 |    9171 |   21M (0)|
|   2 |   TABLE ACCESS FULL   | ORD_ITEM_RCV |      1 |    103K|    103K|00:00:00.02 |    2301 |          |
|   3 |   INDEX FAST FULL SCAN| ORD_ITEM_PK  |      1 |   2468K|   2468K|00:00:00.11 |    6870 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("I"."ORD_NO"="R"."ORD_NO" AND "I"."ITEM_ID"="R"."ITEM_ID")

[세션 통계 — 고친 판 한 번]
  경과 0.38 초 · recursive calls 43 · session logical reads 26,514 · redo size 2.4 MB · undo 0.6 MB
  결과: 신규 4,312 · 이미 있음 98,734 · 반송 0 (지금 판과 ORD_ITEM MINUS 0 / 0)

모범 답안 서술

TKPROF의 INSERT는 Execute 103,046인데 rows는 4,312다. 98,734번은 ORA-00001로 실패했다. 실패한 INSERT도 공짜가 아니다. 테이블 블록에 행을 넣고 인덱스에 키를 넣다가 ORD_ITEM_PK에서 중복을 만나면, 그 문장이 한 일을 되돌린다. 넣을 때 redo와 undo를 만들고, 되돌릴 때 또 만든다. current 781,944와 redo 91.3MB, undo 27.6MB의 거의 전부가 실패한 98,734번에서 나왔다. 신규 4,312행을 넣는 일은 그 가운데 몇 %에 불과하다. ①은 원소 천 개를 한 번에 넘겨 recursive calls를 104,129 → 251로 줄인다. 그러나 SQL 엔진은 원소마다 넣어 보고, 실패하면 되돌린다. 호출만 묶였을 뿐 실패의 일은 그대로라서 5.6초, redo 90.8MB가 남는다. ②는 LOG ERRORS 때문에 집합 처리가 행 단위가 되고, 실패한 98,734행을 오류 기록표에 따로 써서 오히려 더 무겁다(redo 128.4MB). ③은 중복을 만나면 그 행만 되돌리고 넘어가는 행 단위 처리라 일량이 ①과 같고, 중복을 조용히 버리므로 '이미 있음' 건수를 따로 세야 한다. 세 안은 모두 '넣어 보고 실패를 처리하는' 방법이다. 고친 판은 넣어 보기 전에 거른다. 구동 커서에서 NOT EXISTS로 재전송분과 ORD_ITEM_PK를 대어 보면 해시 안티 조인 한 번(Buffers 9,171)으로 빠진 4,312행만 남는다. 그 행만 BULK COLLECT … LIMIT 1000과 FORALL … SAVE EXCEPTIONS로 넣고, 그물에 걸린 모르는 실패는 사유와 함께 반송함에 남긴다. 배열에 구멍이 없으므로 ERROR_INDEX를 그대로 첨자로 써도 된다. 이미 있는 행은 '재전송 − 신규 − 반송'으로 센다. 경과는 7.2초 → 0.38초, 논리 읽기는 792,416 → 26,514, redo는 91.3MB → 2.4MB로 준다. 전제와 부작용은 셋이다. 첫째, NOT EXISTS로 확인한 순간과 INSERT 사이에 다른 세션이 같은 행을 넣으면 중복이 난다. 재전송은 야간에 한 세션만 돌지만, 낮에 돌리면 POS 실시간 적재와 부딪힐 수 있다. 그때는 SAVE EXCEPTIONS 그물이 ORA-00001로 받으므로 반송함에서 '이미 있음'으로 다시 분류한다. 둘째, 해시 안티 조인은 재전송분을 메모리(PGA 21MB)에 올리고 PK 전체(6,870블록)를 매번 읽는다. 재전송이 몇백 행이면 고정 비용이 커지므로 PK로 한 행씩 찾는 NL 안티 조인이 낫다. 셋째, 반송함과 건수 대조를 남겨야 '한 행이 실패해도 나머지는 넣는다'는 규칙이 빠진 행을 숨기지 않는다.

해설

다른 정답: 한 문장 INSERT INTO ORD_ITEM SELECT … FROM ORD_ITEM_RCV r WHERE NOT EXISTS (…) LOG ERRORS INTO ORD_ITEM_ERR REJECT LIMIT UNLIMITED도 된다. 거른 뒤라 실패가 거의 없으므로 LOG ERRORS의 행 단위 비용도 4,312행분뿐이다(약 0.3초). 오류 기록표가 반송함 역할을 한다.
함정: IGNORE_ROW_ON_DUPKEY_INDEX는 편해 보이지만 중복을 만날 때마다 행 단위로 되돌리고, APPEND와 함께 쓸 수 없으며, 중복이 몇 건인지 알려 주지 않는다. '이미 있음'을 세야 하는 재전송에는 맞지 않는다.
LOG ERRORS가 집합 처리를 행 단위로 바꾸는 비용은 교재 15화 §8.4에서 쟀다. 오류가 한 건도 없는 같은 MERGE에 붙이기만 해도 redo가 11.15MB → 55.84MB가 됐다.
장애 구간(12월 26 ~ 28일)만 다시 받을 수 있었다면 재전송 자체가 1만 행 남짓으로 줄었다. POS 업체와 '빠진 구간만 다시 보내기' 절차를 맞추는 것이 가장 큰 개선이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황