ITL과 Block Cleanout: Block 내부 Transaction 추적과 동시 DML

핵심 요약

Oracle은 변경된 모든 Row Lock을 중앙 Memory 목록에 개별 등록하지 않는다. 대신 Row가 저장된 Data Block의 Row Header와 ITL(Interested Transaction List)을 이용해 어떤 Transaction이 Row를 변경했는지 추적한다.

변경된 Row
→ Row Header의 Lock 정보
→ 같은 Block Header의 ITL Slot
→ Transaction ID
→ Undo Segment의 Transaction Table
→ Active·Commit·Rollback 상태와 Commit SCN 확인

한 Transaction이 같은 Block의 여러 Row를 변경하면 여러 Row가 하나의 ITL Entry에 연결될 수 있다. 반대로 여러 Transaction이 같은 Block의 서로 다른 Row를 동시에 변경하려면 Transaction별 ITL Slot이 필요하다. Block에 Slot을 추가할 Free Space가 부족하면 enq: TX - allocate ITL entry 대기가 발생할 수 있다.

Commit은 Transaction을 완료하고 Lock을 해제하지만, 모든 변경 Block의 ITL 관련 표시가 반드시 Commit 순간에 완전히 정리되는 것은 아니다. Commit 시점의 Commit Cleanout과 후속 접근 시점의 Delayed Block Cleanout을 구분해야 한다.

학습 목표

• Row Header, ITL Slot, Transaction ID, Undo Transaction Table의 연결 관계를 설명한다.
• 같은 Row Lock 경합과 같은 Block의 ITL 부족을 구분한다.
• INITRANS, Block Free Space, PCTFREE가 동시 DML에 미치는 영향을 설명한다.
• enq: TX - allocate ITL entry, V$LOCK.REQUEST=4, ITL waits를 연결해 진단한다.
• Commit Cleanout과 Delayed Block Cleanout의 조건과 비용을 설명한다.
• Block Cleanout과 Consistent Read를 구분하고 Query SCN과 Commit SCN을 연결한다.
• 설정 변경 후 동시성·공간·Redo 영향을 함께 검증한다.

1. Oracle이 Row Lock을 Block에 저장하는 이유

Oracle은 Lock Manager가 모든 Row Lock을 중앙 목록으로 관리하는 방식 대신, Row가 들어 있는 Data Block에 Lock 정보를 저장한다. Row를 변경한 Transaction은 Block Header의 ITL에 Entry를 가지며, 해당 Transaction이 변경한 Row들은 그 ITL Entry와 연결된다.

같은 Block에서 Transaction T1이 Row 100개 변경
→ Row Lock은 100개
→ Row들이 참조하는 Transaction ID는 T1
→ 같은 Block에서는 하나의 ITL Entry로 연결 가능

따라서 다음 값들은 서로 같은 의미가 아니다.

Transaction이 변경한 Row 수
≠ Block에서 사용한 ITL Entry 수
≠ V$LOCK에서 관찰되는 TX Resource 수

TX Enqueue는 Transaction Resource 수준의 동기화를 보여 주며, 개별 Row가 어느 Transaction에 의해 변경됐는지는 Block 내부 정보로 추적된다.

1.1 Block 내부 연결 구조

개념적으로 다음 순서로 상태를 확인한다.

Row Header의 Lock Byte
→ ITL Slot 번호
→ ITL Entry의 Transaction ID(XID)
→ Undo Segment Header의 Transaction Table
→ Active 여부 또는 Commit SCN
→ 필요하면 Undo Chain

Row Header의 Lock 정보는 같은 Block의 ITL Entry를 가리킨다. ITL Entry는 Transaction ID를 포함하고, Undo Segment의 Transaction Table을 통해 Transaction 상태와 변경 시점을 판단하는 데 사용된다.

2. ITL Slot의 역할

ITL은 Table Data Block과 Index Block Header에 존재하는 Transaction 추적 영역이다. 하나의 ITL Entry는 해당 Block을 변경하는 하나의 Transaction과 연결된다.

ITL Entry는 개념적으로 다음 정보를 추적하는 데 필요한 연결점을 제공한다.

• Transaction ID
• Undo Record를 찾기 위한 연결 정보
• Transaction이 Active인지 종료됐는지 판단할 정보
• Commit된 경우 Commit SCN을 확인할 정보
• Block 안에서 해당 Transaction이 변경한 Row와의 연결

