Sequence와 Right-Growing Index 경합: CACHE·RAC·Reverse·Scalable

핵심 요약

증가하는 Sequence 값을 Primary Key로 사용한 INSERT가 느릴 때는 Sequence 값 생성과 Index Entry 삽입을 서로 다른 병목으로 분리해야 합니다.

Sequence 할당 경합
→ NEXTVAL을 생성하고 Sequence 상태를 관리하는 구간
→ CACHE·ORDER·Sequence 종류가 핵심

Right-Growing Index 경합
→ 단조 증가 Key가 B-Tree의 오른쪽 Leaf에 집중되는 구간
→ Reverse·Hash Partition·Scalable Key와 Index 설계가 핵심

CACHE를 늘려 NEXTVAL이 빨라져도 생성된 Key가 계속 증가하면 일반 B-Tree의 Rightmost Leaf 집중은 남습니다. 반대로 Reverse Key Index를 적용해 Leaf 삽입을 분산해도 Ordered Sequence 자체의 전역 순서 조정은 사라지지 않습니다.

Oracle RAC에서는 같은 Index Block의 Current 버전을 여러 Instance가 반복해서 요구하면 Cache Fusion 전송과 Global Cache 대기가 더해질 수 있습니다. 따라서 Wait Event 하나만 보고 결론을 내리지 말고 SQL_ID → Sequence·Index Object → File·Block → Segment Statistic → 전후 Delta 순서로 증거를 연결합니다.

학습 목표

• Sequence 할당 경합과 Right-Growing Index 경합을 구분한다.
• CACHE, NOORDER, ORDER의 성능·정합성 범위를 설명한다.
• 증가 Key가 B-Tree Rightmost Leaf에 집중되는 이유를 설명한다.
• Single Instance와 RAC의 Buffer·Global Cache 경합을 연결한다.
• Reverse Key Index·Hash-Partitioned Global Index·Scalable Sequence의 Trade-off를 비교한다.
• Wait Event와 Object·Block·Segment Statistic으로 실제 병목을 검증한다.

1. 증가 Key INSERT에는 두 동기화 지점이 있다

다음 Table과 Sequence를 가정합니다.

