직접 경로로 바꿨다는 컨설팅 사본 적재가 5분을 넘기고, 사본은 10만 블록으로 불었다

실전PLSQL버거439 / 700

상황

버거원 본사는 매달 1일 03:00에 지난달 주문상품을 가맹 컨설팅팀용 사본 CNS_ORD_ITEM에 다시 채운다(매장, 날짜, 시간대, 메뉴, 수량). 몇 년 전에 만든 프로시저는 루프 안의 INSERT에 /*+ APPEND */를 붙여 두었고, 4초 남짓에 끝났다. 지난주 조민우가 실행계획을 보다가 그 INSERT가 직접 경로로 돌지 않는다는 것을 알고 APPEND_VALUES로 바꿨다. 리허설에서 둘째 행에 ORA-12838이 나자 INSERT마다 커밋을 넣었다. 이번에는 끝까지 돌았지만 5분 7초가 걸렸고, 컨설팅 리포트(사본 전체 스캔)가 약 420블록에서 104,780블록을 읽게 됐다. 조민우: "직접 경로가 더 빠르다고 해서 바꾼 건데요."

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · 2013년 12월 주문 42,408건(리프 약 140블록, 테이블 951블록)
ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 2013년 12월 주문상품 104,750행(리프 약 390블록, 테이블 약 2,340블록)
CNS_ORD_ITEM(이 문제용): SHOP_NO · ORD_DT · HR_BAND('M' 오전 · 'L' 점심 · 'A' 오후 · 'D' 저녁) · ITEM_ID · QTY · 인덱스, 트리거, FK 없음 · 한 달 치를 일반 INSERT로 넣으면 약 420블록(블록당 약 250행)이다. 이 배치만 쓰고, 낮에는 컨설팅 리포트가 읽기만 한다.
본사 분석 DB(Oracle 19c, NOARCHIVELOG)다. 배치가 도는 03:00에는 다른 세션이 이 사본을 쓰지 않는다.

원본 SQL

sql

-- 처음 판 (몇 년 전) · 4초 남짓
PROCEDURE P_CNS_LOAD(p_ym VARCHAR2) IS                       -- p_ym = '201312'
  v_band VARCHAR2(1);
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE CNS_ORD_ITEM';
  FOR r IN (SELECT o.SHOP_NO, i.ORD_DT, i.ORD_HMS, i.ITEM_ID, i.ORD_ITEM_QTY
              FROM ORD o, ORD_ITEM i
             WHERE o.ORD_DT BETWEEN p_ym || '01' AND p_ym || '31'
               AND i.ORD_DT BETWEEN p_ym || '01' AND p_ym || '31'
               AND i.ORD_NO = o.ORD_NO) LOOP
    v_band := CASE WHEN r.ORD_HMS < '110000' THEN 'M'
                   WHEN r.ORD_HMS < '140000' THEN 'L'
                   WHEN r.ORD_HMS < '170000' THEN 'A'
                   ELSE 'D' END;
    INSERT /*+ APPEND */ INTO CNS_ORD_ITEM (SHOP_NO, ORD_DT, HR_BAND, ITEM_ID, QTY)
    VALUES (r.SHOP_NO, r.ORD_DT, v_band, r.ITEM_ID, r.ORD_ITEM_QTY);
  END LOOP;
  COMMIT;
END;

-- 지난주 바꾼 판 (조민우) · 루프 안의 INSERT 만 다르다
    INSERT /*+ APPEND_VALUES */ INTO CNS_ORD_ITEM (SHOP_NO, ORD_DT, HR_BAND, ITEM_ID, QTY)
    VALUES (r.SHOP_NO, r.ORD_DT, v_band, r.ITEM_ID, r.ORD_ITEM_QTY);
    COMMIT;                                                  -- 둘째 행의 ORA-12838 을 피하려고 넣었다

실행계획 학습용 예시

xplan

[처음 판 — 12월분 104,750행 · 세션 통계]
  경과 시간                    3.9 초
  INSERT 실행 횟수             104,750
  user commits                 2   (TRUNCATE 의 암시적 커밋 + 마지막 COMMIT)
  redo size                    약 27.4 MB
  undo change vector size      약 6.9 MB
  루프 안 INSERT 의 실행계획    INSERT STATEMENT → LOAD TABLE CONVENTIONAL  CNS_ORD_ITEM
  적재 뒤 CNS_ORD_ITEM          약 420블록

[바꾼 판 ① — APPEND_VALUES · 커밋 없이 (리허설)]
  첫 행   1 row created   (실행계획 LOAD AS SELECT  CNS_ORD_ITEM)
  둘째 행 ORA-12838: cannot read/modify an object after modifying it in parallel
  → 롤백 · 0행