ITL은 단순한 “Row Lock 개수 표”가 아니다. 같은 Transaction이 같은 Block의 여러 Row를 변경해도 하나의 ITL Entry를 공유할 수 있다.

2.1 Table Block과 Index Block 모두 대상이다

ITL 부족은 Table Block에만 발생하지 않는다. 여러 Session이 같은 Index Leaf Block을 동시에 변경하면 Index Block에서도 Transaction Entry가 필요하다. 특히 증가하는 Key가 특정 Rightmost Leaf Block으로 몰리는 구조에서는 Row가 서로 달라도 동일 Index Block에 DML이 집중될 수 있다.

SEQUENCE 기반 증가 Key INSERT
→ 동일한 Rightmost Index Leaf에 집중
→ 여러 Transaction이 같은 Leaf Block 변경
→ ITL 또는 Buffer 경합 가능

따라서 ITL waits가 Index Segment에서 증가하면 Table의 INITRANS만 변경해서는 해결되지 않는다.

3. Row Lock 경합과 ITL 부족의 차이

다음 두 상황을 구분해야 한다.

3.1 같은 Row Lock 경합

Session A: 주문 1001 변경 후 미종료
Session B: 주문 1001 변경 시도

Session B는 같은 Row를 변경하려 하므로 선행 Transaction 종료를 기다린다. 대표 Event는 다음과 같다.

enq: TX - row lock contention

3.2 같은 Block의 ITL 부족

같은 Data Block
├─ 주문 1001: Session A 변경
├─ 주문 1002: Session B 변경
├─ 주문 1003: Session C 변경
└─ 주문 1004: Session D 변경 시도

Row는 서로 다르지만 네 Transaction이 같은 Block을 변경하려면 각각 사용할 ITL Slot이 필요하다. 기존 Slot이 모두 Active Transaction에 연결돼 있고 새 Slot을 만들 Free Space도 없으면 후행 Session이 기다린다.

대표 Event는 다음과 같다.

enq: TX - allocate ITL entry

Oracle 공식 진단 기준에서는 ITL을 기다리는 TX Enqueue가 Mode 4로 관찰될 수 있다. V$LOCK에서 TYPE='TX'이고 REQUEST=4인 Session은 ITL Slot 대기 후보로 볼 수 있지만, Event와 Segment 증거를 함께 확인해야 한다.

SELECT sid,
       type,
       id1,
       id2,
       lmode,
       request
FROM   v$lock
WHERE  type = 'TX'
AND    request > 0;

4. INITRANS, 동적 ITL 확장과 Block Free Space

INITRANS는 새 Block이 할당될 때 처음부터 확보할 Concurrent Transaction Entry 수를 지정한다.

CREATE TABLE order_item (
    order_id   NUMBER,
    item_no    NUMBER,
    quantity   NUMBER
)
INITRANS 8
PCTFREE 20;

4.1 INITRANS의 의미

• Table의 기본값은 일반적으로 1이다.
• Index의 기본값은 일반적으로 2이다.
• 값의 범위는 1~255이다.
• 각 Transaction이 Block을 Update하려면 Transaction Entry가 필요하다.
• 초기 Slot보다 많은 Transaction이 접근해도 Block에 Free Space가 있으면 Slot을 동적으로 추가할 수 있다.

INITRANS는 “최대 동시 Transaction 수”가 아니라 초기 보장 Slot 수다.

4.2 MAXTRANS의 현재 의미

과거 MAXTRANS는 Block별 동시 Transaction Entry의 상한을 지정했지만 현재는 Deprecated다. Oracle은 Block의 사용 가능한 공간에 따라 최대 255개의 Concurrent Transaction Entry를 자동으로 허용한다. 새 MAXTRANS 값을 지정해도 Oracle이 255로 대체할 수 있으므로, 현대 진단에서 핵심은 INITRANS와 Block Free Space다.

4.3 PCTFREE와의 관계

PCTFREE는 ITL 전용 공간 설정이 아니다. 값을 높이면 Row Update와 ITL 확장 여유가 커질 수 있지만, Block당 저장 Row 수가 줄고 Segment 크기와 I/O가 증가할 수 있다.

