B-Tree 인덱스 DML 비용과 경합: Delete·Split·Hot Block·재구성

핵심 요약

Table Row를 변경하면 관련 B-Tree Index도 함께 유지됩니다. INSERT는 새 Index Entry를 추가하고, DELETE는 기존 Entry를 제거하며, Index Key Column의 값이 바뀌는 UPDATE는 기존 위치의 Entry 삭제와 새 위치의 Entry 추가를 모두 유발할 수 있습니다.

DML 처리량
→ Table Row 변경
→ 관련 Index Entry 유지
→ Leaf Block 공간 부족 시 Split
→ 동시 Session이 같은 Block을 수정하면 경합
→ Undo·Redo·CPU와 대기시간 증가

다만 다음 현상은 서로 다릅니다.

• Leaf Block Split: 공간이 부족한 Leaf를 나누어 B-Tree의 정렬·탐색 구조를 유지하는 정상 동작
• Right-Growing Hot Block: 증가 Key를 많은 Session이 동시에 Insert하여 오른쪽 끝 Leaf를 경쟁하는 동시성 문제
• ITL Wait: 같은 Block에서 동시 Transaction Entry를 확보하지 못한 문제
• 삭제 후 빈 공간: Entry가 제거된 뒤 Segment 내부에서 재사용될 수 있는 공간
• Index REBUILD: 새 Tree와 Segment를 만드는 별도 재구성 작업

따라서 leaf node splits가 증가했다는 사실만으로 Index가 손상됐거나 REBUILD가 필요하다고 판단하지 않습니다. 처리량·Redo·대기 이벤트·Hot Block 집중·조회 비용을 같은 시간 구간에서 함께 측정해야 합니다.

학습 목표

1. INSERT·DELETE·UPDATE가 B-Tree Index에 만드는 변경을 구분한다.
2. Leaf Block Split과 Right-Growing Hot Block을 구분한다.
3. Sequence Cache와 Index Right Edge 경합이 서로 다른 병목임을 설명한다.
4. Reverse Key Index·Hash-Partitioned Global Index·Scalable Sequence의 적용 조건을 판단한다.
5. 삭제된 Entry의 공간 재사용과 Segment 축소를 구분한다.
6. COALESCE와 REBUILD의 비용·효과 차이를 설명한다.
7. Invisible Index와 Unusable Index의 DML 유지 차이를 구분한다.
8. Segment 통계·대기 이벤트·Dictionary 통계를 구간 Delta로 분석한다.

1. DML이 B-Tree Index에 만드는 작업

다음 Index를 가정합니다.

CREATE INDEX orders_status_date_ix
ON orders(status, order_date);

1.1 INSERT

INSERT INTO orders(order_id, status, order_date, amount)
VALUES (10001, 'NEW', SYSDATE, 30000);

새 Row에 대해 (status, order_date, ROWID) Entry를 정렬 위치의 Leaf Block에 추가합니다. 해당 Leaf에 충분한 여유 공간이 없으면 새 Block을 확보하고 Entry를 나누는 Split이 발생할 수 있습니다.

1.2 DELETE

DELETE FROM orders
WHERE order_id = 10001;

해당 Row를 가리키는 관련 Index Entry가 제거됩니다. 그러나 Entry 삭제가 곧바로 Index Segment의 Extent를 Tablespace로 반환하거나 Segment 크기를 줄인다는 뜻은 아닙니다. 비워진 Leaf 공간은 같은 Key 영역에 후속 Entry가 들어올 때 재사용될 수 있습니다.

1.3 Index Key UPDATE

UPDATE orders
SET status = 'DONE'
WHERE order_id = 10001;

status가 Index Key Column이므로 기존 (NEW, order_date, ROWID) Entry를 제거하고 새 (DONE, order_date, ROWID) Entry를 추가합니다. 반면 amount처럼 해당 Index에 포함되지 않은 Column만 변경하고 Row가 이동하지 않는다면 이 Index의 Key Entry 유지 작업은 발생하지 않습니다.

대상 Row 수를 N, 전체 Index 수를 K_all, 변경된 Key Column을 포함한 Index 수를 K_changed라고 하면 논리적 비교 출발점은 다음과 같습니다.

INSERT Entry 추가량 ≈ N × K_all
DELETE Entry 제거량 ≈ N × K_all
Index Key UPDATE Entry 변경량 ≈ 2 × N × K_changed

