대량 DML 비용 구조: 탐색·Index·Undo·Redo·Lock·Commit

핵심 요약

대량 INSERT·UPDATE·DELETE·MERGE의 비용은 Table Row를 바꾸는 작업 하나로 설명되지 않습니다. 실제 경로는 다음과 같이 여러 계층으로 이어집니다.

대상 행 탐색
→ Table Block 접근과 Row 변경
→ 관련 Index Entry 유지
→ Constraint·Trigger·Recursive SQL 처리
→ Undo Block 변경
→ Data·Index·Undo Block 변경에 대한 Redo 생성
→ TX Row Lock·TM Table Lock·ITL 사용
→ COMMIT 시 Transaction Redo Flush

따라서 대량 DML은 다음 세 질문으로 나누어 분석해야 합니다.

1. 얼마나 읽었는가? — 대상 행을 찾기 위해 읽은 Row·Block과 Access Path
2. 무엇을 함께 바꾸었는가? — Table, 영향받는 Index, Constraint, Trigger, Materialized View Log 등
3. Transaction을 어떻게 끝냈는가? — Undo·Redo, Lock 보유시간, Commit 빈도, Rollback·재시작 정책

실행계획이 낮은 Cost를 보여도 Index 유지·Trigger 내부 SQL·Undo·Redo·Commit 비용은 별도로 클 수 있습니다. 반대로 변경 행 수가 적어도 대상 탐색에서 대량 Scan이 발생하면 읽기 단계가 병목이 될 수 있습니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. 대량 DML의 탐색 비용과 변경 비용을 분리한다.
2. INSERT·UPDATE·DELETE의 Table·Index 유지 차이를 설명한다.
3. 영향받는 Index 수를 이용해 논리적 변경 작업량을 추정한다.
4. Unique·Primary Key·Foreign Key와 Trigger가 DML 비용에 미치는 영향을 설명한다.
5. Undo의 Rollback·읽기 일관성·Transaction Recovery 역할을 구분한다.
6. Redo가 Data·Index뿐 아니라 Undo Block 변경도 보호한다는 점을 설명한다.
7. COMMIT이 Data Block이 아니라 Transaction Redo의 내구성을 먼저 보장한다는 점을 설명한다.
8. TX Row Lock, TM Table Lock, Unique Key 대기, ITL 경합을 구분한다.
9. Batch Size와 Commit Unit을 구분하고 Transaction 크기의 Trade-off를 판단한다.
10. 실행계획, V$SESSTAT, V$TRANSACTION, Wait Event와 SQL Trace로 실제 비용을 측정한다.

1. 대량 DML 비용을 세 단계로 나누기

1.1 대상 탐색 단계

UPDATE orders
SET    status = 'EXPIRED'
WHERE  order_date < ADD_MONTHS(TRUNC(SYSDATE), -12)
AND    status = 'OPEN';

이 SQL이 10만 행을 변경하더라도 대상 행을 찾기 위해 1억 행과 대량 Block을 읽었다면 병목은 탐색 단계입니다. 다음을 확인합니다.

• Table Full Scan인지 Index Access인지
• Predicate가 Access 조건인지 Filter 조건인지
• E-Rows와 A-Rows 차이가 큰지
• 실제 Buffers·Reads·A-Time이 어느 Row Source에서 증가했는지
• Partition Pruning이 적용됐는지
• 불필요한 Function·묵시적 형변환으로 Index Access가 막혔는지

1.2 변경 단계

대상 Row를 찾은 뒤에는 다음 작업이 이어질 수 있습니다.

• Table Row 변경
• Index Entry 추가·삭제
• Constraint 검사
• Row Trigger 실행
• Materialized View Log·Audit 등 부가 객체 변경
• Undo와 Redo 생성

1.3 Transaction 종료 단계

변경이 끝나도 Transaction은 COMMIT 또는 ROLLBACK 전까지 완료되지 않습니다.

• 변경 Row의 TX Lock 유지
• Table의 TM Lock 유지
• Active Undo 사용
• 실패 시 Rollback 비용 누적
• Commit 시 LGWR를 통한 Redo Flush
• Commit 이후 다른 Session에 변경 결과 공개

총 DML 경과시간
≈ 탐색 시간
 + Table·Index·Constraint·Trigger 변경 시간
 + Lock 대기와 동시성 지연
 + Commit Redo Flush 시간

