PART-046

이관 리허설에서 월별 적재 작업 넷을 동시에 띄웠는데 한 번에 하나씩만 돈다

중급INSERT한빛유통421 / 700

상황

한빛유통은 주말 작업 창에 옛 서버의 T_ORD_BIG을 새 서버의 월별 파티션 T_ORD_BIG_P14로 옮긴다. 이관 스크립트는 36개월을 달마다 작업 하나로 나눠 스케줄러에 올리고, DB 링크로 받는 시간이 길어서 넷씩 동시에 돌게 했다. 작업마다 직접 경로로 넣는다. EE 시험 DB 리허설에서 작업 하나는 약 3초였는데, 36개를 다 넣는 데 1분 52초가 걸렸다. 넷씩 돌면 30초 안팎이어야 한다. 그 시간 대기 1위는 enq: TM - contention이었다.

테이블과 인덱스

T_ORD_BIG@OLD 원본(옛 서버, DB 링크): 1,000,000행 · 5,826블록 · 이관 중에는 아무도 고치지 않는다. 한 달은 약 27,800행이다(2024년 6월 27,390행).
T_ORD_BIG_P14 대상(새 서버, 교재 14화 설계): PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX. 빈 테이블로 먼저 만들었고 STORAGE (INITIAL 64K)를 주었다. 인덱스는 적재가 끝난 뒤 만든다. 이관 중에는 다른 세션이 이 테이블을 쓰지 않는다.
새 서버는 EE이고 CPU는 8개, 병렬 실행이 켜져 있다. 옛 서버와의 링크는 세션 하나에 초당 약 1만 행을 받는다.

원본 SQL

sql