이 식은 정확한 I/O나 Redo Byte 공식이 아닙니다. Key 폭, BLEVEL, Leaf 분포, Cache 상태, 동시성, Split, Unique 검사에 따라 실제 비용은 달라집니다.

2. Leaf Block Split은 언제 발생하는가

B-Tree는 Key 순서를 유지해야 합니다. Insert 대상 Leaf Block에 새 Entry를 저장할 공간이 부족하면 Oracle은 새 Block을 확보하고 Entry를 재배치하여 Tree의 탐색 구조를 유지합니다.

대상 Leaf 공간 부족
→ 새 Leaf Block 확보
→ 기존·신규 Entry 재배치
→ 상위 Branch의 경계 Entry 조정 가능
→ 추가 CPU·Redo·Buffer 변경

leaf node splits는 추가 값을 Insert하기 위해 Index Leaf Node가 Split된 횟수를 나타내는 누적 통계입니다. Split 자체는 오류가 아닙니다. 다음 조건이 함께 나타날 때 성능 문제 후보가 됩니다.

• 동일 DML 처리량 대비 Split Delta가 급격히 증가
• buffer busy waits 또는 RAC의 gc buffer busy 계열 대기 증가
• 특정 Index·Partition·File·Block에 대기가 집중
• Redo·CPU가 처리 Row 수보다 빠르게 증가
• 응답시간과 처리량이 실제로 악화

초기 적재나 자연스러운 성장 과정에서는 Split이 많아도 일시적일 수 있습니다. 누적값 하나가 아니라 업무 구간 전후의 Delta와 처리 Row 수를 함께 비교합니다.

3. Right-Growing Index와 Hot Block

Sequence·Identity·시간값처럼 계속 증가하는 Key가 일반 B-Tree Index의 선두에 있으면 신규 Entry가 주로 오른쪽 끝 Leaf 영역에 들어갑니다.

100001 → 오른쪽 끝 Leaf
100002 → 오른쪽 끝 Leaf
100003 → 오른쪽 끝 Leaf
다수 Session 동시 Insert
→ 같은 Leaf·인접 Branch Block 수정 경쟁
→ Hot Block 가능성

단일 Session의 순차 Insert에서는 Cache 지역성이 좋아질 수 있습니다. 문제는 동시에 많은 Session이 같은 Right Edge를 수정할 때 발생합니다. 따라서 증가 Key를 사용한다는 사실만으로 Hot Block이라고 단정하지 않습니다.

Sequence 경합과 Index 경합은 다르다

• Sequence CACHE 확대: NEXTVAL을 위한 Sequence 번호 할당 비용과 Sequence 관련 경합을 줄임
• Right-Growing Index 완화: Index Entry가 들어가는 Leaf 위치 자체를 분산해야 함

Sequence Cache를 크게 했는데도 Index Segment의 buffer busy waits나 RAC gc 대기가 남는다면, Sequence가 아니라 Index Right Edge가 병목일 수 있습니다.

4. ITL Wait·Buffer Busy·RAC GC 대기 구분

4.1 ITL Wait

Data·Index Block에는 동시 Transaction을 기록하는 ITL Entry가 필요합니다. 같은 Block에 많은 Transaction이 접근하고 새 ITL Entry를 확보할 공간이 부족하면 ITL waits 또는 enq: TX - allocate ITL entry가 나타날 수 있습니다.

대응은 단순 REBUILD가 아니라 다음을 함께 검토합니다.

• 동시 Session 수와 같은 Block 집중 원인
• INITRANS 설계
• Block 내 Free Space
• Hot Key·Right Edge 분산
• 기존 Block에 대한 변경 효과와 필요 시 재구성 테스트

4.2 Buffer Busy Wait

buffer busy waits는 다른 Session이 Buffer를 Pin하는 등의 이유로 현재 Session이 해당 Buffer를 즉시 Pin하지 못한 증상입니다. Index Object의 값이 높다면 Hot Leaf·Branch Block 가능성을 조사하지만, 원인은 반드시 File·Block·Object 수준에서 확인합니다.

4.3 RAC Global Cache 대기

RAC에서 같은 Current Block을 여러 Instance가 반복 수정하면 gc buffer busy acquire·release, gc current block busy와 같은 대기가 증가할 수 있습니다. 이는 Block의 Instance 간 이동과 원격 Block Busy를 포함하므로 Local buffer busy waits와 구분합니다.