2. DML 유형별 기본 변경 경로

2.1 INSERT

INSERT는 새 Row를 저장하고 Table의 모든 관련 Index에 Entry를 추가합니다.

• Table Row 저장
• 모든 Index Entry 추가
• NOT NULL·CHECK·Unique·Primary Key·Foreign Key 검사
• Statement Trigger와 Row Trigger 실행
• Undo·Redo 생성
• Row·Table Lock과 ITL 사용

Table에 읽기용 Index가 많을수록 조회 성능은 좋아질 수 있지만 Insert 비용은 커집니다. Oracle은 Table Row가 삽입되거나 삭제될 때 Table의 모든 Index를 자동으로 유지합니다.

2.2 DELETE

DELETE는 대상 Row 탐색 후 Table Row와 모든 관련 Index Entry를 변경합니다.

• 대상 Row 탐색
• Table Row 삭제 처리
• 모든 Index Entry 삭제 처리
• 자식 Foreign Key·ON DELETE 동작 확인
• Trigger·Recursive SQL 실행
• Undo·Redo 생성
• Transaction 종료까지 Row Lock 유지

Delete 대상이 적어도 자식 Foreign Key가 인덱스되지 않았다면 부모 Key 삭제 검사가 별도 병목과 Lock 문제를 만들 수 있습니다.

2.3 UPDATE

Update 비용은 변경 Column이 Index에 포함되는지에 따라 크게 달라집니다.

비인덱스 Column만 UPDATE
→ Table Row 변경 중심
→ 해당 Column을 포함하지 않는 Index는 Key Entry를 바꾸지 않음

Index Column UPDATE
→ 기존 Index Entry 제거
→ 새 Index Entry 추가
→ 다른 Leaf Block 접근·Block Split·Hot Block 경쟁 가능

Composite Index의 Column 하나라도 바뀌면 해당 Index Key가 달라질 수 있으므로 그 Index는 유지 대상입니다. Row 길이가 증가하고 원래 Block에 공간이 부족하면 Row Migration 가능성도 확인합니다.

3. Index 유지비용 계산

Index는 Query Access를 빠르게 하지만 Base Table 변경 시 자동으로 유지됩니다.

대상 행 수를 N, Table의 전체 Index 수를 K_all, 변경 Column을 포함한 Index 수를 K_changed라고 하면 첫 번째 추정은 다음과 같습니다.

INSERT의 Index Entry 추가량
≈ N × K_all

DELETE의 Index Entry 삭제량
≈ N × K_all

Index Key UPDATE의 논리적 Entry 변경량
≈ 2 × N × K_changed
  (기존 Entry 삭제 + 새 Entry 추가)

이 식은 물리 I/O나 Redo Byte를 정확히 계산하는 공식이 아니라 비교를 위한 논리적 출발점입니다. 실제 비용은 다음에 따라 달라집니다.

• Index Key 폭과 Number of Columns
• BLEVEL·Leaf Block 분포
• 증가 Key의 Right-Hand Hot Block
• Unique 검사와 동시 미확정 Key
• Bitmap Index의 넓은 Lock 영향과 DML 동시성
• Local·Global Partitioned Index
• Cluster Factor가 아니라 DML 시 접근해야 하는 Leaf 위치와 Block 경합
• Buffer Cache Hit, Block Split, Redo·Undo 크기

계산 예시

20만 행의 Index Key를 Update하고 변경 Column이 B-Tree Index 2개에 포함된다면, 단순 논리적 Entry 변경량은 다음과 같습니다.

200,000행 × 2개 Index × 2회(기존 삭제 + 신규 추가)
= 최대 약 800,000회 논리적 Index Entry 변경

실제 Block 변경 횟수와 Redo Byte는 Index 구조와 Cache·동시성에 따라 달라지므로 Session 통계로 확인해야 합니다.

4. Constraint와 Trigger 비용

4.1 Unique·Primary Key

INSERT와 Key UPDATE에서는 중복 여부를 검사합니다. 다른 Transaction이 같은 Unique Key를 먼저 입력했지만 아직 Commit하지 않았다면 결과가 확정될 때까지 대기할 수 있습니다.

4.2 Foreign Key

