인덱스를 더 만든 적이 없다는데 정산 적재의 redo가 두 달 새 두 배가 됐다

중급INSERT한빛유통149 / 700

상황

야간 정산 배치는 매일 01:00에 T_ORD_STLM을 비우고 T_ORD에서 10만 건을 직접 경로로 다시 채운다. 두 달 전에는 적재 단계가 0.9초, redo 23MB였는데 지금은 1.8초, 51MB다. 운영은 ARCHIVELOG라 아카이브 로그도 그만큼 늘었다. 오과장은 이렇게 말한다. "적재 SQL도 건수도 그대로다. 화면에서 쓰는 인덱스도 두 개 그대로라고 들었다." 알아보니 조민우가 두 달 동안 「고객 정산 내역」과 「일자별 대사」를 튜닝하며 인덱스를 여러 개 만들어 재 보았고, 쓰지 않기로 한 것은 지우지 않고 INVISIBLE로 돌려 두었다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · 매일 TRUNCATE 뒤 INSERT /*+ APPEND */ … SELECT로 다시 채운다 · 낮에는 읽기만 한다 · 테이블과 인덱스는 LOGGING이다
인덱스 다섯 개(아래 USER_INDEXES) · 화면이 쓰는 것은 IX_STLM_CUS_DT(「고객 정산 내역」)와 IX_STLM_DT(「일자별 대사」)다
「일자별 고객 정산」 SQL에는 9월에 조민우가 IX_STLM_DT_CUS를 재 보며 붙인 /*+ INDEX(s IX_STLM_DT_CUS) */ 힌트가 그대로 남아 있다
「정산 리포트」 배치(매일 05:00)는 소스 첫머리에서 ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE를 실행한다 · 9월에 조민우가 숨긴 인덱스를 재 보려고 넣은 줄이다

원본 SQL

sql

-- 야간 정산 배치 · 01:00 · 적재 단계
TRUNCATE TABLE T_ORD_STLM;

INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD;
COMMIT;

실행계획 학습용 예시 · 인덱스당 적재 비용은 교재 04화 · 14화 실측 비례

xplan

[적재 단계 · T_ORD_STLM 10만 건 · 배치 로그]
              인덱스 (VISIBLE / INVISIBLE)   경과     redo
  8월 26일    2 / 0                          0.9초    23.1MB
  9월 23일    2 / 2                          1.5초    41.2MB
  10월 28일   2 / 3                          1.8초    50.6MB

[USER_INDEXES · T_ORD_STLM]
INDEX_NAME        COLUMNS                VISIBILITY  LEAF_BLOCKS  만든 달
----------------  ---------------------  ----------  -----------  -------
IX_STLM_CUS_DT    (CUS_ID, STLM_DT)      VISIBLE             392  6월
IX_STLM_DT        (STLM_DT)              VISIBLE             270  6월
IX_STLM_DT_CUS    (STLM_DT, CUS_ID)      INVISIBLE           392  9월
IX_STLM_CUS_AMT   (CUS_ID, STLM_AMT)     INVISIBLE           315  9월
IX_STLM_ORD_AMT   (ORD_SEQ, STLM_AMT)    INVISIBLE           250  10월

[DBA_INDEX_USAGE · 9월 29일 ~ 10월 28일]
IX_STLM_CUS_DT 181,220 · IX_STLM_DT 9,410 · IX_STLM_DT_CUS 0 · IX_STLM_CUS_AMT 30 (LAST_USED 10/28 05:01) · IX_STLM_ORD_AMT 0

[V$SQL · IX_STLM_CUS_AMT를 쓰는 SQL · MODULE STLM_REPORT]
SELECT CUS_ID, SUM(STLM_AMT) FROM T_ORD_STLM GROUP BY CUS_ID
---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |   2000 |00:00:00.02 |     322 |
|   1 |  HASH GROUP BY        |                 |      1 |   2000 |   2000 |00:00:00.02 |     322 |
|   2 |   INDEX FAST FULL SCAN| IX_STLM_CUS_AMT |      1 |   100K |   100K |00:00:00.01 |     322 |
---------------------------------------------------------------------------------------------------------

문제

적재 SQL도 건수도 그대로인데 redo와 시간이 늘어난 이유를 배치 로그와 USER_INDEXES로 설명하시오. 숨긴 인덱스 하나가 적재에 더하는 비용도 어림하시오.
숨긴 인덱스 셋을 각각 어떻게 할지 정하시오. 힌트가 남아 있는 IX_STLM_DT_CUS와 사용 이력이 30번인 IX_STLM_CUS_AMT는 근거를 따로 쓰시오.
판정에 맞는 정리 절차를 SQL로 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 「정산 리포트」 배치 첫머리의 세션 설정을 지운다 — 검증할 때 쓰는 줄이 운영 배치에 남아, 숨긴 인덱스를 이 배치만 쓰고 있었다
--    ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   ← 삭제
--    지운 뒤 리포트 SQL 은 FULL 640 블록 (지금 FFS 322) · 하루 한 번이라 받아들인다