-- 이관 작업 하나 (스케줄러가 :m 을 2022-01-01 ~ 2024-12-01 로 바꿔 36번 띄운다 · 동시에 4개)
INSERT /*+ APPEND */ INTO T_ORD_BIG_P14 (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@OLD
 WHERE ORD_DT >= :m
   AND ORD_DT <  ADD_MONTHS(:m, 1);
COMMIT;

실행계획 학습용 예시 · EE 시험 DB 기준

xplan

[작업 하나 · :m = 2024-06-01 (단독 실행)]
---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |               |      1 |        |      0 | 00:00:03.02 |     251 |
|   1 |  LOAD AS SELECT                  | T_ORD_BIG_P14 |      1 |        |      0 | 00:00:03.02 |     251 |
|   2 |   OPTIMIZER STATISTICS GATHERING |               |      1 |  27778 |  27390 | 00:00:02.95 |       0 |
|   3 |    REMOTE                        | T_ORD_BIG     |      1 |  27778 |  27390 | 00:00:02.91 |       0 |
---------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "ORD_SEQ","CUS_ID","ORD_DT","ORD_ST","ORD_AMT" FROM "T_ORD_BIG" "T_ORD_BIG"
       WHERE "ORD_DT">=:1 AND "ORD_DT"<ADD_MONTHS(:1,1) (accessing 'OLD')

[스케줄러 기록 · 리허설]
  슬롯 4 · 작업 36 · 전체 1분 52초 · 작업마다 실제로 넣는 시간 약 3초, 나머지는 대기

[V$SESSION · V$LOCK 표본 · 01:00:10]
  SID   EVENT                   OBJECT          LMODE  REQUEST  BLOCKING_SESSION
  141   (적재 중)                T_ORD_BIG_P14   6      0
  142   enq: TM - contention    T_ORD_BIG_P14   0      6        141
  143   enq: TM - contention    T_ORD_BIG_P14   0      6        141
  144   enq: TM - contention    T_ORD_BIG_P14   0      6        141

문제

작업 넷이 한 번에 하나씩만 돈 까닭을 실행계획과 잠금 표본으로 설명하시오.
작업 넷이 서로 기다리지 않도록 이관 작업의 SQL을 고치고, 고친 SQL이 지켜야 할 조건을 쓰시오.
36개 작업 대신 한 문장의 병렬 DML로 옮기는 안과 비교해, 이 이관에 어느 쪽을 권할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 이관 작업 하나 : 넣을 칸을 문장에 적는다 → 직접 경로 적재가 테이블 전체가 아니라 그 칸만 배타로 잠근다
DECLARE
  v_m  DATE         := :m;                                  -- 2022-01-01 ~ 2024-12-01
  v_p  VARCHAR2(30) := 'P' || TO_CHAR(:m, 'YYYYMM');         -- P202201 ~ P202412
BEGIN
  EXECUTE IMMEDIATE
       'INSERT /*+ APPEND */ INTO T_ORD_BIG_P14 PARTITION (' || v_p || ')'
    || ' (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@OLD'
    || '  WHERE ORD_DT >= :b1 AND ORD_DT < ADD_MONTHS(:b2, 1)'   -- 칸 범위와 꼭 같게 (밖이면 ORA-14401)
    USING v_m, v_m;
  COMMIT;                                                      -- 칸 잠금은 커밋할 때 풀린다
END;

-- 한 달을 손으로 넣을 때의 모양
-- INSERT /*+ APPEND */ INTO T_ORD_BIG_P14 PARTITION (P202406) (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@OLD
--  WHERE ORD_DT >= DATE '2024-06-01' AND ORD_DT < DATE '2024-07-01';

개선 후 실행계획 학습용 예시 · EE 시험 DB 기준

xplan

[작업 하나 · P202406 을 적은 판]
---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |               |      1 |        |      0 | 00:00:03.05 |     251 |
|   1 |  LOAD AS SELECT                  | T_ORD_BIG_P14 |      1 |        |      0 | 00:00:03.05 |     251 |
|   2 |   OPTIMIZER STATISTICS GATHERING |               |      1 |  27778 |  27390 | 00:00:02.97 |       0 |
|   3 |    REMOTE                        | T_ORD_BIG     |      1 |  27778 |  27390 | 00:00:02.93 |       0 |
---------------------------------------------------------------------------------------------------

[스케줄러 기록 · 다시 한 리허설]
  슬롯 4 · 작업 36 · 전체 29초 · enq: TM - contention 0

[V$LOCK 표본 · 01:00:10]   SID 141 · 142 · 143 · 144 가 각자 자기 칸(P202201 · P202202 · P202203 · P202204)에
                            배타 잠금을 잡고, 서로 기다리지 않는다

[비교 · 한 문장 병렬 DML (ENABLE PARALLEL DML · APPEND PARALLEL 4)]
  적재는 PX 서버 넷이 나눠 쓰지만 REMOTE 원본은 QC 한 세션이 링크 하나로 받는다(S->P) · 전체 1분 49초

모범 답안 서술

1번 줄 LOAD AS SELECT는 직접 경로 적재다. 직접 경로 적재는 고수위선 위에 블록을 통째로 쓰므로, 커밋할 때까지 대상 테이블 전체에 배타 잠금(TM 6)을 건다. 넣는 행이 한 달 치뿐이어도 문장에 칸을 적지 않았으므로 테이블 전체를 잠근다. 그래서 SID 141이 적재하는 동안 142 ~ 144는 같은 테이블의 TM 잠금을 6으로 요청한 채(REQUEST 6, BLOCKING_SESSION 141) enq: TM - contention으로 기다렸다. 슬롯 넷이 있어도 실제로는 한 번에 하나씩 돌아 36 × 약 3초 = 1분 52초가 걸렸다. 처방은 넣을 칸을 문장에 적는 것이다. INSERT /*+ APPEND */ INTO T_ORD_BIG_P14 PARTITION (P202406) …처럼 칸을 적으면 직접 경로 적재가 그 칸에만 배타 잠금을 걸고, 다른 칸에 넣는 세션은 기다리지 않는다. 작업마다 :m으로 칸 이름('P' || TO_CHAR(:m, 'YYYYMM'))을 만들어 동적 SQL로 실행한다. 다시 한 리허설은 TM 대기 없이 29초에 끝났다. 고친 SQL이 지켜야 할 조건이 있다. 첫째, WHERE의 범위가 적은 칸의 범위와 같아야 한다. 칸을 적은 INSERT에 범위 밖 행이 하나라도 섞이면 ORA-14401로 문장 전체가 실패한다. 이것은 잘못 나눈 작업을 바로 드러내는 안전장치이기도 하다. 둘째, 칸 잠금은 커밋할 때 풀린다. 작업 하나는 칸 하나만 넣고 바로 커밋하며, 한 트랜잭션에 여러 칸을 묶지 않는다. 셋째, 비운 칸에 직접 경로로 넣으므로 STORAGE (INITIAL 64K)를 준 지금 설계를 유지한다. 그러지 않으면 칸마다 빈 블록 약 1,006개가 남는다(교재 14화 §8.5). 한 문장의 병렬 DML(ENABLE PARALLEL DML + APPEND PARALLEL)로 옮기는 안은 문장 하나로 끝나고 칸마다 이름을 만들 필요가 없다. 하지만 원본이 DB 링크 너머에 있어서, 원격 테이블은 QC 한 세션이 링크 하나로 받아 PX 서버에 나눠 준다(S->P). 이관 시간의 대부분이 링크로 받는 시간이므로 병렬 DML로는 1분 49초로 거의 줄지 않는다. 칸을 적은 작업 넷은 링크 세션 넷으로 동시에 받으므로 29초다. 실패한 달만 다시 돌리기도 쉽다. 그래서 이 이관에는 칸을 적은 작업을 권한다. 원본이 같은 DB에 있었다면 원본을 한 번만 읽는 병렬 DML 한 문장이 더 단순한 답이다.

해설

병렬 DML의 켜는 법과 부작용(ORA-12838, 잠금, 다운그레이드)은 DML-043에서 다룬다. 이 문제의 요점은 '같은 테이블에 여러 세션이 직접 경로로 넣으려면 칸을 나눠 적는다'는 것이다.
칸을 적는 방법은 칸 이름(PARTITION (P202406)) 말고 값(PARTITION FOR (DATE '2024-06-01'))으로도 쓸 수 있다. INTERVAL 칸처럼 이름이 SYS_P…로 붙는 테이블에서는 값으로 적는다.
인덱스는 적재 뒤에 만든다. LOCAL 인덱스를 먼저 UNUSABLE로 만들어 두고 칸마다 REBUILD PARTITION을 작업으로 나누면 인덱스 짓기도 넷씩 돌릴 수 있다.
교재 14화처럼 Free에서는 병렬이 돌지 않으므로, 병렬 DML 안의 시간은 EE 시험 DB에서 잰 값으로 판단한다(교재 14화 §9.2).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · EE 시험 DB 기준 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · EE 시험 DB 기준 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황