INSERT 한 문장에 329초, 일요일 밤 재적재가 아침 9시를 넘긴다

기초INSERT한빛유통429 / 700

상황

일요일 22:00에 시작하는 야간 체인의 첫 단계는 T_ORD_BIG을 비우고 주문계 원천 100만 건을 다시 넣는다. 04:00 마감인데 이번 주는 09:12에 끝났다. 배치 담당 오과장: "10화 때 통계 수집 단계를 빼 봤는데도 안 줄었어요." 낮에는 정산 기간 조회(Q1)가 IX_ORD_BIG_DT_AMT를, 정산 목록 페이징(Q4)이 IX_ORD_BIG_DT_SEQ를 쓴다. IX_ORD_BIG_DT는 04화부터 '폐기 승인 대기'로 남아 있다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록. PK, FK, 트리거는 없고, 다른 세션은 체인이 끝난 뒤에 읽는다.
인덱스 셋(갓 만든 크기, 리프 블록 수): IX_ORD_BIG_DT (ORD_DT) 2,653 · IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) 3,206 · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ) 3,344
ORD_ST에 FREQUENCY 히스토그램이 있다(10화 통계 정책서). 원천 STG_ORD_BIG은 100만 행이다(주문계에서 받아 둔 사본).
연습 DB는 NOARCHIVELOG이고, 온라인 리두 로그는 10MB × 2다.

원본 SQL

sql

-- 일요일 22:00 · 체인 ① T_ORD_BIG 재적재 (오과장)
TRUNCATE TABLE T_ORD_BIG;