4.4 기존 Block에 대한 주의

ALTER TABLE ... INITRANS n 또는 ALTER INDEX ... INITRANS n을 수행했다고 해서 이미 가득 찬 모든 기존 Block에 즉시 n개의 Slot이 물리적으로 사전 확보되는 것으로 해석하면 안 된다. 공식 예시에서도 변경된 Index의 INITRANS는 Future Data Block의 초기 Entry 수에 적용된다고 설명한다.

기존 Block이 이미 조밀하고 Free Space가 부족하면 다음 대안을 검토한다.

• Segment 또는 Index Rebuild로 Block 재구성
• PCTFREE와 Row 밀도 조정
• Partitioning 또는 Key 분산
• Hot Block을 만드는 Application 접근 패턴 변경

5. Commit과 Transaction 상태 기록

Transaction이 Commit되면 Oracle은 다음 핵심 작업을 수행한다.

Commit SCN 생성
→ Undo Segment Transaction Table에 Commit 상태와 SCN 기록
→ 남은 Redo를 Online Redo Log에 기록
→ Row·Table Lock 해제
→ Commit Cleanout 시도
→ Transaction 완료

Commit은 변경한 Row 수나 Block 수 전체를 모두 동기적으로 다시 방문해야만 끝나는 구조가 아니다. 그래서 대량 변경 Transaction도 Commit 자체는 일반적으로 빠르게 완료될 수 있다.

6. Commit Cleanout

Commit 시점에 Transaction이 변경한 Block이 Buffer Cache에 남아 있고 다른 Session이 해당 Block을 변경 중이지 않으면 Oracle은 Lock 관련 Transaction 정보를 정리하는 Commit Cleanout을 수행할 수 있다.

Transaction T1 COMMIT
├─ Commit SCN 기록
├─ Lock 해제
└─ 접근 가능한 변경 Block의 ITL 관련 정보 Cleanout 시도

Oracle은 V$SYSSTAT에서 Commit Cleanout 관련 통계를 제공한다.

SELECT name, value
FROM   v$sysstat
WHERE  name LIKE 'commit cleanout%'
ORDER BY name;

대표 통계는 다음과 같다.

• commit cleanouts
• commit cleanouts successfully completed
• commit cleanout failures: cannot pin
• commit cleanout failures: buffer being written
• 기타 Commit Cleanout 실패 원인 통계

이 통계는 Instance 누적값이므로 단일 시점 숫자보다 문제 구간의 Delta로 해석한다.

7. Delayed Block Cleanout

Commit 시점에 Cleanout되지 않은 Block은 이후 다른 Session이 읽거나 변경할 때 Transaction 상태를 확인하고 정리될 수 있다. 이것이 Delayed Block Cleanout이다.

Session A: 여러 Block 변경 후 COMMIT
→ Transaction은 이미 Commit 완료
→ 일부 Block의 ITL 표시가 남음
→ Session B가 Block 접근
→ Undo Segment Transaction Table에서 Commit 상태·SCN 확인
→ Block Cleanout 수행

Delayed Cleanout이 보인다고 해서 선행 Transaction이 아직 미완료라는 뜻은 아니다. Transaction은 Commit됐지만 Block 내부의 Transaction 표시가 최신 상태로 정리되지 않은 것이다.

7.1 조회가 Redo를 만들 수 있는 이유

Oracle 공식 개념 문서는 Block Cleanout이 Redo를 생성할 수 있으므로, Query가 Redo를 만들고 Block을 Dirty 상태로 만들어 다음 Checkpoint에서 기록될 수 있다고 설명한다.

따라서 “SELECT는 절대로 Redo를 만들지 않는다”는 문장은 항상 참이 아니다. 일반적인 조회 데이터 접근은 DML처럼 변경 Redo를 만들지 않지만, Delayed Block Cleanout 같은 내부 Block 정리로 Redo가 발생할 수 있다.

7.2 Cleanout 관련 Consistent Get 통계

다음과 같은 V$SYSSTAT 통계를 통해 Consistent Read 과정의 Cleanout 발생을 관찰할 수 있다.

• cleanouts only - consistent read gets
• cleanouts and rollbacks - consistent read gets
• consistent changes