CREATE TABLE orders (
    order_id  NUMBER       NOT NULL,
    payload   VARCHAR2(200),
    CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

CREATE SEQUENCE order_id_seq
  CACHE 1000
  NOORDER
  NOCYCLE;

INSERT INTO orders(order_id, payload)
VALUES (order_id_seq.NEXTVAL, :payload);

한 건의 INSERT에는 최소한 다음 두 단계가 있습니다.

1. ORDER_ID_SEQ.NEXTVAL 획득
2. ORDERS_PK B-Tree에 ORDER_ID Entry 삽입

두 단계는 같은 현상이 아닙니다.

NEXTVAL 호출만 매우 빠르게 끝나더라도 PK Index 삽입에서 병목이 발생할 수 있습니다. 따라서 Sequence 설정만 보고 원인을 확정하지 않습니다.

2. Sequence CACHE와 ORDER를 정확히 해석한다

2.1 CACHE의 역할

CACHE n은 여러 Sequence 값을 Memory에 미리 확보해 NEXTVAL 접근 비용을 줄입니다.

ALTER SEQUENCE order_id_seq
  CACHE 10000
  NOORDER;

Oracle AI Database 26ai에서 CACHE의 최소 지정값은 2이며, CACHE와 NOCACHE를 모두 생략하면 기본 20개를 Cache합니다. Oracle은 RAC에서 Sequence 성능을 위해 CACHE 사용을 권장합니다.

충분한 CACHE
→ Sequence 상태를 다시 확보하는 빈도 감소
→ NEXTVAL 처리량 개선 가능

하지만
→ 생성 Key의 증가 방향은 그대로
→ Rightmost Leaf 집중은 별도 문제

System Failure가 발생하면 아직 사용하지 않은 Cached Value가 손실될 수 있으므로 큰 CACHE는 더 큰 번호 Gap을 만들 수 있습니다. Gap은 Sequence 성능 특성이지, 그 자체로 중복이나 데이터 오염을 의미하지 않습니다.

2.2 LAST_NUMBER는 마지막 사용값이 아니다

SELECT sequence_name,
       increment_by,
       cache_size,
       order_flag,
       last_number,
       scale_flag,
       extend_flag
FROM   user_sequences
WHERE  sequence_name = 'ORDER_ID_SEQ';

Cache를 사용하면 LAST_NUMBER는 마지막으로 사용한 번호가 아니라 Disk에 기록된 값이며, Cache에 마지막으로 배치된 값일 수 있습니다. 최근 업무 번호나 다음 실제 발급값으로 해석하지 않습니다.

2.3 ORDER와 NOORDER

ORDER는 Sequence 번호가 NEXTVAL 요청 순서대로 생성되도록 보장합니다. NOORDER는 이 전역 요청 순서를 보장하지 않으며 기본값입니다.

NEXTVAL 요청 순서
≠ Transaction 시작 순서
≠ 업무 완료 순서
≠ Commit 순서

Ordered Sequence 접근에서 관찰할 수 있는 대표 대기는 enq: SV - contention입니다. Primary Key처럼 유일성만 필요하다면 불필요한 ORDER를 제거하고 NOORDER를 우선 검토합니다.

enq: SV - contention 증가
→ Ordered Sequence Object 접근 여부 확인
→ ID1의 Sequence Object와 SQL_ID 연결
→ ORDER가 실제 업무 불변조건인지 재검토

3. Right-Growing B-Tree Index의 원리

일반 B-Tree Index는 Key 순서에 따라 Leaf Entry를 저장합니다. Sequence나 Timestamp처럼 계속 증가하는 값은 대부분 오른쪽 끝 Leaf에 들어갑니다.

100001 → Rightmost Leaf
100002 → Rightmost Leaf
100003 → Rightmost Leaf
100004 → Rightmost Leaf

동시 INSERT가 많으면 한 Block 또는 인접 Block에 다음 작업이 집중될 수 있습니다.

• Index Entry를 삽입하기 위한 Current Get
• Buffer Pin·변경 경합
• ITL Slot 사용
• Leaf Block Split과 Split 완료 대기
• Redo·Undo 생성
• RAC Instance 사이 Current Block 전송

enq: TX - index contention은 Transaction이 Index에 Row를 삽입하면서 다른 Transaction이 수행 중인 Index Block Split의 종료를 기다릴 때 발생합니다. RAC에서는 일부 Index Split 대기가 index split completion으로 관찰될 수 있습니다.

따라서 다음 판단은 잘못입니다.

증가 Key INSERT가 느리다
→ Sequence가 느리다
→ CACHE만 키우면 해결된다

정확한 판단은 다음과 같습니다.

NEXTVAL 단계가 느린가?
→ Sequence 설정·Ordered Sequence 대기 검증

Index Entry 삽입 단계가 느린가?
→ 대상 Index·File·Block·Split·Buffer·GC 대기 검증

4. Single Instance와 RAC에서 나타나는 증거

4.1 Single Instance

같은 Leaf Block을 여러 Process가 동시에 변경하면 다음 증상이 나타날 수 있습니다.

• buffer busy waits
• Index Segment의 buffer busy waits 증가
• ITL waits
• leaf node splits
• enq: TX - index contention

buffer busy waits는 다른 Session이 Buffer를 Pin하고 있어 현재 Session이 즉시 Pin하지 못하는 증상입니다. 이 Event만으로 Index Hot Block을 확정하지 말고 File·Block과 Object를 연결해야 합니다.

4.2 RAC

RAC에서는 Block의 Current 버전을 한 Instance만 소유할 수 있으므로 변경 주체가 Instance 사이를 오가면 Global Cache 비용이 추가될 수 있습니다.

• gc current block busy
• gc buffer busy acquire
• gc buffer busy release
• 동일 Index Object·File·Block에 집중된 ASH Sample

gc current block busy는 요청 Instance가 Current Block을 즉시 받지 못했음을 뜻하고, gc buffer busy는 진행 중인 Global Operation 때문에 Local Buffer 접근이 지연되는 증상입니다. Network 지연만이 아니라 같은 Block을 반복 변경하는 작업 부하가 근본 원인일 수 있습니다.

5. Object·Block·Segment Statistic으로 원인을 좁힌다

현재 Session과 ASH에서 다음 값을 함께 확인합니다.

SELECT sid,
       serial#,
       sql_id,
       event,
       current_obj#,
       row_wait_file#,
       row_wait_block#
FROM   v$session
WHERE  wait_class <> 'Idle';

CURRENT_OBJ#, File·Block은 Event 종류와 상태에 따라 해석 조건이 다르므로 단독으로 확정 근거로 사용하지 않습니다. SQL Plan과 Object Dictionary, ASH의 반복 Sample을 함께 봅니다.

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

Segment Statistic은 누적값입니다. 장애 시점의 원인을 판단할 때는 같은 부하 구간의 시작·종료 Snapshot으로 Delta를 계산합니다.

진단 순서
1. 느린 INSERT SQL_ID와 Plan 확인
2. NEXTVAL 단독 호출과 전체 INSERT 시간 분리
3. Sequence Object·ORDER·CACHE·SCALE 설정 확인
4. PK Index Object·File·Block 집중도 확인
5. Wait Event와 Segment Statistic Delta 연결
6. RAC이면 Instance별 Block 이동과 Service 배치 확인

6. Reverse Key Index

Reverse Key Index는 각 Index Key의 Byte 순서를 뒤집어 저장하되 Column 순서는 유지하는 B-Tree Index입니다.

ALTER INDEX orders_pk REBUILD REVERSE;

증가 Key가 원래는 인접한 오른쪽 Leaf에 들어가지만, Byte가 뒤집히면 Index 전체의 서로 다른 Leaf로 분산될 수 있습니다.

일반 B-Tree
100001, 100002, 100003 → 인접 Rightmost Leaf

Reverse Key
뒤집힌 Key가 서로 먼 위치로 분산

장점

• 증가 Key INSERT의 Rightmost Leaf 집중 완화
• RAC에서 동일 Leaf Block의 반복 이동 감소 가능
• 완전 일치 조건의 등치 검색은 B-Tree 접근 가능

Trade-off

• 원래 Column 값 순서가 Index Leaf 순서로 유지되지 않음
• >, <, BETWEEN 같은 일반 Range Scan 활용이 제한됨
• ORDER BY order_id의 정렬 제거를 위해 원래 Key 순서를 이용하기 어려움
• Bitmap Index와 Index-Organized Table에는 Reverse를 사용할 수 없음
• Partition·Subpartition 단위에 REVERSE·NOREVERSE를 지정할 수 없음

다음 SQL이 핵심 경로라면 신중해야 합니다.

SELECT *
FROM   orders
WHERE  order_id BETWEEN :from_id AND :to_id
ORDER BY order_id;

Reverse Key 적용 전에는 실제 조회 SQL의 Plan과 Buffer를 반드시 비교합니다.

7. Hash-Partitioned Global Index

Global Index를 Hash Partitioning하면 단조 증가 Key의 오른쪽 끝 삽입을 여러 Hash Partition으로 분산할 수 있습니다.

-- 기존 PK Constraint와 Index를 계획된 절차로 제거·재구성한 뒤 적용하는 대안
CREATE UNIQUE INDEX orders_pk_hg
ON orders(order_id)
GLOBAL PARTITION BY HASH(order_id)
PARTITIONS 16;

ALTER TABLE orders
  ADD CONSTRAINT orders_pk
  PRIMARY KEY (order_id)
  USING INDEX orders_pk_hg;

일반 Global Index
→ 하나의 Right Edge로 Insert 집중

Hash-Partitioned Global Index
→ Hash 결과에 따라 N개 Index Partition의 Right Edge로 분산

Oracle 공식 문서는 이 방식이 Multiuser OLTP에서 소수 Leaf Block의 높은 경합을 완화하고, 단조 증가 Column의 Index Skew 영향을 줄일 수 있다고 설명합니다. Partitioning Key에 대한 등치와 IN Predicate는 효율적으로 사용할 수 있습니다.

장점

• Key 표현 자체를 뒤집지 않고 Insert 지점을 여러 Index Partition으로 분산
• 단조 증가 Key의 Right Edge 경합 완화
• 등치·IN 중심 접근과 결합하기 쉬움

Trade-off

• Index Partition 수, Tablespace, 통계와 유지관리 복잡도 증가
• 전체 Key 순서가 하나의 Leaf Chain으로 유지되지 않음
• Range·정렬 SQL은 여러 Partition 접근과 추가 정렬 가능성을 검증해야 함
• Table Partition DDL이 Global Index의 사용 가능 상태와 유지비용에 영향을 줄 수 있음

Hash Partition 수는 많을수록 무조건 좋은 것이 아닙니다. 실제 동시성, Partition별 크기, 관리 비용과 Query Plan을 기준으로 결정합니다.

8. Scalable Sequence

Scalable Sequence는 높은 동시성의 순서가 필요 없는 Primary·Unique Key를 생성할 때 Sequence와 Index Block 경합을 함께 줄이기 위한 기능입니다. Single Instance와 RAC 모두 이점을 얻을 수 있습니다.

CREATE SEQUENCE order_id_seq
  SCALE
  EXTEND
  CACHE 1000
  NOORDER;

Oracle AI Database 26ai의 Scalable Sequence는 일반 Sequence 부분 앞에 5자리 Prefix를 추가합니다.

2자리 Instance Offset
+
3자리 Session Offset
+
Sequence 부분

(instance_id MOD 100) || (session_id MOD 1000) || sequence

Release 23부터 새로 생성한 Scalable Sequence는 과거 형식에 있던 Instance Offset의 선행 1이 제거되었습니다. 업그레이드 환경에서는 기존 Sequence와 신규 Sequence의 값 외형이 다를 수 있으므로 Interface와 Column 폭을 검증합니다.

SCALE·EXTEND

• SCALE은 Instance·Session Offset을 붙여 동시 생성값이 한 증가 구간에만 모이지 않게 합니다.
• EXTEND는 Offset을 포함한 값이 기존 MAXVALUE 폭보다 넓어질 수 있도록 합니다.
• NOEXTEND는 기존 최대 폭 안에서 값을 만들므로 Prefix와 Sequence 부분이 그 폭 안에 들어가는지 사전에 검증해야 합니다.
• Oracle은 SCALE과 ORDER를 동시에 사용하지 않을 것을 강하게 권고합니다.

장점

• 큰 CACHE만 사용한 일반 Sequence보다 Sequence와 Index Block 경합을 함께 줄일 수 있음
• Reverse Key Index로 바꾸지 않고 생성 Key 자체를 분산
• Single Instance와 RAC의 고동시성 적재에 적합

Trade-off

• 값이 단순한 1, 2, 3 형태가 아니며 자릿수가 늘어날 수 있음
• 숫자 크기 순서가 업무 발생·Commit 순서를 나타내지 않음
• 외부 Interface, Column Precision, 로그·화면 형식 점검 필요
• ID Range가 업무 시간 구간을 의미한다는 기존 가정이 깨질 수 있음

Scalable Sequence는 실제 Hotspot 증거가 있을 때 적용하고, 단순히 숫자 모양이 마음에 들지 않는다는 이유로 제거하지 않습니다.

9. Random Key와 자연 분산 Partition의 주의점

9.1 Random UUID

Random UUID는 Rightmost Leaf 집중을 피할 수 있지만 다음 비용이 생길 수 있습니다.

• Sequence NUMBER보다 넓은 Key와 더 큰 Index
• Index 전체의 Random Insert와 Cache Locality 저하 가능성
• Range·정렬 의미 상실
• Table과 Secondary Index에 전파되는 넓은 Foreign Key 비용

따라서 “분산되므로 항상 빠르다”는 결론은 성립하지 않습니다.

9.2 Table·Local Index Partitioning

Tenant, Region, 업무일자처럼 자연스럽게 분산되는 Key가 있으면 Table과 Local Index Partitioning을 검토할 수 있습니다. 하지만 모든 신규 Row가 같은 최신 Partition으로 들어가면 Partitioning만으로 Hot Leaf가 사라지지 않을 수 있습니다.

Partition 수가 많다
≠ 실제 Insert가 여러 Partition으로 분산된다

실제 Insert Key 분포와 Service·Instance 배치를 함께 확인합니다.

10. 대안 선택표

두 병목이 동시에 존재하면 조합이 필요할 수 있습니다.

작은 CACHE + ORDER + Right-Growing Index
→ CACHE 확대·NOORDER로 Sequence 경합 완화
→ Reverse·Hash·SCALE 중 조회 요구에 맞는 방식으로 Index 경합 완화

11. 적용과 회귀 검증 절차

11.1 변경 전 Baseline

• Insert TPS, 평균·P95·P99 응답시간
• enq: SV - contention과 Sequence Object
• buffer busy waits, gc current block busy, gc buffer busy 시간
• enq: TX - index contention, index split completion
• Index Segment의 leaf node splits, ITL waits, buffer busy waits Delta
• Index 크기, Redo, CPU, Interconnect 전송량
• 주요 등치·Range·ORDER BY SQL의 Plan과 Buffer

11.2 한 번에 원인 하나씩 검증

1. CACHE·ORDER만 변경
→ NEXTVAL과 전체 INSERT의 변화 비교

2. Index 분산 방식만 변경
→ Hot Block·Split·GC 대기와 조회 회귀 비교

3. Scalable Sequence 적용
→ 값 폭·Interface·조회 의미와 처리량 동시 검증

여러 변경을 한꺼번에 적용하면 어떤 변경이 효과를 냈는지 판단하기 어렵습니다.

11.3 성공 기준

Insert TPS만 높아졌다고 성공으로 판단하지 않습니다.

• P95·P99 지연과 Timeout 감소
• Hot Object·Block 집중 완화
• Range·정렬·등치 조회 Plan 유지 또는 허용 범위
• Index 크기·Redo·CPU·Interconnect 비용 허용
• 장애·Rollback 시 번호 Gap 정책 충족
• 외부 Interface와 Column Precision 이상 없음

혼동하기 쉬운 판단

핵심 판단 순서

NEXTVAL이 느린가, Index 삽입이 느린가?
→ Ordered Sequence와 CACHE가 필요한가?
→ 특정 Index Object·File·Block으로 Wait가 집중되는가?
→ 등치와 Range·Order 중 어느 조회가 중요한가?
→ Reverse·Hash·SCALE의 값·조회·운영 Trade-off를 감당할 수 있는가?
→ 변경 후 Insert와 조회를 같은 부하로 회귀 검증했는가?