매주 인덱스를 전부 다시 만드는 정비 작업을 없애려 하자, 그래야 인덱스가 얕아진다는 반대가 나왔다

기초PLSQL한빛유통138 / 700

상황

운영 DB에는 2019년 외주 DBA가 만든 「주간 인덱스 정비」 작업이 있다. 매주 일요일 01:00에 sqlp 스키마의 인덱스를 하나도 빼지 않고 REBUILD ONLINE으로 다시 만든다. 작업 설명서에는 "인덱스는 쓸수록 깊어지고 느려지므로 매주 다시 만들어 높이를 낮춘다"라고 적혀 있다. 새로 온 DBA가 근거 없는 정기 작업을 정리하겠다며 이 작업을 목록에 올리자 조민우가 반대했다. "다시 만들어야 인덱스가 얕아지고 월요일 화면이 빨라진다." 박팀장은 이 작업이 무엇을 주는지 숫자로 보자고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 운영에서는 주문이 계속 INSERT된다
IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): 「기간 매출」용(2024년 6월 한 달 91블록) · 새 주문의 항목은 늘 맨 오른쪽 리프에 들어간다
IX_ORD_BIG_CUS (CUS_ID): 「고객 요약」(COUNT(*), 고객당 500건)용 · 새 주문의 항목은 고객 2,000명 각자의 자리에 끼어든다
정비 작업 한 번: 스키마 인덱스 23개(약 260MB) · 약 2분 30초 · redo 약 280MB(운영은 ARCHIVELOG라 그만큼 아카이브 로그가 쌓인다)
이 스키마 인덱스의 브랜치 블록 하나는 아래 블록을 약 400개 가리킨다

원본 SQL

sql

-- 「주간 인덱스 정비」 (2019) · DBMS_SCHEDULER 작업 JOB_IDX_WEEKLY · 매주 일요일 01:00
BEGIN
  FOR r IN (SELECT index_name
              FROM user_indexes
             WHERE index_type = 'NORMAL') LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || r.index_name || ' REBUILD ONLINE';
  END LOOP;
END;

실행계획 학습용 예시 · 리프 블록 수는 교재 04화 실측 규모

xplan

[USER_INDEXES · 정비 직전(일요일 00:55) → 직후(01:05)]
INDEX_NAME          BLEVEL    LEAF_BLOCKS        NUM_ROWS     CLUSTERING_FACTOR
------------------  --------  -----------------  -----------  ----------------------
IX_ORD_BIG_DT_AMT   2 → 2     3,214 → 3,206      1,000,000    999,999 → 999,999
IX_ORD_BIG_CUS      2 → 2     3,128 → 3,060      1,000,000    약 1,000,000 → 그대로
(다음 일요일 00:55에는 다시 3,214와 3,128 근처다. 넉 주 동안 같은 모양이 되풀이됐다)

[「고객 요약」 CUS_000123 · 정비 직전 토요일]
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE   |                |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN| IX_ORD_BIG_CUS |      1 |    500 |    500 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUS_ID"=:V_CUS)

[정비 직후 월요일 · 같은 SQL]          INDEX RANGE SCAN · Buffers 4
[「기간 매출」 2024년 6월 · 직전 / 직후]  INDEX RANGE SCAN · Buffers 91 / 91

문제

정비 직전과 직후의 BLEVEL과 LEAF_BLOCKS를 근거로, 이 작업이 인덱스 높이를 낮추지 못하는 이유를 설명하시오. 높이가 언제 하나 늘어나는지도 어림하시오.
「고객 요약」과 「기간 매출」의 Buffers가 정비 전후에 그대로인 이유를 설명하시오.
이 작업을 어떻게 할지 SQL로 작성하고, 인덱스를 다시 만드는 것이 맞는 경우를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 정기 정비 작업을 멈춘다 — 높이(BLEVEL 2)도, 화면이 읽는 블록도 바꾸지 못하고 redo 와 커서 무효화만 남긴다
BEGIN
  DBMS_SCHEDULER.DISABLE('JOB_IDX_WEEKLY');
END;
/

-- 다시 만들 근거는 숫자로 본다 : 크게 지운 뒤이거나, 건수는 그대로인데 리프가 계속 늘 때만
SELECT index_name, blevel, leaf_blocks, num_rows,
       ROUND(num_rows / NULLIF(leaf_blocks, 0)) AS rows_per_leaf,
       last_analyzed
  FROM user_indexes
 WHERE table_name = 'T_ORD_BIG'
 ORDER BY index_name;
-- 그런 인덱스가 나오면 그 인덱스 하나만 COALESCE (또는 REBUILD ONLINE) 하고, 그 인덱스를 쓰는 SQL 의 전후 Buffers 를 잰다

개선 후 실행계획 학습용 예시 · 리프 블록 수는 교재 04화 실측 규모

xplan

