이번 달 주문상품 칸을 밤마다 지우고 다시 넣는 데 2분이 넘고 redo가 110MB씩 나온다

실전DDL버거467 / 700

상황

버거원 본사 분석 DB에는 주문상품을 월별로 나눈 ORD_ITEM_RANGE가 있다. 매장 정정(취소, 수량 수정)이 그달 안에서 계속 들어오므로, 매일 03:00에 이번 달 칸을 지우고 운영 ORD_ITEM에서 다시 넣는다. 12월 말로 가면서 이 단계가 2분 10초까지 늘었고, 아카이브 로그는 이 단계에서만 하루 110MB가 나온다. 오과장이 두 가지 안을 들고 왔다. ⓐ 칸을 TRUNCATE하고 /*+ APPEND */로 넣는다. ⓑ 칸을 TRUNCATE하고 그 칸의 로컬 인덱스만 끈 채 일반 INSERT로 넣은 뒤 다시 짓는다. 06:00에는 매장 집계 리포트가 이번 달 칸을 통째로 읽는다.

테이블과 인덱스

ORD_ITEM_RANGE(문제 설정, ORD_ITEM의 월별 파티션 복사본): PARTITION BY RANGE (ORD_YM) 2012년 1월 ~ 2013년 12월 24칸과 빈 P_MAX. 칸 하나에 약 103,000행 · 데이터 약 2,300블록이다. 오늘(12월 30일) 밤 다시 넣을 P201312은 99,720행이다.
LOCAL 인덱스 ORD_ITEM_RANGE_X01 (ORD_DT, ORD_HMS) · ORD_ITEM_RANGE_X02 (ITEM_ID, ORD_DT). 둘 다 유니크가 아니다. GLOBAL 인덱스, FK, 트리거는 없다.
이 버전은 파티션의 첫 익스텐트를 8MB(1,024블록)로 잡는다(교재 14화 §8.5). 테이블에 STORAGE 절은 없다.
원천 ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
분석 DB는 ARCHIVELOG이고 테이블과 인덱스는 LOGGING이다. 03:00 ~ 06:00에는 이 테이블에 다른 작업이 없다.

원본 SQL

sql

-- 매일 03:00 · 이번 달 칸 다시 채우기 (:v_ym = '201312')
DELETE FROM ORD_ITEM_RANGE
 WHERE ORD_YM = :v_ym;