5. Hot Block 완화 선택지

5.1 불필요한 Index 제거가 우선

Index는 Select Access Path를 늘리지만 모든 관련 DML에서 유지비용을 만듭니다.

• 선두 Column과 용도가 사실상 겹치는 중복 Index
• 사용 증거가 부족한 단일 Column Index
• 조회보다 갱신이 훨씬 많은 넓은 Covering Index
• 같은 Constraint를 중복 지원하는 구조

제거 전에는 Constraint 지원, 특수 Report·Batch, Plan Baseline, Invisible Index 시험 범위를 확인합니다.

5.2 Sequence CACHE 확대

ALTER SEQUENCE orders_seq CACHE 1000;

Sequence Cache는 번호 발급 비용을 줄이지만 생성 값이 계속 증가한다면 일반 B-Tree의 Right-Growing 특성은 유지됩니다. 따라서 Sequence 관련 Wait와 Index Segment Wait를 따로 측정합니다.

5.3 Reverse Key Index

CREATE INDEX orders_pk_rev_ix
ON orders(order_id) REVERSE;

Reverse Key Index는 Key Byte 순서를 뒤집어 연속 Key를 서로 떨어진 Leaf 영역에 배치합니다. Rowid는 Reverse 대상에서 제외됩니다.

BETWEEN, >, <, Key 순서 기반 ORDER BY, 최신 구간 조회가 중요하다면 Reverse Key를 신중히 판단합니다.

5.4 Hash-Partitioned Global Index

CREATE INDEX orders_pk_hix
ON orders(order_id)
GLOBAL PARTITION BY HASH(order_id)
PARTITIONS 8;

Monotonic Key의 Right Edge를 여러 Global Index Partition에 분산해 Leaf Block 경합을 줄일 수 있습니다. Equality와 IN Predicate는 Hash Partitioned Global Index를 효율적으로 사용할 수 있습니다.

Trade-off는 다음과 같습니다.

• Range 조회와 Key 순서 활용 요구
• Global Index Partition 관리 복잡도
• Table Partition Maintenance와 Global Index 유지 정책
• Partition 수와 DOP·부하 분산
• Local Index와 다른 장애·복구·운영 범위

5.5 Scalable Sequence

CREATE SEQUENCE orders_seq
  SCALE EXTEND
  CACHE 1000;

Scalable Sequence는 Instance·Session Offset을 번호 앞부분에 붙여 고동시성 Insert의 Sequence·Index Block 경합을 줄이는 방식입니다. Oracle AI Database 26ai에서 새 Scalable Sequence의 Prefix는 Instance Offset 2자리와 Session Offset 3자리로 구성됩니다.

적용 전 다음을 확인합니다.

• 번호가 전역적으로 순차 증가해야 하는가
• Column 자릿수가 충분한가
• SCALE EXTEND·NOEXTEND에 따른 폭 변화
• ORDER 요구가 있는가
• 외부 시스템이 번호 형식이나 정렬 순서에 의존하는가

Scalable Sequence는 전역 순서를 보장하기 위한 기능이 아니므로 ORDER와 함께 사용하지 않는 것이 권고됩니다.

6. DELETE 후 빈 공간과 Segment 크기

대량 DELETE 후 다음 세 가지를 구분해야 합니다.

1. Entry 삭제: 삭제된 Row를 가리키는 Index Entry 제거
2. Leaf 내부 재사용 공간: 같은 Key 영역의 후속 Insert가 사용할 수 있는 공간
3. Segment 할당 크기: Extent가 Tablespace에 반환됐는지 여부

Entry가 많이 삭제돼도 Segment Bytes는 그대로일 수 있습니다. 그러나 해당 Key 영역이 다시 채워진다면 내부 공간 재사용으로 후속 Split이 줄 수 있습니다. 반대로 과거 Key 영역이 장기간 다시 사용되지 않고 넓은 Index Scan의 I/O가 실제로 문제라면 COALESCE·REBUILD·SHRINK 후보를 검토할 수 있습니다.

삭제 비율 하나만으로 REBUILD를 결정하지 않습니다. Key 재사용 패턴, Leaf Blocks, Scan 범위, Buffer Gets, Segment Bytes와 운영 목적을 함께 봅니다.

7. COALESCE와 REBUILD