-- ② 아무 화면도 못 쓰는 숨긴 인덱스 셋을 지운다 (INVISIBLE 이어도 매일 밤 적재 때마다 정렬해서 고친다)
DROP INDEX IX_STLM_DT_CUS;     -- 「일자별 고객 정산」의 INDEX 힌트는 지금도 무시되고 있다 → 힌트도 뗀다
DROP INDEX IX_STLM_ORD_AMT;
DROP INDEX IX_STLM_CUS_AMT;    -- ① 을 반영하고 리포트가 FULL 로 도는 것을 하루 본 뒤

-- ③ 적재는 그대로
TRUNCATE TABLE T_ORD_STLM;

INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD;
COMMIT;

개선 후 실행계획 학습용 예시 · 인덱스당 적재 비용은 교재 04화 · 14화 실측 비례

xplan

[정리 뒤 · 적재 단계]  인덱스 2 / 0 · 경과 0.9초 · redo 23.0MB
[「정산 리포트」 · 세션 설정을 지운 뒤]  TABLE ACCESS FULL T_ORD_STLM · Buffers 640 · 하루 한 번
[「일자별 고객 정산」]  정리 전후 같은 계획 (IX_STLM_DT 범위 스캔) · 힌트는 떼었다

모범 답안 서술

적재 SQL도 건수도 그대로인데 redo가 23.1 → 41.2 → 50.6MB로 늘어난 시점은 INVISIBLE 인덱스가 둘, 셋으로 늘어난 시점과 정확히 겹친다. INVISIBLE은 옵티마이저가 그 인덱스를 고르지 못하게 할 뿐, 테이블에 행이 들어가면 인덱스는 VISIBLE과 똑같이 고쳐진다. 직접 경로 적재는 넣은 행의 키를 모아 정렬한 뒤 인덱스마다 한 번에 넣으므로, 인덱스 하나마다 정렬과 인덱스 블록 쓰기, 그 redo가 따로 든다. 로그로 나누면 숨긴 인덱스 하나에 redo 약 9MB, 시간 약 0.3초다. 셋이면 매일 밤 약 27MB, 0.9초이고, ARCHIVELOG라 아카이브 로그도 그만큼 는다. 셋은 아무 화면도 쓰지 못하는 인덱스다. IX_STLM_DT_CUS와 IX_STLM_ORD_AMT는 30일 사용 이력이 0이다. 「일자별 고객 정산」에 IX_STLM_DT_CUS를 가리키는 INDEX 힌트가 남아 있지만, INVISIBLE 인덱스는 힌트로 이름을 찍어도 쓰이지 않으므로 그 SQL은 지금도 힌트를 무시하고 IX_STLM_DT로 돌고 있다. 지워도 실행계획은 바뀌지 않고, 힌트는 함께 떼어 낸다. IX_STLM_CUS_AMT는 숨긴 인덱스인데 사용 이력이 30번(매일 05:01)이다. 「정산 리포트」 배치가 첫머리에서 OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE로 세션을 바꾸기 때문이다. 검증할 때 쓰는 세션 설정이 운영 배치에 남아, 이 배치만 숨긴 인덱스를 FAST FULL SCAN으로 읽고 있다(322블록). 이 상태로 인덱스를 지우면 리포트의 계획이 예고 없이 바뀐다. 그래서 순서를 지킨다. 먼저 배치에서 그 줄을 지우고 리포트가 FULL(640블록)로 도는 것을 확인한다. 하루 한 번이라 318블록 차이는 문제가 아니다. 그다음 셋을 지운다. 적재 문장은 그대로 둔다. 숨긴 인덱스 셋을 지우면 적재 단계는 약 0.9초, redo 약 23MB로 두 달 전으로 돌아간다. 앞으로 INVISIBLE은 '지우기 전 관찰' 단계로만 쓰고, 숨긴 날과 지울 날을 승인서에 함께 적어 둔다.

해설

INVISIBLE과 UNUSABLE: 적재 비용을 없애려고 INVISIBLE 대신 UNUSABLE로 두자는 말이 나올 수 있다. UNUSABLE 인덱스는 DML이 고치지 않지만 쓰이지도 않는 정의만 남는다(유니크 인덱스라면 INSERT가 ORA-01502로 막힌다). 쓰지 않을 인덱스라면 지운다.
힌트가 가리키는 인덱스를 지울 때: 없는 인덱스를 가리키는 힌트는 에러 없이 무시된다. 그래서 지우기 전에 소스와 V$SQL의 문장에서 그 이름을 찾아 힌트를 함께 정리한다(교재 06화 힌트 유효기간 대장).
세션 설정의 흔적: OPTIMIZER_USE_INVISIBLE_INDEXES 같은 검증용 설정이 운영에 남았는지는 숨긴 인덱스의 사용 이력이 0이 아닌 것으로 드러난다. 숨긴 인덱스가 쓰였다면 누가 세션을 바꿨는지부터 찾는다.
일반 INSERT였다면: 이 적재는 직접 경로라서 인덱스가 늘어도 행마다 끼워 넣는 일반 INSERT보다는 덜 비싸다. 일반 INSERT라면 인덱스 하나가 10만 건에 redo 약 24MB씩 붙는다(교재 04화 §9 비례).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스당 적재 비용은 교재 04화 · 14화 실측 비례 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스당 적재 비용은 교재 04화 · 14화 실측 비례 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황