INSERT INTO ORD_ITEM_RANGE (ORD_YM, ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT SUBSTR(ORD_DT, 1, 6), ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS
  FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :v_ym || '01' AND :v_ym || '31';
COMMIT;

실행계획 학습용 예시 · 빈 블록 수는 교재 14화 §8.5 실측

xplan

[① DELETE]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT        |                |      1 |        |      0 |00:00:47.21 |    675K |       |       |
|   1 |  DELETE                 | ORD_ITEM_RANGE |      1 |        |      0 |00:00:47.21 |    675K |       |       |
|   2 |   PARTITION RANGE SINGLE|                |      1 |  96412 |  96412 |00:00:00.03 |    2244 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL    | ORD_ITEM_RANGE |      1 |  96412 |  96412 |00:00:00.02 |    2244 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------
   3 - filter("ORD_YM"=:V_YM)

[② INSERT]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |                |      1 |        |      0 |00:01:23.10 |    627K |
|   1 |  LOAD TABLE CONVENTIONAL            | ORD_ITEM_RANGE |      1 |        |      0 |00:01:23.10 |    627K |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM      |      1 |    102K|  99720 |00:00:00.09 |    2643 |
|*  3 |    INDEX RANGE SCAN                 | ORD_ITEM_X01   |      1 |    102K|  99720 |00:00:00.02 |     398 |
-------------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT">=:V_YM||'01' AND "ORD_DT"<=:V_YM||'31')

[세션 통계 — ① + ② + COMMIT (분석 DB · ARCHIVELOG)]
  redo size                  약 110 MB (DELETE 약 44 · INSERT 약 66)
  undo change vector size    약 39 MB
  db block changes           약 1,130,000
  경과                       2분 10초

[P201312 칸 — USER_SEGMENTS · DBMS_SPACE (작업 뒤)]
  테이블 칸 24 MB 할당 · 고수위선 아래 2,246블록
  X01 칸 리프 약 410블록 · X02 칸 리프 약 330블록

문제

지금 방식의 비용(redo 110MB, 2분 10초)이 어디서 나오는지 행마다 하는 일로 설명하시오.
칸 단위 DDL을 써서 이 단계를 다시 작성하시오. 로컬 인덱스를 어떻게 다룰지도 SQL로 쓰시오.
오과장의 ⓐ와 ⓑ를 redo, 06:00 리포트가 읽는 블록, 잠금, 실패했을 때의 상태로 비교하고 하나를 고르시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매일 03:00 · 이번 달 칸 다시 채우기 — ⓑ (배치에서는 칸 이름 P || :v_ym 을 EXECUTE IMMEDIATE 로 만든다)
-- ① 칸을 통째로 비운다 — 행을 하나씩 지우지 않는다 (DDL · 암시적 커밋 · 되돌릴 수 없다)
ALTER TABLE ORD_ITEM_RANGE TRUNCATE PARTITION P201312;

-- ② 그 칸의 로컬 인덱스만 끈다 — 다른 23칸의 인덱스는 그대로 쓴다
ALTER TABLE ORD_ITEM_RANGE MODIFY PARTITION P201312 UNUSABLE LOCAL INDEXES;

-- ③ 일반 INSERT — 비운 칸의 첫 블록부터 채운다 (ARCHIVELOG · LOGGING 이라 APPEND 로도 redo 는 거의 같다)
INSERT INTO ORD_ITEM_RANGE PARTITION (P201312)
       (ORD_YM, ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT SUBSTR(ORD_DT, 1, 6), ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS
  FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :v_ym || '01' AND :v_ym || '31';
COMMIT;

-- ④ 꺼 둔 칸의 인덱스만 다시 짓는다 (칸 크기만큼만 정렬한다)
ALTER TABLE ORD_ITEM_RANGE MODIFY PARTITION P201312 REBUILD UNUSABLE LOCAL INDEXES;

-- ⑤ 확인 — 칸 건수와 인덱스 칸 상태. 하나라도 어긋나면 배치를 실패로 멈추고 06:00 전에 다시 돌린다
SELECT COUNT(*) FROM ORD_ITEM_RANGE PARTITION (P201312);           -- 99,720
SELECT index_name, partition_name, status
  FROM user_ind_partitions
 WHERE partition_name = 'P201312'
   AND status <> 'USABLE';                                          -- 0행이어야 한다

개선 후 실행계획 학습용 예시 · 빈 블록 수는 교재 14화 §8.5 실측

xplan

[같은 99,720행 · 넣는 법만 다르다 (분석 DB · ARCHIVELOG · LOGGING · 학습용 어림)]
  방법                                              redo       경과       P201312 고수위선 아래     잠금(커밋까지)
  지금   DELETE + 일반 INSERT (인덱스 켠 채)           약 110 MB  2분 10초   2,246블록                 TM 3
  ⓐ     TRUNCATE PARTITION + APPEND (인덱스 켠 채)    약 41 MB   약 4 초    약 3,250블록 (빈 1,006)    TM 6
  ⓑ     TRUNCATE PARTITION + 칸 인덱스 끄기
         + 일반 INSERT + 칸 REBUILD                   약 26 MB   약 3 초    2,246블록                 TM 3
  (참고) ⓐ + 칸 인덱스 끄기 · REBUILD                 약 24 MB   약 3 초    약 3,250블록 (빈 1,006)    TM 6

[06:00 매장 집계 리포트 — 이번 달 칸 FULL]
  지금 · ⓑ   Buffers 약 2,250
  ⓐ         Buffers 약 3,255 (빈 블록까지 고수위선 아래라 다 읽는다 · +45%)

모범 답안 서술

지금 방식의 비용은 행 단위 일에서 나온다. ① DELETE는 칸을 하나만 열지만(PARTITION RANGE SINGLE, 2,244블록), 96,412행마다 테이블 행 하나와 로컬 인덱스 키 둘(X01, X02)을 지우고, 지우기 전 모습을 undo에 적은 뒤 그 변경을 다시 redo에 남긴다. DELETE 줄의 Buffers 675K와 47초가 그 일이다. ② INSERT도 99,720행마다 테이블에 한 행, 인덱스 둘에 키를 하나씩 끼워 627K를 읽고 83초가 걸린다. 합쳐서 redo 약 110MB, undo 약 39MB, 블록 변경 약 113만이다. 버릴 행과 다시 넣을 행을 하나하나 기록하는 셈이다. 칸 단위 DDL로 바꾸면 ①이 사라진다. ALTER TABLE ORD_ITEM_RANGE TRUNCATE PARTITION P201312는 행을 지우지 않고 그 칸의 세그먼트를 비운 채 고수위선을 처음으로 되돌린다. 로컬 인덱스의 그 칸도 함께 비워진다. 다음에 MODIFY PARTITION … UNUSABLE LOCAL INDEXES로 그 칸의 로컬 인덱스만 끄고 넣은 뒤, REBUILD UNUSABLE LOCAL INDEXES로 그 칸만 다시 짓는다. 로컬 인덱스는 칸마다 따로 있으므로 다시 짓는 비용이 칸 크기(약 10만 행)에 비례하고, 다른 23칸의 인덱스는 건드리지 않는다. ⓐ와 ⓑ를 비교하면 이렇다. redo는 생각만큼 차이 나지 않는다. ARCHIVELOG에서 LOGGING 칸에 직접 경로로 쓰면 새 블록을 통째로 redo에 남기므로 데이터 몫(약 18MB)이 일반 INSERT(약 20MB)와 비슷하다. ⓐ는 인덱스를 켠 채 넣어 적재 끝에 키를 한꺼번에 고치는 몫이 더해져 약 41MB이고, ⓑ는 약 26MB다. 06:00 리포트가 읽는 블록에서는 ⓐ가 불리하다. 비운 칸에 직접 경로로 넣으면 8MB 첫 익스텐트의 남은 블록 약 1,006개를 비워 둔 채 다음 익스텐트부터 쓰고, 그 빈 블록은 고수위선 아래에 남는다(교재 14화 §8.5). 리포트의 전체 스캔이 약 2,250 → 약 3,255블록으로 45% 는다. ⓑ의 일반 INSERT는 비운 칸의 첫 블록부터 채우므로 2,246블록 그대로다. 잠금은 ⓐ가 커밋까지 TM 6을 걸고 ⓑ는 TM 3이다. 03:00 ~ 06:00에 다른 작업이 없으니 지금은 차이가 없지만, 정정 반영 시각이 낮으로 옮겨지면 ⓑ만 쓸 수 있다. 실패했을 때의 상태는 둘이 같다. TRUNCATE PARTITION은 DDL이라 암시적 커밋이 일어나고 되돌릴 수 없다. 적재가 실패하면 06:00 리포트까지 이번 달 칸이 0건으로 남는다. 그래서 ⑤에서 칸 건수와 인덱스 칸 상태를 확인하고, 어긋나면 배치를 실패로 멈춰 06:00 전에 다시 돌린다. 그래서 ⓑ를 고른다. redo는 110 → 약 26MB, 경과는 2분 10초 → 약 3초로 줄고, 리포트가 읽는 블록은 늘지 않으며, 잠금도 가볍다.

해설

ⓐ를 살리는 방법: 칸 인덱스 끄기를 더하면 redo는 ⓑ와 거의 같다(약 24MB). 빈 블록 함정은 칸에 작은 첫 익스텐트를 주면 피할 수 있다(교재 14화 실측: STORAGE (INITIAL 64K)로 지은 칸은 빈 블록 1,006 → 5, 6월 칸 FULL 1,169 → 169). 이미 있는 칸은 MOVE PARTITION … STORAGE (INITIAL 64K)로 한 번 다시 만들고, 앞으로 만들 칸은 만들 때 STORAGE 절을 준다.
NOLOGGING: 분석 DB라서 운영에서 언제든 다시 만들 수 있다면 칸을 NOLOGGING으로 두고 직접 경로로 넣어 redo를 거의 0으로 만들 수 있다. 대신 그 블록은 다음 백업 전까지 복구할 수 없다.
EXCHANGE: 스테이징 테이블을 CTAS로 짓고 같은 모양의 인덱스를 만든 뒤 EXCHANGE PARTITION … INCLUDING INDEXES WITHOUT VALIDATION으로 바꿔 끼우면, 적재가 실패해도 칸에는 어제 데이터가 남는다. 사전만 바꾸므로 바꿔 끼우는 순간도 짧다. 파티션 교환은 PART 단원에서 따로 다룬다.
GLOBAL 인덱스가 있었다면 TRUNCATE PARTITION에 UPDATE GLOBAL INDEXES를 붙여야 한다. 빼먹으면 GLOBAL 인덱스가 UNUSABLE이 되어 그 인덱스를 쓰던 조회가 조용히 느려진다(PART-003).
파티션 없는 테이블에서 같은 일을 하면 인덱스 전체를 다시 지어야 해서 증분 적재에는 맞지 않는다(DML-037). 로컬 인덱스의 칸 단위 끄고 켜기는 파티션이 주는 이점 중 하나다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 빈 블록 수는 교재 14화 §8.5 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 빈 블록 수는 교재 14화 §8.5 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황