• 자식 INSERT·FK UPDATE: 부모 Key 존재 확인
• 부모 DELETE·PK UPDATE: 참조하는 자식 Row 존재 확인
• ON DELETE CASCADE·SET NULL: 자식 Row 변경이 추가로 발생

자식 Foreign Key Column에 적절한 Index가 없으면 부모 Key 삭제·변경 시 다음 문제가 커질 수 있습니다.

• 자식 Table Full Scan으로 참조 Row 확인
• 부모 Key 변경 중 Child Table에 더 강한 Table Lock 발생 가능
• 동시 Child DML의 대기 범위 확대

Foreign Key Index는 모든 경우에 무조건 필요한 것이 아니라, 부모 Key를 삭제·수정하지 않고 Child 규모도 작다면 효과가 제한될 수 있습니다. 그러나 부모 변경과 Child DML이 병행되는 시스템에서는 Index가 Full Scan과 Child Table Lock을 방지하는 중요한 동시성 장치가 됩니다.

4.3 Trigger

Statement Trigger는 Statement당 한 번 실행되지만, Row Trigger는 영향을 받는 Row마다 실행됩니다.

1,000,000행 UPDATE
× Row Trigger 내부 SQL 2개
→ 최대 2,000,000회의 추가 SQL 실행 가능성

실제 비용은 Trigger 내부 SQL, PL/SQL Function, Sequence, Audit Table 변경, External Call에 따라 달라집니다. Compound Trigger나 Set 기반 처리로 Row별 반복을 줄일 수 있는지 검토하되, 업무 규칙과 실행 순서를 먼저 보존해야 합니다.

5. Undo의 역할과 대량 DML 영향

Undo는 단순한 Rollback 공간이 아닙니다.

대량 DML에서는 다음을 확인합니다.

• Active Transaction의 Undo Block·Record 사용량
• Undo Tablespace 확장 가능성
• Undo Retention과 장기 Query 길이
• V$UNDOSTAT의 UNDOBLKS·TXNCOUNT·MAXQUERYLEN·SSOLDERRCNT
• V$TRANSACTION.USED_UBLK·USED_UREC

COMMIT했다고 Undo가 즉시 사라지는 것은 아닙니다. Commit된 Undo도 Read Consistency와 Flashback을 위해 보존될 수 있으며, 공간이 부족하면 오래된 Undo가 재사용됩니다. 대량 DML이 Undo를 빠르게 소비하고 장기 Query가 오래된 Before Image를 요구하면 ORA-01555 위험이 커질 수 있습니다.

Commit을 자주 하는 것만으로 ORA-01555가 해결되는 것은 아닙니다. 오히려 Cursor를 열어 둔 채 반복 Fetch·Update·Commit을 수행하면 필요한 Undo가 재사용될 가능성이 커질 수 있으므로 Query 길이, Undo 공간, Retention, 처리 방식과 함께 판단해야 합니다.

6. Redo와 Commit의 정확한 의미

Redo는 장애 복구를 위해 Database Block의 변경을 다시 적용할 수 있도록 기록합니다. Redo에는 Table·Index Block뿐 아니라 Undo Segment Block과 Transaction Table 변경도 포함됩니다. 이 때문에 Redo Roll Forward 후 Undo를 이용해 미Commit 변경을 취소할 수 있습니다.

6.1 COMMIT 시 실제로 보장되는 것

Foreground Session이 COMMIT 요청
→ LGWR가 Transaction Redo를 Online Redo Log에 기록
→ Redo가 안전하게 기록되면 Commit 완료 통지

COMMIT 시점에 변경된 Table·Index Data Block 전체가 Datafile에 기록될 필요는 없습니다. Data Block은 DBWR가 별도 시점에 기록할 수 있으며, Commit 내구성은 먼저 Transaction Redo의 안전한 기록으로 보장합니다.

6.2 대표 대기

동시에 Commit하는 여러 Session의 Redo를 LGWR가 함께 처리할 수 있으므로, 단순 평균 Wait만이 아니라 다음을 함께 확인합니다.

• 초당 Transaction 수
• user commits
• redo size
• redo writes·redo write time
• Commit당 Redo Byte
• Storage Latency와 동기 Standby 전송

7. Lock·TM Lock·ITL 경합 구분

7.1 TX Row Lock