[작업을 끈 뒤 4주 · 매주 일요일 00:55]
INDEX_NAME          BLEVEL   LEAF_BLOCKS                    「고객 요약」  「기간 매출」 6월
IX_ORD_BIG_DT_AMT   2        3,214 · 3,216 · 3,217 · 3,219   —              91 · 91 · 91 · 91
IX_ORD_BIG_CUS      2        3,128 · 3,131 · 3,133 · 3,136   4 · 4 · 4 · 4  —
정비 작업 몫의 redo 280MB × 4주 · 일요일 01:00의 부하 · 월요일 아침의 재파싱이 사라졌다

모범 답안 서술

정비 직전과 직후를 비교하면 BLEVEL은 두 인덱스 모두 2 → 2이고, LEAF_BLOCKS는 3,214 → 3,206(0.2%), 3,128 → 3,060(2%)만 준다. BLEVEL 2는 루트, 브랜치, 리프의 세 단계라는 뜻이다. 브랜치 블록 하나가 아래 블록을 약 400개 가리키므로, 리프가 400개를 넘으면 루트 아래에 브랜치 단계가 하나 있어야 한다. 리프가 3천 개 남짓인 이 인덱스는 아무리 빽빽하게 다시 만들어도 BLEVEL이 2보다 낮아질 수 없다. 거꾸로 한 단계가 더 늘려면 리프가 약 400 × 400 = 16만 개(행으로는 약 5천만)가 되어야 하므로, 데이터가 50배쯤 늘기 전에는 깊어질 일도 없다. B-tree는 루트에서 어느 리프까지나 높이가 같아서, 지우거나 넣는다고 한쪽만 깊어지지도 않는다. 화면의 Buffers가 그대로인 것은 화면이 인덱스 전체가 아니라 필요한 리프 몇 블록만 읽기 때문이다. 「고객 요약」은 루트 1 + 브랜치 1 + 고객 한 명의 500항목이 든 리프 2블록으로 4블록이다. 리프 전체가 3,128개든 3,060개든 고객 한 명이 차지하는 리프 수는 같으므로 4 → 4다. 「기간 매출」도 한 달 치 리프 89블록과 수직 탐색 2블록으로 91 → 91이다. 다시 만든 효과도 오래 가지 않는다. IX_ORD_BIG_CUS는 새 주문이 고객 2,000명의 자리마다 끼어들어 꽉 찬 블록이 반씩 쪼개지므로, 일주일이면 다시 3,128 근처로 돌아간다. 맨 오른쪽에만 쌓이는 IX_ORD_BIG_DT_AMT는 처음부터 빈틈이 거의 없어 줄어들 것도 없다. 그래서 작업을 멈춘다(DBMS_SCHEDULER.DISABLE). 이 작업이 남기는 것은 매주 redo 약 280MB(그만큼의 아카이브 로그)와 2분 30초의 부하, 그리고 REBUILD도 DDL이라 그 인덱스를 쓰던 커서가 무효화되어 월요일 첫 실행에서 다시 파싱되는 일이다. 다시 만드는 것이 맞는 경우는 따로 있다. 테이블의 절반을 지운 뒤처럼 지운 항목만 남은 리프가 많을 때, 건수는 그대로인데 리프가 계속 늘 때, 그리고 그 인덱스를 쓰는 SQL의 Buffers가 실제로 줄어드는 것을 측정으로 확인했을 때다. 그때도 그 인덱스 하나만 COALESCE나 REBUILD ONLINE으로 처리한다.

해설

"리빌드하니 빨라졌다"는 기억: 리빌드 직후 빨라진 것처럼 보였다면 대개 같은 시각의 다른 원인(캐시, 통계, 다시 파싱되며 바뀐 실행계획)이다. 같은 SQL의 전후 Buffers를 재 보면 가릴 수 있다. 재파싱으로 계획이 바뀐 것이라면 리빌드의 효과가 아니라 우연이라서, 다음 주에는 거꾸로 나빠질 수도 있다.
REBUILD가 못 고치는 것: 높이 말고도 CLUSTERING_FACTOR가 있다(999,999 → 999,999). 테이블 행이 놓인 순서는 인덱스를 다시 만들어도 그대로다(ACC-002, 교재 04화 §4).
빽빽하게 만든 인덱스의 값: 끼어드는 INSERT가 많은 인덱스를 빈틈없이 다시 만들면 그 뒤 한동안 블록 분할이 몰린다. 분할은 redo와 짧은 대기를 만들므로 리빌드 직후의 INSERT가 오히려 무거워질 수 있다.
정기 작업을 없앨 때: 바로 지우지 않고 DISABLE로 몇 주 꺼 두면서 인덱스별 LEAF_BLOCKS와 대표 화면의 Buffers를 주마다 적어 둔다. 숫자가 그대로면 지운다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 리프 블록 수는 교재 04화 실측 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 리프 블록 수는 교재 04화 실측 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황