[바꾼 판 ② — APPEND_VALUES + INSERT 마다 COMMIT (리허설) · 세션 통계]
  경과 시간                    5분 7초
  INSERT 실행 횟수             104,750
  user commits                 104,752   (행마다 104,750 + TRUNCATE + 마지막 COMMIT)
  log file sync 대기           약 2분 41초
  physical writes direct       104,750
  redo size                    약 112 MB   (적재마다 고수위선과 공간 비트맵을 고친 기록 · 커밋 기록)
  CNS_ORD_ITEM (USER_SEGMENTS) 104,780블록 · 행 104,750
  컨설팅 리포트 (CNS_ORD_ITEM 전체 스캔)  Buffers 약 420 → 104,780

문제

처음 판의 /*+ APPEND */가 직접 경로로 돌지 않은 이유와, 바꾼 판 ①이 둘째 행에서 ORA-12838로 멈춘 이유를 설명하시오.
바꾼 판 ②가 5분 넘게 걸리고 사본이 104,780블록이 된 이유를 직접 경로가 블록을 쓰는 방식으로 설명하시오.
(가) INSERT /*+ APPEND */ … SELECT 한 문장, (나) BULK COLLECT와 FORALL … APPEND_VALUES, (다) FORALL 일반 INSERT를 실행 횟수, 커밋, 블록 수, redo, 잠금으로 비교하고 고른 방법으로 다시 작성하시오. 이미 커진 사본을 되돌리는 방법도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_CNS_LOAD(p_ym VARCHAR2) IS                     -- p_ym = '201312'
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE CNS_ORD_ITEM';         -- 고수위선도 처음으로 내려간다 (104,780블록 → 0)

  -- (가) 루프 · 행마다 INSERT · 행마다 커밋 → 한 문장 · 직접 경로 · 커밋 한 번
  INSERT /*+ APPEND */ INTO CNS_ORD_ITEM (SHOP_NO, ORD_DT, HR_BAND, ITEM_ID, QTY)
  SELECT o.SHOP_NO, i.ORD_DT,
         CASE WHEN i.ORD_HMS < '110000' THEN 'M'
              WHEN i.ORD_HMS < '140000' THEN 'L'
              WHEN i.ORD_HMS < '170000' THEN 'A'
              ELSE 'D' END,
         i.ITEM_ID, i.ORD_ITEM_QTY
    FROM ORD o, ORD_ITEM i
   WHERE o.ORD_DT BETWEEN p_ym || '01' AND p_ym || '31'
     AND i.ORD_DT BETWEEN p_ym || '01' AND p_ym || '31'
     AND i.ORD_NO = o.ORD_NO;
  COMMIT;
END;

-- (나) 규칙이 PL/SQL 에만 있어 한 문장으로 못 쓸 때: 묶음 하나가 직접 경로 적재 한 번
--   LOOP
--     FETCH c BULK COLLECT INTO v_shop, v_dt, v_band, v_item, v_qty LIMIT 5000;
--     EXIT WHEN v_shop.COUNT = 0;
--     FORALL k IN 1 .. v_shop.COUNT
--       INSERT /*+ APPEND_VALUES */ INTO CNS_ORD_ITEM (SHOP_NO, ORD_DT, HR_BAND, ITEM_ID, QTY)
--       VALUES (v_shop(k), v_dt(k), v_band(k), v_item(k), v_qty(k));
--     COMMIT;                  -- 다음 묶음을 직접 경로로 넣기 전에 커밋해야 한다 (19c ORA-12838)
--   END LOOP;

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

xplan

[(가) — DISPLAY_CURSOR (ALLSTATS LAST)]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                        |              |      1 |        |      0 |00:00:00.58 |    3912 |          |
|   1 |  LOAD AS SELECT                         | CNS_ORD_ITEM |      1 |        |      0 |00:00:00.58 |    3912 |          |
|   2 |   OPTIMIZER STATISTICS GATHERING        |              |      1 |    104K|    104K|00:00:00.31 |    3821 |          |
|*  3 |    HASH JOIN                            |              |      1 |    104K|    104K|00:00:00.27 |    3821 | 3170K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD          |      1 |  42419 |  42408 |00:00:00.03 |    1091 |          |
|*  5 |      INDEX RANGE SCAN                   | ORD_X01      |      1 |  42419 |  42408 |00:00:00.01 |     140 |          |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |    104K|    104K|00:00:00.09 |    2730 |          |
|*  7 |      INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |    104K|    104K|00:00:00.02 |     390 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ORD_NO"="O"."ORD_NO")
   5 - access("O"."ORD_DT">=:B1||'01' AND "O"."ORD_DT"<=:B2||'31')
   7 - access("I"."ORD_DT">=:B1||'01' AND "I"."ORD_DT"<=:B2||'31')

[같은 12월분 104,750행 — 넣는 법만 다르다]
  넣는 법                         INSERT 실행   커밋      경과       redo       CNS_ORD_ITEM   TM 락
  처음 판 (APPEND 무시)            104,750      1         3.9초      27.4 MB    약 420블록      3
  바꾼 판 ② (행마다 직접 경로)      104,750      104,750   5분 7초    112 MB     104,780블록     6 (행마다)
  (가) INSERT … SELECT             1            1         0.58초     0.1 MB     409블록         6 (0.6초)
  (나) FORALL + APPEND_VALUES      21           21        0.9초      0.6 MB     약 430블록      6 (묶음마다)
  (다) FORALL 일반 INSERT          21           1         1.1초      약 6 MB    약 420블록      3