단, 이러한 통계도 누적값이고 내부 구현에 따라 이름이나 제공 범위가 변할 수 있으므로 V$STATNAME에서 현재 Release의 통계를 확인한다.

8. Block Cleanout과 Consistent Read의 차이

Block을 읽을 때는 다음 두 질문을 분리한다.

1. 해당 Transaction은 Commit됐는가?
2. 그 Commit SCN은 내 Query SCN 이전인가?

Transaction이 Commit됐더라도 Query SCN 이후에 Commit됐다면 현재 Query에는 그 변경이 보이면 안 된다. 이 경우 Undo를 적용해 Query 시점의 과거 Block 버전을 재구성한다.

Block 접근
→ ITL로 Transaction 상태 확인
→ Commit SCN 확인
→ Query SCN과 비교
├─ Query SCN 이전 Commit: 변경 버전 사용 가능
└─ Query SCN 이후 Commit 또는 미Commit: Undo 적용해 CR Block 재구성

Cleanout과 CR 재구성은 연결돼 있지만 같은 작업은 아니다.

9. ITL 문제 진단 절차

9.1 1단계: 현재 대기 확인

SELECT inst_id,
       sid,
       serial#,
       event,
       state,
       wait_time_micro,
       sql_id,
       blocking_instance,
       blocking_session,
       row_wait_obj#
FROM   gv$session
WHERE  event LIKE 'enq: TX%';

Event 이름은 Release와 내부 구현에 따라 달라질 수 있으므로 GV$SESSION.EVENT와 V$EVENT_NAME에서 실제 값을 확인한다.

9.2 2단계: TX Mode와 Request 확인

SELECT inst_id,
       sid,
       type,
       id1,
       id2,
       lmode,
       request,
       block
FROM   gv$lock
WHERE  type = 'TX'
AND    request > 0;

REQUEST=4는 ITL Slot 대기의 강한 후보 증거지만, Unique Constraint 관련 TX Mode 4 등 다른 상황과 혼동하지 않도록 Event 이름과 Segment 통계를 결합한다.

9.3 3단계: Segment별 누적 ITL Wait 확인

SELECT owner,
       object_name,
       subobject_name,
       object_type,
       value AS itl_waits
FROM   v$segment_statistics
WHERE  statistic_name = 'ITL waits'
AND    value > 0
ORDER BY value DESC;

ITL waits는 Instance가 시작된 이후 누적값이다. 과거에 한 번 발생한 Segment도 남아 있으므로 다음처럼 Delta를 계산한다.

점검 시작값 저장
→ 문제 부하 실행
→ 점검 종료값 저장
→ 종료값 - 시작값 비교

9.4 4단계: Table인지 Index인지 확인

• Table Segment: 같은 Data Block의 여러 Row에 동시 Update 집중 여부
• Index Segment: Right-Growing Leaf, Hot Key, 특정 Partition 집중 여부
• Partitioned Object: SUBOBJECT_NAME으로 Partition 단위 집중 확인

9.5 5단계: Block 집중 원인 확인

• 같은 Row를 반복 갱신하는 Hot Row
• 연속 Key Insert로 특정 Index Leaf에 집중
• 상태값·업무 Key에 의해 특정 Block 또는 Partition에 집중
• 지나치게 긴 Transaction으로 ITL Slot이 오래 Active 상태 유지
• Commit 주기와 Batch 동시 실행 시간대 중첩

10. 개선 방법과 Trade-off

10.1 INITRANS 조정

실제 Block당 동시 변경 Transaction 수를 기준으로 최소 Slot을 확보한다.

ALTER TABLE order_item INITRANS 8;
ALTER INDEX order_item_pk INITRANS 16;

Table과 Index 중 실제 ITL waits가 발생한 Segment를 구분해 적용한다.

10.2 PCTFREE와 Segment 재구성

기존 Block이 조밀해 ITL 확장 공간이 부족하다면 PCTFREE 변경만으로 기존 Block이 즉시 재배치되지 않을 수 있다. 필요하면 Table Move, Online Redefinition, Index Rebuild 등으로 새 Block 구조를 만들어야 한다.

10.3 Hot Block 분산

• Hash Partitioning 또는 업무 기준 Partitioning
• Reverse Key Index의 적용 가능성 검토
• Key 생성 방식 분산
• 동시 Batch 시작 시간 분산
• 긴 Transaction 축소와 Commit 주기 재설계

