14화 처방대로 바꾼 주문상품 일 적재가 0.6초에서 41초로 늘었다

중급INSERT버거462 / 700

상황

버거원 본사는 매일 03:00에 전날 POS 주문상품을 ORD_ITEM에 더한다. 하루 약 3,400행이다. 지난달 신입 개발자가 교재 14화의 처방(인덱스를 끄고 직접 경로로 넣은 뒤 다시 짓는다)을 이 단계에 그대로 옮겼다. PK는 유니크라서 끌 수 없다는 것까지 알고 ORD_ITEM_X01만 끈다. 그 뒤로 적재 단계가 0.6초에서 41초로 늘었고, 아카이브 로그는 하루 70MB 넘게 늘었다. 03:02에 도는 본사 재고 연동 조회도 가끔 2초씩 걸린다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · 날짜순 적재 · 하루 약 3,400행(테이블의 0.14%)
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID): 리프 약 8,900블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS): 리프 약 9,500블록
STG_ORD_ITEM 수신 테이블: 날마다 비우고 POS 파일을 받는다(약 3,400행 · 80블록).
03:02에 본사 재고 연동이 전날 주문상품을 ORD_DT로 읽는다(평소 ORD_ITEM_X01 · 약 120블록).
운영은 ARCHIVELOG이고 테이블과 인덱스는 LOGGING이다. 03:00 ~ 03:02에 이 테이블에 쓰는 다른 세션은 없다.

원본 SQL

sql

-- 매일 03:00 · 전날 주문상품 적재 (지난달 수정본)
ALTER INDEX ORD_ITEM_X01 UNUSABLE;

INSERT /*+ APPEND */ INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS
  FROM STG_ORD_ITEM;
COMMIT;

ALTER INDEX ORD_ITEM_X01 REBUILD;

실행계획 학습용 예시

xplan

[적재 단계 — 바꾸기 전과 뒤 (운영 · 사흘 평균)]
                                    바꾸기 전                      바꾼 뒤
  넣는 법                           일반 INSERT · 인덱스 켠 채      X01 끄고 → APPEND → REBUILD
  INSERT 3,412행                    0.6 초 · redo 2.7 MB           0.1 초 · redo 1.6 MB
  ALTER INDEX ORD_ITEM_X01 REBUILD  —                              40.8 초 · redo 74.3 MB
  합계                              0.6 초 · redo 2.7 MB           40.9 초 · redo 75.9 MB

[REBUILD 한 번의 일 — 세션 통계]
  physical reads (ORD_ITEM 전체 스캔)    55,265 블록
  sorts (rows)                          2,476,129
  Used-Tmp                              약 62 MB
  새로 쓴 인덱스 블록                    약 9,500

[03:02 재고 연동 조회 — REBUILD가 끝나기 전에 돈 날]
---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |   3412 |00:00:01.92 |   55270 |
|*  1 |  TABLE ACCESS FULL | ORD_ITEM |      1 |   3400 |   3412 |00:00:01.92 |   55270 |
---------------------------------------------------------------------------------------
   1 - filter("ORD_DT"=:B1)
  (평소: INDEX RANGE SCAN ORD_ITEM_X01 · Buffers 약 120)

문제

바꾼 뒤의 41초와 redo 75.9MB가 어디서 나오는지 REBUILD가 하는 일로 설명하시오.
이 적재를 고치고, 인덱스를 끄고 다시 짓는 방법이 맞는 적재와 맞지 않는 적재를 가르는 기준을 쓰시오.
바꾼 뒤 03:02 재고 연동 조회가 가끔 느렸던 이유를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매일 03:00 · 테이블의 0.14%를 더하는 증분 적재 — 인덱스는 켠 채 일반 INSERT
--   (켠 채 넣는 비용은 넣는 3,412 행에 비례하고, REBUILD 비용은 테이블 247만 행에 비례한다)
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS
  FROM STG_ORD_ITEM;
COMMIT;

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

xplan

[적재 단계 — 되돌린 뒤 (운영)]
  INSERT 3,412행 · 인덱스 둘 켠 채    0.6 초 · redo 2.7 MB · REBUILD 없음
  아카이브 로그                       하루 약 73 MB 줄어든다

[03:02 재고 연동 조회]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |   3412 |00:00:00.01 |     118 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |   3400 |   3412 |00:00:00.01 |     118 |
|*  2 |   INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   3400 |   3412 |00:00:00.01 |      16 |
-------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:B1)

모범 답안 서술

41초의 몸통은 REBUILD다. 꺼진 인덱스를 다시 지으려면 인덱스가 아니라 테이블을 처음부터 읽어야 한다. 3,412행을 더하려고 ORD_ITEM 전체 55,265블록을 읽고, 2,476,129행의 키를 정렬해(Used-Tmp 약 62MB) 리프 약 9,500블록짜리 인덱스를 처음부터 다시 쓴다. ARCHIVELOG이고 인덱스가 LOGGING이라 새로 쓴 인덱스 블록이 그대로 redo가 되어 74.3MB가 나왔다. APPEND로 넣는 몫은 0.1초, 1.6MB뿐이다. 고친 방법은 인덱스를 켠 채 일반 INSERT로 되돌리는 것이다. 켠 채 넣는 비용은 넣는 행 수(3,412행)에 비례하고, REBUILD 비용은 테이블 전체 크기(247만 행)에 비례한다. 이 적재는 테이블의 0.14%만 더하므로 켠 채 넣는 쪽이 훨씬 싸다. 40.9초 → 0.6초, redo 75.9MB → 2.7MB로 돌아간다. 인덱스를 끄고 다시 짓는 방법은 테이블을 비우고 통째로 채우는 재적재(교재 14화의 T_ORD_BIG 100만 행)처럼 넣는 양이 테이블의 대부분일 때의 처방이다. 그때는 켠 채 넣으면 행마다 키를 끼우고 리프를 쪼개는 일이 행 수만큼 생기고, 다시 짓는 쪽은 한 번 정렬하면 끝난다. 넣는 양이 기존 양에 비해 작을수록 켠 채 넣는 쪽이 유리하고, 그 사이(수십 %)는 재 보고 정한다. 03:02 재고 연동 조회가 가끔 느렸던 것은 REBUILD가 끝나기 전에 조회가 돌았기 때문이다. 인덱스가 꺼진 동안 옵티마이저는 에러 없이 그 인덱스를 건너뛰고(skip_unusable_indexes) TABLE ACCESS FULL로 55,270블록을 읽었다. 평소에는 약 120블록이다.

해설

처방은 조건과 함께 옮긴다. 교재 14화의 표는 '비우고 통째로 채우는 100만 행' 조건에서 잰 것이다. 같은 무기라도 증분 적재에서는 반대 결과가 나온다.
APPEND는 남겨도 되지만 이득이 작다. 하루 80블록을 고수위선 위에 쓰고, 커밋할 때까지 TM 6을 건다. 새벽에 이 테이블에 쓰는 세션이 생기면 그때 문제가 된다.
증분이 크고 테이블이 날짜로 파티션되어 있다면, 넣는 칸의 로컬 인덱스만 끄고 그 칸만 다시 짓는 방법이 있다. 다시 짓는 비용이 칸 크기에 비례하므로 증분 적재에도 맞는다(DML-042).
REBUILD ONLINE은 다른 세션의 DML을 막지 않을 뿐 읽고 정렬하는 일량은 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황