ALTER INDEX orders_status_date_ix COALESCE;

ALTER INDEX orders_status_date_ix REBUILD ONLINE;

REBUILD는 추가 I/O·CPU·Redo·공간과 운영 리스크가 있습니다. 다음처럼 측정 가능한 이유가 있을 때 수행합니다.

• Tablespace 이동이나 Storage·Compression 속성 변경
• Index가 UNUSABLE이어서 복구 필요
• 장기간 재사용되지 않는 공간과 넓은 Scan 비용이 함께 확인
• BLEVEL·Leaf Blocks·Buffer Gets·응답시간 개선 가설을 재현 가능
• 구조 손상이 검증되어 재생성이 필요

REBUILD 전후에는 같은 SQL·Bind·부하에서 Plan, Buffer Gets, DML 처리량, Redo, Wait를 비교합니다. BLEVEL·LEAF_BLOCKS는 수집 시점의 통계이므로 LAST_ANALYZED와 함께 해석하며 단일 임계값으로 Fragmentation을 판정하지 않습니다.

8. Invisible Index와 Unusable Index

Invisible Index는 조회 Plan에서 Index를 제외했을 때의 영향을 시험하는 데 적합하지만, DML 유지비용은 그대로 남습니다. DML 비용 감소를 시험하려면 별도 테스트 환경에서 Drop 또는 Unusable 전략과 SKIP_UNUSABLE_INDEXES·Constraint 영향을 검증해야 합니다.

9. 실측 방법

9.1 Segment 통계

SELECT owner,
       object_name,
       subobject_name,
       statistic_name,
       value
FROM   v$segment_statistics
WHERE  owner = 'APP'
AND    object_name = 'ORDERS_PK'
AND    statistic_name IN (
         'leaf node splits',
         'buffer busy waits',
         'ITL waits'
       )
ORDER BY statistic_name;

이 값은 누적값이므로 다음과 같이 구간 Delta로 정규화합니다.

Split Rate
= (구간 종료 leaf node splits - 구간 시작 값)
  ÷ 구간 Insert Row 수

Wait per DML
= 구간 대기시간 Delta ÷ 구간 DML Row 수

9.2 Index Dictionary 통계

SELECT index_name,
       status,
       visibility,
       blevel,
       leaf_blocks,
       num_rows,
       distinct_keys,
       last_analyzed
FROM   user_indexes
WHERE  table_name = 'ORDERS';

BLEVEL·LEAF_BLOCKS는 Optimizer 통계이며 수집 시점과 데이터 변화량을 확인해야 합니다. Segment Bytes는 USER_SEGMENTS, Partition은 USER_IND_PARTITIONS에서 별도로 확인합니다.

9.3 Hot Block 확인

ASH 또는 실시간 Session에서 Wait의 File·Block·Object를 확인하고 다음을 구분합니다.

• 한 Index의 동일 Block에 집중되는가
• 여러 Partition에 고르게 분산되는가
• Local buffer busy waits인가 RAC gc 대기인가
• Leaf Split이 아니라 ITL Entry 부족인가
• Sequence Wait와 Index Block Wait 중 무엇이 지배적인가

10. 혼동하기 쉬운 판단

11. 적용·진단 절차

1. 느린 DML의 Statement, 영향 Row 수, 동시 Session 수를 확정합니다.
2. 변경 Column과 관련된 Index를 USER_IND_COLUMNS로 목록화합니다.
3. Sequence Wait, Index buffer busy waits, ITL, RAC gc 대기를 구분합니다.
4. V$SEGMENT_STATISTICS의 구간 Delta와 ASH의 File·Block 집중을 확인합니다.
5. 불필요한 Index 제거와 Sequence Cache 같은 낮은 위험의 개선을 먼저 검토합니다.
6. Equality·Range Query 비중을 확인해 Reverse Key와 Hash-Partitioned Global Index를 비교합니다.
7. Scalable Sequence 사용 시 번호 형식·자릿수·전역 순서 요구를 검증합니다.
8. 삭제 공간은 같은 Key 영역의 재사용 여부와 실제 Scan 비용을 먼저 확인합니다.
9. COALESCE·REBUILD는 구체적인 공간·구조·운영 목적이 있을 때 선택합니다.
10. 변경 전후 같은 부하에서 처리량, Redo, Wait, Buffer Gets와 조회 Plan을 비교합니다.