Reverse Key Index는 Range Scan을 제한하는 Trade-off가 있으므로 증가 Key Hot Block 문제라는 증거가 있을 때만 검토한다.

10.4 무조건 큰 INITRANS를 피하는 이유

큰 INITRANS는 Block Header 공간을 더 사용한다. 너무 크게 설정하면 Row 저장 공간이 줄어 Block 수와 I/O가 늘어날 수 있다. 해결 기준은 “값을 크게”가 아니라 “필요한 동시 Transaction Entry를 확보하면서 공간 손실을 통제”하는 것이다.

11. 실전 진단 시나리오

시나리오 A: 서로 다른 Row인데 TX 대기가 발생

증상
- Event: enq: TX - allocate ITL entry
- V$LOCK.REQUEST = 4
- 대상 Table Segment의 ITL waits Delta 증가
- 같은 Block 범위에 여러 Session의 DML 집중

판단: 같은 Row Lock보다 ITL 부족 가능성이 높다.

조치: 동시 Transaction 수, INITRANS, Block Free Space, PCTFREE, 기존 Block 재구성 필요성을 검토한다.

시나리오 B: Index Segment의 ITL waits 증가

증상
- Table보다 PK Index Segment의 ITL waits 증가
- 증가 Key INSERT가 동시 수행
- Rightmost Leaf에 DML 집중

판단: Table INITRANS만 변경하면 해결되지 않는다.

조치: Index INITRANS, Key 분산, Partitioning, Reverse Key 적용 가능성과 Range Scan 요구를 함께 평가한다.

시나리오 C: 대량 DML 후 첫 조회에서 Redo와 지연 증가

증상
- 대량 DML과 COMMIT 이후 첫 대량 조회
- Cleanout 관련 통계 Delta 증가
- 조회 구간에서 Redo 증가 가능

판단: Delayed Block Cleanout이 일부 비용에 기여했을 가능성이 있다.

조치: AWR·ASH·Session 통계, Redo Delta, Cleanout 통계를 같은 시간축으로 확인한다. Cleanout 하나만으로 전체 지연 원인을 단정하지 않는다.

12. 잘못된 판단과 바로잡기

13. 최종 적용 체크리스트

현상 확인

• 현재 Event가 enq: TX - allocate ITL entry인지 확인했다.
• 같은 Row Lock Event와 구분했다.
• GV$LOCK.REQUEST=4와 Event를 함께 확인했다.
• RAC에서는 INST_ID·SID·SERIAL#를 함께 식별했다.

대상 확인

• V$SEGMENT_STATISTICS의 ITL waits를 Table·Index·Partition별로 확인했다.
• 단일 누적값이 아니라 문제 구간 Delta를 계산했다.
• Hot Row, Hot Block, Right-Growing Index 여부를 확인했다.

개선 설계

• 실제 동시 Transaction 수를 기준으로 INITRANS를 정했다.
• Block Free Space와 PCTFREE의 공간 Trade-off를 검토했다.
• 기존 Block 재구성 필요성을 검토했다.
• Table과 Index 중 실제 문제 Segment에 변경을 적용했다.
• Key·Partition·Batch 시간 분산 가능성을 검토했다.

사후 검증

• 같은 부하에서 ITL Wait Delta와 TX 대기시간이 감소했는지 확인했다.
• 처리량과 응답시간이 개선됐는지 확인했다.
• Segment 크기, Logical I/O, Buffer 경합이 악화되지 않았는지 확인했다.
• Cleanout·Redo 통계를 같은 시간 구간으로 비교했다.

15. 공식 검증 기준

• 한국데이터산업진흥원 데이터자격시험 SQL 전문가 시험주요내용
• Oracle AI Database 26ai Concepts: Data Concurrency and Consistency, Transactions
• Oracle AI Database 26ai SQL Language Reference: Automatic Locks in DML Operations, physical_attributes_clause
• Oracle AI Database 26ai Performance Tuning Guide: Instance Tuning Using Performance Views
• Oracle AI Database 26ai Reference: Oracle Wait Events, Statistics Descriptions, V$SEGSTAT
• Oracle AI Database 26ai Data Guard Administration: Troubleshooting ITL Pressure