모범 답안 서술

처음 판의 APPEND 힌트는 처음부터 듣지 않았다. APPEND 힌트는 INSERT … SELECT에만 듣고, VALUES로 한 행을 넣는 INSERT에서는 에러 없이 무시된다. 실행계획 첫 줄이 LOAD TABLE CONVENTIONAL인 것이 그 증거다. 몇 년 동안 일반 경로로 104,750번 INSERT하며 redo 27.4MB를 남겼다. APPEND_VALUES는 VALUES INSERT도 직접 경로로 돌린다. 그런데 직접 경로로 넣은 테이블은 커밋하기 전에는 같은 트랜잭션에서 다시 읽거나 넣을 수 없어(19c) 둘째 행의 INSERT가 ORA-12838로 멈췄다(바꾼 판 ①). 바꾼 판 ②는 행마다 커밋해서 그 에러를 피했지만 직접 경로의 고정 비용을 행마다 냈다. 직접 경로 적재는 버퍼 캐시의 빈자리를 찾지 않고 고수위선 위의 새 블록부터 블록째 쓰고, 커밋하면 고수위선을 그 블록 뒤로 올린다. 한 번에 한 행만 넣고 커밋하니 적재마다 고수위선 위에 새 블록을 하나씩 써서 블록 하나에 한 행이 들어갔다. 그래서 104,750행이 104,780블록(physical writes direct 104,750)이 됐고, 행마다 한 커밋 104,750번의 log file sync가 2분 41초를 더해 5분 7초가 걸렸다. 사본을 통째로 읽는 컨설팅 리포트도 약 420 → 104,780블록을 읽는다. 세 방법을 비교하면 이렇다. (가) INSERT /*+ APPEND */ … SELECT 한 문장은 실행 1번, 커밋 1번이고 409블록에 꽉 채워 쓴다. 경과 0.58초, redo 0.1MB다. 테이블에 배타 잠금(TM 6)이 걸리지만 0.6초이고 03:00에는 쓰는 세션이 없다. (나) FORALL과 APPEND_VALUES는 묶음 하나가 직접 경로 적재 한 번이라 다음 묶음을 넣기 전에 커밋해야 한다(5,000행씩 21번). 묶음마다 마지막 블록이 덜 찬 채 남아 약 430블록이 되고, 묶음마다 배타 잠금을 잡았다 놓는다. 규칙이 PL/SQL에만 있을 때 쓰는 차선이다. (다) FORALL 일반 INSERT는 실행 21번, 커밋 1번이고 잠금은 TM 3이라 낮에 여러 세션이 쓰는 테이블에도 쓸 수 있지만 redo가 약 6MB 남는다. 시간대 규칙은 CASE로 SQL에 쓸 수 있으므로 (가)를 고른다. 이미 커진 사본은 고친 프로시저의 첫 줄 TRUNCATE가 고수위선을 처음으로 내리고 익스텐트도 돌려주므로 한 번 돌리면 되돌아간다. 다시 적재한 뒤 USER_SEGMENTS.BLOCKS가 약 420인지 확인한다.

해설

함정: "직접 경로가 빠르다"는 한 번에 많은 행을 넣을 때의 이야기다. 직접 경로는 버퍼 캐시를 거치지 않고 블록을 통째로 쓰는 대신, 적재마다 고수위선 위의 새 블록에서 시작하고 테이블에 배타 잠금을 건다. 한 행씩 쓰면 이 고정 비용을 행마다 낸다(교재 14화 §4).
힌트를 붙였으면 실행계획 첫 줄이 LOAD AS SELECT인지 눈으로 확인한다. 처음 판은 몇 년 동안 LOAD TABLE CONVENTIONAL로 돌았는데 아무도 몰랐다. 힌트가 듣지 않아도 에러는 나지 않는다(교재 14화 §4.3).
ORA-12838은 버전에 따라 다르다. 교재 14화 §4.4처럼 연습 DB(26ai Free)에서는 커밋 전 읽기가 막히지 않았으므로 운영 버전에서 재 보고 쓴다. 어느 버전이든 행마다 커밋하는 직접 경로 적재는 행마다 블록을 하나씩 쓴다.
(다)의 배열 INSERT는 한 행씩 넣던 처음 판보다 redo가 4분의 1쯤으로 준다. 블록 단위로 기록하기 때문이다(교재 15화 §5). 직접 경로의 잠금을 피해야 하는 공유 테이블이라면 (다)가 맞는 답이다.
ALTER TABLE … MOVE로도 블록을 되돌릴 수 있다. 하지만 이 사본은 매달 비우고 다시 채우므로 TRUNCATE와 고친 적재 한 번이 가장 싸다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황