Oracle은 INSERT·UPDATE·DELETE·MERGE·SELECT FOR UPDATE가 수정하거나 잠근 각 Row에 Row Lock을 사용합니다. Row Lock은 Transaction이 COMMIT 또는 ROLLBACK할 때까지 유지됩니다.

enq: TX - row lock contention
→ 다른 Transaction이 보유한 동일 Row의 Lock 해제를 기다림

대량 Transaction이 많은 Row를 변경해도 이를 하나의 Exclusive Table Lock으로 자동 승격시키는 방식으로 이해하면 안 됩니다. Row Lock 정보는 Data Block의 ITL과 Transaction ID를 통해 관리됩니다.

7.2 TM Table Lock

DML은 Table에도 TM Lock을 획득합니다. 일반적인 Row Exclusive(TM RX/SX) Lock은 다른 Session의 서로 다른 Row DML을 허용하지만, 현재 Transaction과 충돌하는 DDL을 방지합니다.

enq: TM - contention
→ DDL 충돌, Foreign Key Lock 문제 등 Table 단위 Enqueue 원인 확인

7.3 ITL 경합

ITL(Interested Transaction List)은 Block Header에서 해당 Block을 변경 중인 Transaction을 연결하는 Slot입니다.

enq: TX - allocate ITL entry
→ 같은 Block에 동시 Transaction이 집중
→ 새 ITL Slot을 만들 Free Space가 부족

대응은 단순히 Commit을 늘리는 것보다 다음을 함께 검토합니다.

• Hot Block과 동시 Update 패턴
• INITRANS
• Block Free Space와 PCTFREE
• 증가 Key Index의 Hot Leaf Block
• Partitioning·Hash 분산 가능성

8. Commit 빈도와 Transaction 크기

Commit은 단순한 성능 Option이 아니라 업무 원자성과 가시성의 경계입니다.

8.1 지나치게 잦은 Commit

• log file sync 반복
• Commit Record와 통신·호출 횟수 증가
• 부분 완료 상태가 다른 Session에 노출
• 실패 시 재시작·보상·중복 처리 복잡성 증가
• 한 업무가 여러 Transaction으로 분리되어 원자성 약화

8.2 지나치게 큰 Transaction

• Active Undo와 Redo 증가
• Row Lock·TM Lock 보유시간 증가
• 실패 시 Rollback 시간 증가
• 장애 복구·Standby 전송 부담 증가
• 다른 업무의 대기시간과 Batch Window 위험 증가

Batch Size
→ 한 번에 조회·전달·처리하는 Row 수

Commit Unit
→ 함께 성공하거나 함께 실패해야 하는 원자적 업무 범위

두 값은 반드시 같지 않습니다. 예를 들어 1,000행씩 Fetch하더라도 업무상 10만 행이 하나의 원자 단위라면 100 Batch를 처리한 후 한 번 Commit할 수 있습니다. 반대로 재시작 Key와 멱등성이 보장된다면 Partition·업무 Key 단위로 Commit Unit을 분리할 수 있습니다.

8.3 Commit 단위 결정 순서

1. 함께 성공·실패해야 하는 업무 범위를 정의한다.
2. 부분 Commit이 허용되는지 확인한다.
3. 재시작 Key·멱등성·중복 방지 방식을 설계한다.
4. Undo·Redo·Lock·Rollback 시간의 상한을 정한다.
5. 운영 동시성과 Batch Window를 실측한다.

9. 실행계획만으로 전체 비용을 알 수 없는 이유

실행계획은 대상 Row를 찾는 Row Source와 Join·Access 경로를 분석하는 데 중요하지만, 다음 전체 비용을 직접 합산해 보여 주지는 않습니다.

• 변경되는 Index 수와 Index Entry 유지량
• Trigger 내부 Recursive SQL
• Constraint 검사와 동시 미확정 Key 대기
• Undo·Redo Byte
• Row Lock·ITL·Foreign Key Lock 대기
• Commit 횟수와 LGWR Flush
• Rollback·재시작 비용

따라서 DBMS_XPLAN.DISPLAY_CURSOR의 Runtime 통계와 Session·Transaction 통계를 함께 사용합니다.

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +ALIAS +IOSTATS'
         )
       );

10. 실측 도구와 진단 SQL

10.1 Session 통계의 구간 Delta

