서버 이관 계획서에는 'Data Pump 병렬 8' 한 줄뿐인데, 1년 전 이관 뒤 월마감이 38배 무거워졌던 일이 있다

실전DDL버거103 / 700

상황

버거원 DB가 다음 달 새 서버로 옮겨 간다. 인프라팀 계획서에는 "Data Pump 병렬 8로 내보내고 들여온다. 인덱스는 들여올 때 다시 만든다" 한 줄만 있다. 점검 시간은 토요일 새벽 4시간이다. 박팀장은 이렇게 주문했다. "1년 전 서버 교체 때 ORDER BY 없이 옮겨서 월마감이 41,825블록이 됐다(ACC-002). 그때 미뤄 둔 정렬 재적재를 이번에 같이 한다. 주문과 주문상품을 각각 무엇의 순서로 쌓을지, 절차와 대가, 옮긴 뒤 무엇으로 확인할지 적어 와라."

테이블과 인덱스

ORD_RANDOM(운영 주문): 1,000,000행 · 22,414블록 · 1년 전 순서 없이 적재 · ORD_RANDOM_PK (ORD_NO) CF 약 999,950 · ORD_RANDOM_X01 (ORD_DT, ORD_HMS) CF 999,958
ORD_ITEM(운영 주문상품): 2,472,717행 · 55,265블록 · (ORD_NO, ITEM_ID) 순으로 쌓여 있다 · ORD_ITEM_PK (ORD_NO, ITEM_ID) CF 약 55,100 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) CF 약 55,200 · 리프 약 9,320
ORD_NO는 처음 적재할 때 주문 시각 (ORD_DT, ORD_HMS) 순으로 매긴 번호다 · POS는 주문이 생길 때마다 시각 순으로 INSERT한다
비교용 ORD_ITEM_RANDOM: ORD_ITEM을 무작위 순서로 복제한 개발 서버의 테이블(같은 행 수, 약 55,300블록) · 같은 정의의 X01 CF 약 2,470,000
교재 04화 실측(100만 행): 정렬 CTAS 2.28초 · 인덱스 재생성 1.99초 · 통계 0.87초(NOARCHIVELOG) · 새 서버는 ARCHIVELOG다
Data Pump를 병렬로 돌리면 행이 들어가는 순서를 보장하지 않는다

원본 SQL

sql

-- 인프라팀 계획: expdp / impdp PARALLEL=8 (테이블 그대로) · 인덱스는 impdp 가 다시 만든다

-- 이관 뒤에도 빨라야 하는 SQL 넷
-- S1 월마감 「가맹점별 주문 건수」
SELECT o.SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_RANDOM o
 WHERE o.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY o.SHOP_NO;
-- S2 「일별 주문 조회」
SELECT o.ORD_NO, o.ORD_HMS, o.SHOP_NO
  FROM ORD_RANDOM o
 WHERE o.ORD_DT = :v_dt
 ORDER BY o.ORD_HMS;
-- S3 「주문 상세」
SELECT i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD_ITEM i
 WHERE i.ORD_NO = :v_ord_no;
-- S4 「월 상품 판매」
SELECT i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM i
 WHERE i.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY i.ITEM_ID;

실행계획 학습용 예시 · 정렬 전후 숫자는 교재 04화 실측(ORD · ORD_RANDOM)

xplan