INSERT INTO T_ORD_BIG (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM STG_ORD_BIG;
COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG')

실행계획 실측 · 교재 14화(인덱스 사용 이력은 학습용 예시)

xplan

[단계별 — 연습 DB 복사본 · 인덱스 셋 켠 채 (교재 14화 실습/02 [A])]
  단계                     경과      redo        undo       db block changes   logical reads
  INSERT 100만 + COMMIT    329 초    916.6 MB    344.4 MB   5,455,266          7,949,703
    └ 그중 log file switch (checkpoint incomplete) 대기 309.5 초
  통계 수집                1.06 초     1.4 MB    0             15,617             37,327

[같은 5만 건 · 인덱스 개수만 바꿔 (교재 14화 실습/01)]
  인덱스                        redo        undo       db block changes
  0 개                           2.44 MB     0.13 MB        1,921
  1 개  (ORD_DT)                14.13 MB     4.34 MB      102,481
  2 개  +(ORD_DT, ORD_AMT)      26.09 MB     8.72 MB      201,060
  3 개  +(ORD_DT, ORD_SEQ)      38.55 MB    13.31 MB      301,704

[켠 채 일반 INSERT 로 채운 뒤의 인덱스 크기 (리프 블록)]
  IX_ORD_BIG_DT  2,653 → 4,374 · IX_ORD_BIG_DT_AMT  3,206 → 3,555 · IX_ORD_BIG_DT_SEQ  3,344 → 5,505

[인덱스 사용 이력 — DBA_INDEX_USAGE · 최근 30일]
  NAME                 TOTAL_ACCESS_COUNT   LAST_USED
  IX_ORD_BIG_DT                         0   —
  IX_ORD_BIG_DT_AMT                41,206   어제 17:52
  IX_ORD_BIG_DT_SEQ               318,440   어제 18:03

문제

329초와 redo 916.6MB가 어디서 나오는지 위 표로 설명하시오.
낮의 조회를 죽이지 않으면서 재적재 시간을 줄이도록 배치를 고치시오. 인덱스 셋을 각각 어떻게 할지, 그렇게 판단한 근거와 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (T2-002 승인 뒤) 쓰는 SQL 이 없고, 앞 컬럼이 같은 두 인덱스가 같은 조건을 받는 IX_ORD_BIG_DT 는 지운다
--   승인 전이라면 ALTER INDEX IX_ORD_BIG_DT INVISIBLE; 로 한 주 지켜본 뒤
DROP INDEX IX_ORD_BIG_DT;

-- 일요일 22:00 · 체인 ① — 비우고 · 끄고 · 직접 경로로 넣고 · 다시 짓는다
TRUNCATE TABLE T_ORD_BIG;
ALTER INDEX IX_ORD_BIG_DT_AMT UNUSABLE;      -- 끄는 것은 TRUNCATE 다음에 (14화 실습 순서)
ALTER INDEX IX_ORD_BIG_DT_SEQ UNUSABLE;

INSERT /*+ APPEND */ INTO T_ORD_BIG (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM STG_ORD_BIG;
COMMIT;

ALTER INDEX IX_ORD_BIG_DT_AMT REBUILD;       -- 다 넣은 뒤 정렬해서 한 번에 — 갓 만든 크기
ALTER INDEX IX_ORD_BIG_DT_SEQ REBUILD;

-- 통계 수집은 남긴다 — 직접 경로의 온라인 통계(STATS_ON_LOAD)는 ORD_ST 히스토그램을 모으지 않는다
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG')

개선 후 실행계획 실측 · 교재 14화(인덱스 사용 이력은 학습용 예시)

xplan

[같은 100만 행 · 넣는 법만 다르다 (교재 14화 실습/03 · 인덱스 셋 기준)]
  넣는 법                         적재 redo    적재 경과    인덱스 재생성    합계
  일반 INSERT · 켠 채 (원본)      916.6 MB     329 초       —                329 초
  APPEND · 켠 채                  268.8 MB     8.3 초       —                8.3 초
  일반 INSERT · 끄고 → 켜기        48.4 MB     1.05 초      5.08 초          6.1 초
  APPEND · 끄고 → 켜기              0.2 MB     0.26 초      4.74 초          5.0 초
  ※ 인덱스를 둘로 줄이면 재생성 몫이 더 준다 · NOARCHIVELOG 연습 DB 기준

[꺼진 동안의 낮 Q1 (교재 14화)]
  인덱스 꺼진 동안   TABLE ACCESS FULL   Buffers 5,822
  다시 켠 뒤         INDEX RANGE SCAN    Buffers    91

모범 답안 서술

329초의 몸통은 테이블이 아니라 인덱스다. 5만 건 표에서 인덱스가 0개면 redo 2.44MB, 블록 변경 1,921인데, 인덱스가 하나 늘 때마다 redo 약 12MB와 블록 변경 약 10만(행당 2)이 붙는다. 셋이면 redo는 15.8배, 블록 변경은 157배가 된다. 일반 INSERT는 행마다 인덱스 셋에서 정렬된 자리를 찾아 키를 끼우고 리프를 쪼개며, 그 변경마다 redo와 undo를 남긴다. 100만 건이면 redo가 916.6MB이고, 10MB 리두 로그를 아흔 번 넘게 갈아 끼우느라 329초 중 309.5초를 기다렸다. 무작위로 끼워 넣은 인덱스는 반쯤 빈 리프 때문에 최대 65% 커진다. 고친 배치는 TRUNCATE → 인덱스 UNUSABLE → INSERT /*+ APPEND */ → REBUILD 순서다. 테이블은 직접 경로로 블록째 쓰고, 인덱스는 다 넣은 뒤 정렬해서 한 번에 새로 짓는다. 100만 건 적재가 329초 → 약 5초로, 적재 redo가 916.6 → 0.2MB로 준다(연습 DB 기준). 인덱스별로 판단하면 이렇다. IX_ORD_BIG_DT는 DBA_INDEX_USAGE 사용 이력이 30일 동안 0이고, 앞 컬럼 ORD_DT가 같은 두 인덱스가 같은 조건을 받아 줄 수 있다. 그래서 끄지 않고 지운다(승인 전이라면 INVISIBLE로 한 주 지켜본다). DT_AMT와 DT_SEQ는 낮의 Q1과 Q4가 매일 쓰므로 남기고 밤에만 끈다. 대가도 있다. 꺼진 동안 그 인덱스를 쓰던 조회는 에러 없이 조용히 FULL로 간다(skip_unusable_indexes, Q1 91 → 5,822). 그래서 체인이 마감 안에 끝나야 한다는 조건이 더 무거워진다. UNIQUE나 PK 인덱스는 끄면 INSERT가 ORA-01502로 막혀 이 방법을 쓸 수 없다. 이 처방은 테이블을 통째로 다시 채우는 재적재라서 맞다. 큰 테이블에 일부만 더하는 적재라면 REBUILD가 테이블 전체를 다시 읽어 더 비쌀 수 있다. 통계 수집(1.06초)은 범인이 아니었으므로 남긴다.

해설

다른 정답: 인덱스를 DROP했다가 적재 뒤 CREATE INDEX로 다시 만든다. 결과는 같지만 인덱스 정의(이름, 컬럼, 저장 절)를 배치가 들고 있어야 하고, 중간에 실패하면 낮에 인덱스가 아예 없다. UNUSABLE은 정의가 딕셔너리에 남는다.
APPEND의 대가: 커밋할 때까지 TM 6 잠금이 걸려 다른 트랜잭션이 DML을 할 수 없다. DELETE로 비운 블록은 다시 쓰지 않는다. 자식 쪽 FK나 트리거가 있으면 조용히 일반 INSERT로 바뀌므로 실행계획에 LOAD AS SELECT가 있는지 확인한다(교재 14화 §4).
NOARCHIVELOG 연습 DB라서 APPEND의 redo가 거의 0이다. 운영이 ARCHIVELOG라면 NOLOGGING 사용을 미리 협의해야 하고, NOLOGGING 블록은 다음 백업 전까지 복구할 수 없다.
사용 이력 0의 무게: DBA_INDEX_USAGE는 기본이 표본 수집이라서 0이 곧 '안 쓴다'는 뜻은 아니다. AWR의 실행계획(DBA_HIST_SQL_PLAN)에서 인덱스 이름을 찾아보고, INVISIBLE로 한 주 지켜본 뒤 지운다(T2 승인서의 「적재 비용」 칸).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 14화(인덱스 사용 이력은 학습용 예시) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 14화(인덱스 사용 이력은 학습용 예시) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황