DML 직전과 직후 값을 각각 저장하고 차이를 계산합니다.

SELECT n.name,
       s.value
FROM   v$sesstat s
JOIN   v$statname n
  ON   n.statistic# = s.statistic#
WHERE  s.sid = SYS_CONTEXT('USERENV', 'SID')
AND    n.name IN (
         'session logical reads',
         'physical reads',
         'db block changes',
         'redo size',
         'undo change vector size',
         'user commits',
         'user rollbacks'
       );

10.2 Active Transaction의 Undo 사용량

SELECT s.sid,
       t.xidusn,
       t.xidslot,
       t.xidsqn,
       t.used_ublk,
       t.used_urec,
       t.start_date
FROM   v$transaction t
JOIN   v$session s
  ON   s.taddr = t.addr
WHERE  s.sid = SYS_CONTEXT('USERENV', 'SID');

USED_UBLK는 사용한 Undo Block 수, USED_UREC는 Undo Record 수입니다. Transaction 종료 전 증가 추세를 보면 Rollback 부담과 Undo 용량을 추정하는 데 도움이 됩니다.

10.3 대기와 Segment 경합

• enq: TX - row lock contention
• enq: TX - allocate ITL entry
• enq: TM - contention
• log file sync
• log file parallel write
• buffer busy waits

V$SESSION, ASH·AWR, V$SEGMENT_STATISTICS를 이용해 Blocking Session, Object, Hot Block과 Commit 지연을 연결합니다.

10.4 SQL Trace

SQL Trace와 TKPROF에서는 다음을 확인합니다.

• Main DML의 Execute 횟수와 Row Count
• Trigger·Constraint 관련 Recursive SQL
• Parse·Execute·Fetch Call 수
• CPU·Elapsed·Disk·Query·Current Mode Gets
• Row-by-row 처리와 Database Call 수

11. 대량 DML 튜닝 적용 순서

11.1 업무 규칙 먼저 확정

• 원자성 범위
• 허용 완료시간
• 부분 완료 허용 여부
• 재시작·Rollback·보상 처리
• Standby·Backup·복구 정책

11.2 탐색 비용 최소화

• Sargable Predicate와 정확한 Data Type 사용
• 불필요한 Function·묵시적 변환 제거
• 적절한 Index·Partition Pruning 검토
• 여러 번의 Row-by-row SQL을 Set 기반 One-SQL로 통합

11.3 변경 부대비용 정리

• 변경 Column을 포함한 Index 목록화
• 사용되지 않는 중복·저효용 Index를 근거 기반으로 정리
• Unique·PK·FK Index와 업무 무결성 Index는 무작정 제거하지 않음
• Row Trigger·Recursive SQL을 Trace로 검증
• Materialized View Log·Audit·Replication 부가 변경 확인

11.4 Transaction 크기 조정

• Batch Size와 Commit Unit을 분리
• Undo·Redo·Lock·Rollback 시간을 기준으로 상한 설정
• 업무 Key·Partition·Date Range 단위의 재시작 설계
• 잦은 Commit으로 원자성을 훼손하지 않도록 검증

11.5 구조적 대안 비교

업무가 허용한다면 다음 후보를 비교합니다.

• Set 기반 MERGE·UPDATE·DELETE
• Staging Table과 Partition Exchange
• Direct-Path Insert
• Partition 단위 병렬 처리
• Error Logging과 재처리 Queue

각 대안은 Logging, Constraint, Trigger, Index, Lock, Space, Backup·Standby 영향을 함께 검증해야 합니다.

혼동하기 쉬운 판단

핵심 정리

대량 DML 비용
= 탐색 비용
+ Table 변경
+ Index 유지
+ Constraint·Trigger
+ Undo·Redo
+ TX·TM Lock·ITL
+ Commit Flush

Undo
→ Rollback·Read Consistency·Transaction Recovery

Redo
→ Data·Index·Undo Block 변경의 복구 기록

COMMIT
→ Datafile Flush가 아니라 Transaction Redo의 내구성 보장

진단
→ 실행계획 + Session Delta + V$TRANSACTION + Wait + SQL Trace

튜닝
→ 업무 원자성 정의
→ 불필요한 읽기 제거
→ 영향 Index·Trigger 축소
→ Commit Unit·재시작 설계
→ 운영 동시성으로 검증