[S4 · 지금 서버 · 2012년 1월]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |     92 |00:00:00.06 |    2690 |
|   1 |  HASH GROUP BY                       |              |      1 |     92 |     92 |00:00:00.06 |    2690 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |  10050 |   102K |00:00:00.05 |    2690 |
|*  3 |    INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |  10050 |   102K |00:00:00.01 |     390 |
-----------------------------------------------------------------------------------------------------------------
   3 - access("I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)

[측정 · 지금 서버 · Buffers]
  S1 월마감 2012년 1월 (ORD_RANDOM_X01)   41,825      (같은 SQL 을 이관 전 정렬본 ORD 에서 1,086)
  S2 일별 주문 2012-01-01 (1,371건)        1,378      (ORD 에서 39)
  S3 주문 상세 (주문 한 건 2.47줄)          4         (주문 한 건의 줄이 한 블록에 있다)
  S4 월 상품 판매 2012년 1월                2,690      (같은 SQL 을 ORD_ITEM_RANDOM 에서 103,310)

문제

두 테이블을 각각 무엇의 순서로 쌓아야 하는지 정하시오. 그 순서 하나로 PK와 X01의 CLUSTERING_FACTOR가 함께 좋아지는 이유를 설명하고, ORD_ITEM을 순서 없이 옮기면 S3과 S4가 어떻게 되는지 숫자로 설명하시오.
새 서버에서 할 이관 절차를 SQL과 DDL로 작성하시오(정렬 적재, 제약과 인덱스, 통계, 확인 쿼리). 정렬 적재의 대가(시간, TEMP, 잠금, 로그)도 적으시오.
이관 뒤 좋은 CF가 유지되는 조건과 다시 무너지는 경우를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ⓪ 테이블 정의만 먼저 옮긴다 (impdp CONTENT=METADATA_ONLY, 인덱스와 제약은 빼고)
-- ① 정렬해서 쌓는다 — 병렬 도구에 순서를 맡기지 않는다 · 옛 서버는 DB 링크 OLD_DB
INSERT /*+ APPEND */ INTO ORD_RANDOM
SELECT * FROM ORD_RANDOM@OLD_DB ORDER BY ORD_DT, ORD_HMS, ORD_NO;
COMMIT;
INSERT /*+ APPEND */ INTO ORD_ITEM
SELECT * FROM ORD_ITEM@OLD_DB ORDER BY ORD_NO, ITEM_ID;
COMMIT;

-- ② 제약과 인덱스는 적재가 끝난 뒤에 만든다
ALTER TABLE ORD_RANDOM ADD CONSTRAINT ORD_RANDOM_PK PRIMARY KEY (ORD_NO);
CREATE INDEX ORD_RANDOM_X01 ON ORD_RANDOM (ORD_DT, ORD_HMS);
ALTER TABLE ORD_ITEM ADD CONSTRAINT ORD_ITEM_PK PRIMARY KEY (ORD_NO, ITEM_ID);
CREATE INDEX ORD_ITEM_X01 ON ORD_ITEM (ORD_DT, ORD_HMS);

-- ③ 통계
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD_RANDOM', cascade => TRUE);
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD_ITEM',   cascade => TRUE);
END;
/

-- ④ 확인 — CF 가 행 수가 아니라 블록 수에 가까운가
SELECT i.table_name, i.index_name, i.clustering_factor, t.blocks, t.num_rows
  FROM user_indexes i, user_tables t
 WHERE t.table_name = i.table_name
   AND i.table_name IN ('ORD_RANDOM', 'ORD_ITEM')
 ORDER BY i.table_name, i.index_name;
-- ⑤ S1 ~ S4 를 다시 재서 위 표와 대조한다 (S1 약 1,086 · S2 약 39 · S3 4 · S4 약 2,690)

개선 후 실행계획 학습용 예시 · 정렬 전후 숫자는 교재 04화 실측(ORD · ORD_RANDOM)

xplan

[이관 리허설 · 개발 서버 · 정렬 적재 뒤 S2 2012-01-01]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |   1371 |00:00:00.01 |      39 |
|   1 |  SORT ORDER BY                       |                |      1 |   1370 |   1371 |00:00:00.01 |      39 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |   1370 |   1371 |00:00:00.01 |      39 |
|*  3 |    INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |   1370 |   1371 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------------------------------------

[④ 확인 쿼리 · 리허설]
TABLE_NAME  INDEX_NAME       CLUSTERING_FACTOR   BLOCKS    NUM_ROWS
ORD_ITEM    ORD_ITEM_PK                 55,1xx   55,265   2,472,717
ORD_ITEM    ORD_ITEM_X01                55,2xx   55,265   2,472,717
ORD_RANDOM  ORD_RANDOM_PK               22,2xx   22,422   1,000,000
ORD_RANDOM  ORD_RANDOM_X01              22,223   22,422   1,000,000

[S1 ~ S4]  41,825 → 1,086 · 1,378 → 39 · 4 → 4 · 2,690 → 2,690

모범 답안 서술

주문은 주문 시각 (ORD_DT, ORD_HMS) 순으로, 주문상품은 (ORD_NO, ITEM_ID) 순으로 쌓는다. ORD_NO는 처음 적재할 때 주문 시각 순으로 매긴 번호이므로, 주문을 시각 순으로 정렬하면 주문 번호도 같은 순서가 된다. 그래서 정렬 한 번으로 ORD_RANDOM_PK와 ORD_RANDOM_X01의 CF가 함께 블록 수(약 22,200)로 내려온다. 주문상품도 같다. (ORD_NO, ITEM_ID)로 쌓으면 한 주문의 줄이 한 블록에 모이고(PK), 주문 번호가 시각 순이니 날짜 순서도 맞는다(X01). 한 테이블은 한 가지 순서로만 쌓을 수 있지만, 이 두 테이블에서는 키들이 같은 순서를 따르므로 모든 인덱스가 그 혜택을 받는다. 숫자로 보면 S1 월마감은 41,825 → 약 1,086, S2 일별 주문은 1,378 → 약 39다(교재 04화의 ORD 실측). 반대로 주문상품을 순서 없이 옮기면 지금 좋은 것을 잃는다. 개발 서버의 ORD_ITEM_RANDOM으로 재면 S4 월 상품 판매가 2,690 → 103,310블록, 38배가 된다. X01의 CF가 약 55,200에서 2,470,000으로 바뀌어 한 달 102,900행이 행마다 다른 블록이 되기 때문이다. S3 주문 상세도 한 주문의 2.47줄이 여러 블록에 흩어져 4 → 약 6블록이 된다. 병렬 Data Pump는 순서를 보장하지 않으므로, 지금 정렬되어 있는 ORD_ITEM도 ORDER BY로 순서를 적어서 옮긴다. 절차는 정의만 먼저 옮기고(METADATA_ONLY), 두 테이블을 INSERT /*+ APPEND */ … ORDER BY로 쌓은 뒤, 제약과 인덱스를 만들고 통계를 모은다. 마지막으로 USER_INDEXES의 CLUSTERING_FACTOR가 행 수가 아니라 USER_TABLES의 BLOCKS에 가까운지 확인하고, S1 ~ S4를 다시 재서 표와 대조한다. 대가는 다음과 같다. 정렬은 100만 행과 247만 행을 한 번씩 소트하므로 TEMP를 테이블 크기만큼(주문 약 176MB, 주문상품 약 430MB) 잡아 둔다. 교재 실측으로 100만 행 정렬 적재가 약 2.3초, 인덱스가 약 2초였지만 그것은 NOARCHIVELOG 기준이다. 새 서버는 ARCHIVELOG라 redo가 훨씬 크므로 리허설에서 시간을 재고 4시간 안에 드는지 확인한다. 적재하는 동안 테이블은 잠기므로 점검 시간에만 한다. 시간을 줄이려 NOLOGGING으로 쌓는다면 그 테이블은 복구할 수 없으므로 끝나자마자 백업을 받는다. 좋은 CF는 쌓이는 순서가 인덱스 키 순서와 같을 때 유지된다. POS는 주문이 생길 때마다 시각 순으로 INSERT하므로 새 주문도 날짜 순으로 붙는다(교재 04화: 5만 건을 키 순서대로 넣자 22,223 → 23,387). 반대로 누락분 백필이나 다음 이관을 ORDER BY 없이 하면 다시 무너진다(5%를 섞어 넣자 23,387 → 122,193). 그래서 이관과 백필 절차서에 ORDER BY를 넣고, 매달 CLUSTERING_FACTOR를 블록 수와 대조하는 점검을 둔다.

해설

함정: "새 서버로 옮기면 깨끗해진다"는 기대는 틀렸다. 도구는 행을 읽은 순서대로 옮길 뿐이라 흩어진 테이블은 흩어진 채로 간다. 순서는 SQL에 ORDER BY로 적어야만 생긴다.
CTAS … ORDER BY로 새로 만들어도 된다. 다만 CTAS는 제약, 기본값, 권한, 주석을 따로 챙겨야 한다. 정의를 Data Pump로 먼저 옮기고 INSERT로 채우면 정의가 원본과 같다.
병렬로 정렬 적재(PARALLEL)를 하면 빨라지지만, 작업자마다 따로 쓴 덩어리가 섞여 CF가 조금 나빠질 수 있다. 리허설에서 CF를 재 보고 정한다.
REBUILD나 MOVE로는 CF가 바뀌지 않는다(교재 04화 §4). 이번 이관은 1년 전 ACC-002에서 미뤄 둔 정렬 재적재를 잠금 걱정 없이 할 수 있는 기회다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 정렬 전후 숫자는 교재 04화 실측(ORD · ORD_RANDOM) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 정렬 전후 숫자는 교재 04화 실측(ORD · ORD_RANDOM) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황