DML Call 최소화: One-SQL·FORALL·Array Bind·Batch

핵심 요약

대량 DML 튜닝의 첫 질문은 "같은 결과를 한 번의 집합 SQL로 만들 수 있는가"입니다. 가능하면 One-SQL을 우선하고, SQL로 옮기기 어려운 행별 PL/SQL 로직이 남을 때 BULK COLLECT와 FORALL, Client에서 반복 Bind가 필요할 때 표준 JDBC Batch를 검토합니다.

우선순위
One-SQL
→ PL/SQL BULK COLLECT + FORALL
→ Client PreparedStatement + Standard Batch
→ 불가피한 Row-by-Row

중요한 구분은 다음과 같습니다.

• 고정된 Static SQL은 Cursor를 재사용할 수 있으므로 Row마다 Hard Parse가 발생한다고 단정하면 안 됩니다.
• Row-by-Row의 핵심 반복 비용은 SQL Execute, PL/SQL Engine과 SQL Engine 사이 전환, Client·Server Network Round Trip입니다.
• Bulk·Batch는 Call을 줄이지만 Table·Index 변경, Constraint·Trigger, Undo·Redo 같은 Row별 Database 작업 자체를 제거하지는 않습니다.
• Batch Size는 Call·Memory 단위이고, Commit Unit은 업무 원자성·Lock·Undo·가시성·재시작 경계입니다.

학습 목표

1. Parse·Execute·Context Switch·Network Round Trip을 구분한다.
2. One-SQL이 최우선인 이유와 Statement-Level Atomicity를 설명한다.
3. BULK COLLECT ... LIMIT과 FORALL의 역할을 구분한다.
4. SAVE EXCEPTIONS 유무에 따른 Rollback 범위를 설명한다.
5. SQL%BULK_EXCEPTIONS, SQL%BULK_ROWCOUNT, SQL%ROWCOUNT를 구분한다.
6. Sparse Collection에서 INDICES OF와 VALUES OF를 적용한다.
7. 표준 JDBC Batch의 Transaction·오류 처리 규칙을 설명한다.
8. Batch Size와 Commit Unit을 독립적으로 설계한다.
9. 재시작 가능한 Chunk와 Error Logging 전략을 설계한다.
10. Call·Memory·Redo·Commit·Lock을 같은 구간에서 측정한다.

1. Row-by-Row 비용을 정확히 분해하기

1.1 PL/SQL 내부 Row-by-Row

다음 코드는 Source Row마다 같은 INSERT를 실행합니다.

BEGIN
  FOR r IN (
    SELECT id, amount
    FROM   source_order
    ORDER BY id
  ) LOOP
    INSERT INTO target_order(id, amount)
    VALUES (r.id, r.amount);
  END LOOP;
END;
/

Static SQL Cursor가 재사용되더라도 각 Row마다 PL/SQL Engine이 SQL Engine에 DML 실행을 요청합니다.

100,000 Source Rows
→ INSERT Execute 약 100,000회
→ PL/SQL ↔ SQL Engine 전환 반복
→ Table·Index·Undo·Redo 작업 100,000 Row분 수행

따라서 parse count가 100,000이 아니더라도 execute count, Context Switch와 CPU가 병목일 수 있습니다. Parse와 Execute를 같은 비용으로 묶어 설명하지 않습니다.

1.2 Client Row-by-Row

Application이 executeUpdate()를 Row마다 호출하면 Database Call뿐 아니라 Client·Server Round Trip이 반복됩니다.

Client Row 수 N
Batch Size B
예상 Batch 전송 횟수 ≈ CEIL(N / B)

실제 Round Trip은 Driver, Network Protocol, Auto-Commit, 오류 처리와 Statement 설정에 따라 달라질 수 있으므로 Trace와 Client 지표로 확인합니다.

1.3 Bulk가 줄이지 못하는 비용

One-SQL·FORALL·JDBC Batch를 사용해도 다음 작업은 영향 Row 수에 따라 계속 발생합니다.

• Table Row 변경
• 관련 Index Entry 유지
• Constraint와 Trigger 수행
• Undo와 Redo 생성
• Row·Table Lock 유지
• Data Type 변환과 업무 로직

즉 Bulk 처리의 핵심은 같은 Row 작업을 더 적은 Call로 전달하는 것이지 Row별 변경 비용을 0으로 만드는 것이 아닙니다.

2. 1순위: One-SQL

같은 결과를 집합 SQL로 표현할 수 있으면 먼저 One-SQL을 검토합니다.

INSERT INTO target_order(id, amount)
SELECT id, amount
FROM   source_order;

UPDATE target_order t
SET    amount = amount * 1.1
WHERE  EXISTS (
         SELECT 1
         FROM   target_customer c
         WHERE  c.customer_id = t.customer_id
         AND    c.grade = 'VIP'
       );

2.1 One-SQL의 장점

• Optimizer가 전체 집합의 Access Path·Join Order·Join Method를 선택합니다.
• SQL Execute와 Engine 전환을 가장 크게 줄일 수 있습니다.
• Partition Pruning, Direct-Path Insert, Parallel Execution 같은 집합 처리 기능을 활용할 수 있습니다.
• 일반적인 DML 오류에서는 Statement-Level Atomicity를 적용하기 쉽습니다.

Oracle의 Statement-Level Atomicity에서 한 SQL 문장이 실패하면 그 문장이 만든 변경은 Rollback되지만, 같은 Transaction에서 이전에 성공한 다른 문장까지 자동으로 Rollback되는 것은 아닙니다.

2.2 일부 Row 오류를 분리해야 할 때

One-SQL을 유지하면서 허용되는 Row 오류를 별도 Table에 남겨야 한다면 DML Error Logging을 검토할 수 있습니다.

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('TARGET_ORDER');
END;
/

INSERT INTO target_order(id, amount)
SELECT id, amount
FROM   source_order
LOG ERRORS INTO err$_target_order ('LOAD_20260802')
REJECT LIMIT 100;

LOG ERRORS는 모든 오류를 무조건 흡수하지 않습니다. Deferred Constraint 위반이나 일부 Unique 위반 등 Error Logging이 적용되지 않는 조건이 있으며, Reject Limit을 초과하면 DML 문장은 실패합니다.

2.3 One-SQL의 운영 Trade-off

One-SQL은 Call 최소화에 유리하지만 Transaction이 매우 커지면 Undo·Redo·Lock 유지시간과 장애 복구 범위가 커질 수 있습니다. 따라서 성능만이 아니라 업무 원자성, 운영 시간창과 재시작 전략을 함께 설계합니다.

3. PL/SQL Bulk SQL: BULK COLLECT와 FORALL

행별 계산이나 PL/SQL 처리 때문에 One-SQL로 완전히 바꾸기 어렵다면 Bulk SQL을 사용합니다.

DECLARE
  CURSOR c_source IS
    SELECT id, amount
    FROM   source_order
    ORDER BY id;

  TYPE t_id_tab IS TABLE OF source_order.id%TYPE;
  TYPE t_amount_tab IS TABLE OF source_order.amount%TYPE;

  l_ids     t_id_tab;
  l_amounts t_amount_tab;
BEGIN
  OPEN c_source;

  LOOP
    FETCH c_source
    BULK COLLECT INTO l_ids, l_amounts
    LIMIT 1000;

    EXIT WHEN l_ids.COUNT = 0;

    FORALL i IN 1 .. l_ids.COUNT
      INSERT INTO target_order(id, amount)
      VALUES (l_ids(i), l_amounts(i));
  END LOOP;

  CLOSE c_source;
  COMMIT;
END;
/

3.1 BULK COLLECT ... LIMIT

BULK COLLECT는 SQL 결과를 PL/SQL Collection으로 묶어 가져옵니다. LIMIT은 한 번의 FETCH가 Collection에 담는 최대 Row 수를 제한해 Fetch Call과 PGA 사용량을 조절합니다.

LIMIT가 너무 작음
→ Fetch·FORALL 호출 증가

LIMIT가 너무 큼
→ PGA 사용량·한 번의 작업시간·오류 분석 범위 증가

마지막 Fetch는 LIMIT보다 적은 Row를 반환할 수 있습니다. 따라서 Fetch 직후 %NOTFOUND만 보고 종료하여 마지막 부분 Batch를 건너뛰지 않도록, Collection을 처리한 뒤 COUNT=0으로 종료하는 구조를 사용합니다.

3.2 FORALL

FORALL은 일반 Loop가 아니라, 하나의 Static 또는 Dynamic DML 문장을 Collection의 여러 Bind Set으로 실행하는 Bulk Bind 문법입니다.

BULK COLLECT
→ SQL 결과를 Collection으로 묶어 Fetch

FORALL
→ Collection Bind Set을 SQL Engine에 Batch로 전달

FORALL은 INSERT, UPDATE, DELETE, MERGE에 사용할 수 있으며 DML은 적어도 하나의 Collection을 참조해야 합니다.

3.3 Bulk SQL의 제한

• Bulk SQL은 Remote Table에 사용할 수 없습니다.
• Bulk SQL을 사용하는 동안 Parallel DML은 비활성화됩니다.
• Dynamic SQL의 USING 절에서는 collection(i) 같은 단순 Collection 참조를 사용해야 하며 표현식은 제한됩니다.

따라서 FORALL을 적용했다고 Parallel DML까지 동시에 얻는다고 가정하면 안 됩니다.

4. FORALL 오류 처리와 Rollback 범위

4.1 SAVE EXCEPTIONS가 없고 오류를 처리하지 않을 때

SAVE EXCEPTIONS 없이 한 Iteration의 DML이 Unhandled Exception을 발생시키면 FORALL이 중단되고, 그 FORALL에서 앞선 Iteration이 만든 변경도 Rollback됩니다. 이후 Iteration은 실행되지 않습니다.

4.2 오류를 즉시 처리할 때

SAVE EXCEPTIONS 없이 발생한 오류를 Exception Handler가 처리하면 실패한 DML은 Rollback되고 앞서 성공한 Iteration의 변경은 남아 있을 수 있습니다. 그러나 FORALL은 중단되므로 뒤의 Iteration은 수행되지 않습니다. 최종 Commit·Rollback은 Handler의 정책입니다.

4.3 SAVE EXCEPTIONS

DECLARE
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
  FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
    INSERT INTO target_order(id, amount)
    VALUES (l_ids(i), l_amounts(i));

EXCEPTION
  WHEN dml_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'statement=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
        ', code='    || SQL%BULK_EXCEPTIONS(j).ERROR_CODE ||
        ', message=' || SQLERRM(
          -SQL%BULK_EXCEPTIONS(j).ERROR_CODE
        )
      );
    END LOOP;

    -- 업무 정책에 따라 COMMIT 또는 ROLLBACK
    ROLLBACK;
END;
/

SAVE EXCEPTIONS를 사용하면 실패 정보를 저장하고 나머지 Iteration을 계속 수행한 뒤 ORA-24381을 한 번 발생시킵니다.

• ERROR_INDEX: 실패한 DML Statement 번호
• ERROR_CODE: 양수 형태의 Oracle 오류 코드
• 오류 문자열: SQLERRM(-ERROR_CODE)

성공한 Iteration은 자동 Commit되지 않습니다. 성공 Row를 Commit할지 전체 Rollback할지는 업무 원자성과 재처리 정책으로 결정합니다.

5. SQL%BULK_ROWCOUNT와 Sparse Collection

5.1 SQL%BULK_ROWCOUNT

FORALL i IN 1 .. l_ids.COUNT
  UPDATE target_order
  SET    amount = l_amounts(i)
  WHERE  id = l_ids(i);

FOR i IN 1 .. l_ids.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(
    'input=' || i ||
    ', affected=' || SQL%BULK_ROWCOUNT(i)
  );
END LOOP;

• SQL%BULK_ROWCOUNT(i): i번째 DML이 변경한 Row 수
• SQL%ROWCOUNT: 가장 최근 FORALL 전체가 변경한 Row 수의 합계

UPDATE·DELETE의 0 Row 변경은 Database 오류가 아닙니다. 이를 누락·경합·정상 미매칭 중 무엇으로 처리할지는 업무 규칙으로 정합니다.

5.2 INDICES OF

Collection Index가 Sparse할 때 실제 존재하는 Index만 순회합니다.

FORALL i IN INDICES OF l_ids
  DELETE FROM target_order
  WHERE id = l_ids(i);

5.3 VALUES OF

별도의 Index Collection에 저장된 값들을 실제 Collection Subscript로 사용합니다.

FORALL i IN VALUES OF l_selected_indexes
  UPDATE target_order
  SET    status = 'DONE'
  WHERE  id = l_ids(i);

Sparse Collection과 VALUES OF에서는 SQL%BULK_EXCEPTIONS.ERROR_INDEX를 단순히 Dense Collection의 Subscript로 가정하지 말고, 실제 Iteration 순서와 Pointer Collection을 따라 실패 입력을 매핑해야 합니다.

6. 표준 JDBC Batch

Client에서 같은 Prepared SQL을 여러 Bind Set으로 실행해야 한다면 표준 JDBC Batch를 사용합니다.

String sql = "INSERT INTO target_order(id, amount) VALUES (?, ?)";

connection.setAutoCommit(false);

try (PreparedStatement ps = connection.prepareStatement(sql)) {
    for (Order row : rows) {
        ps.setLong(1, row.id());
        ps.setBigDecimal(2, row.amount());
        ps.addBatch();
    }

    int[] counts = ps.executeBatch();
    connection.commit();
} catch (BatchUpdateException e) {
    int[] succeeded = e.getUpdateCounts();
    connection.rollback();
    throw e;
}

6.1 표준 Batch의 핵심

• PreparedStatement를 재사용합니다.
• addBatch()로 Bind Set을 모읍니다.
• executeBatch()로 Batch를 전송합니다.
• 오류 시 BatchUpdateException.getUpdateCounts()로 성공한 선행 작업의 개수를 확인합니다.
• Auto-Commit을 끄면 성공 작업을 Commit할지 전체 Rollback할지 선택할 수 있습니다.

Oracle 고유의 과거 Update Batching API는 Deprecated되었고 최신 Driver에서는 사실상 Batch Size 1로 동작할 수 있으므로 표준 JDBC Batch를 사용합니다.

6.2 Batch가 줄이는 비용과 남는 비용

Driver와 Statement 종류에 따라 실제 Batch 처리 방식과 Update Count가 다를 수 있으므로 Client Metric과 Database Trace를 함께 확인합니다.

7. Batch Size와 Commit Unit을 분리한다

7.1 Batch Size

Batch Size는 한 번에 Fetch·Bind·전송·실행할 Row 수입니다.

• 너무 작으면 Call과 Round Trip이 많습니다.
• 너무 크면 PL/SQL PGA·Application Heap·Payload·한 번의 호출시간이 증가합니다.
• 실패 Row 위치를 찾거나 다시 전송할 범위가 커질 수 있습니다.

7.2 Commit Unit

Commit Unit은 하나의 Transaction으로 확정하는 업무 범위입니다.

• 업무 원자성
• Undo 보존과 Transaction 크기
• Lock 유지시간
• 다른 Session에 대한 가시성
• 장애 시 재시작 위치
• Commit 횟수와 log file sync

Commit Unit을 그대로 둔 채 Batch Size만 변경하면 Transaction 전체의 업무 원자성과 Lock 해제 시점은 바뀌지 않습니다. Batch Size가 커졌다는 이유만으로 총 Undo·Redo가 자동으로 크게 증가한다고 단정하지 않습니다. 총 변경 Row와 Index·Trigger 구조가 같다면 Row별 변경량은 비슷하고, 차이는 Call·Memory·실행 패턴에서 주로 발생합니다.

7.3 예시

10만 Row가 하나의 업무 Transaction이어야 하지만 PGA 때문에 1,000 Row씩 처리할 수 있습니다.

BULK COLLECT LIMIT 1,000
+ FORALL 1,000 Row
× 100회
→ 마지막 COMMIT 1회

반대로 각 1,000 Row가 독립 업무 단위라면 Batch마다 Commit할 수 있습니다. 이 경우 부분 완료를 허용하고 재실행 중복을 막는 설계가 반드시 필요합니다.

8. 재시작 가능한 Batch와 Chunk 처리

8.1 기본 설계 요소

• 업무 Key에 Unique Constraint 적용
• Batch ID·처리 상태·처리 시각 저장
• Key Range·ROWID·Partition 단위 Chunk
• 성공·실패 Row와 Error Code 기록
• 동일 입력 재실행 시 결과가 중복되지 않는 Idempotent 로직
• 완료 Chunk를 건너뛰고 실패 Chunk만 재실행

MERGE 문법 자체가 Idempotency를 자동 보장하는 것은 아닙니다. Match Key, Update 값, 중복 Source Row와 Trigger Side Effect까지 재실행 안전하게 설계해야 합니다.

8.2 DBMS_PARALLEL_EXECUTE

DBMS_PARALLEL_EXECUTE는 Table을 ROWID 또는 Number 범위 Chunk로 나누고 Chunk 상태를 관리하며 실패 Chunk를 재개하는 데 사용할 수 있습니다.

중요한 Transaction 의미는 RUN_TASK가 각 Chunk 처리 후 Commit한다는 점입니다. 따라서 여러 Chunk 전체가 하나의 Transaction이어야 하는 업무에는 맞지 않으며, Chunk 단위 부분 완료와 재시작을 허용할 때 사용합니다.

8.3 오류 수집 방식 선택

각 방식은 자동 Commit 여부와 오류 범위가 다르므로 서로 같은 기능으로 보지 않습니다.

9. Batch Size 실측 방법

9.1 동일 조건 비교

100·500·1,000·5,000 등 후보 크기를 동일 데이터·동일 Plan·동일 Commit Unit에서 비교합니다.

측정값
→ Rows/sec와 경과시간
→ Parse·Execute·User Call Delta
→ SQL*Net Round Trip
→ Redo/Row와 Undo 사용량
→ PGA·Application Heap
→ Commit 수와 log file sync
→ Lock 대기와 실패 재처리 시간

9.2 Database Session 통계 예시

SELECT name, 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 (
         'parse count (total)',
         'execute count',
         'user calls',
         'redo size',
         'SQL*Net roundtrips to/from client'
       );

작업 전후 Delta로 비교합니다. Static SQL Cursor 재사용 때문에 Row-by-Row에서도 Parse Count가 Row 수와 같지 않을 수 있으므로 execute count, user calls, Round Trip을 함께 봅니다.

9.3 SQL Trace 해석

• Parse Count가 줄었는가?
• Execute Count와 Execute당 Row 수가 어떻게 바뀌었는가?
• 한 번의 Execute가 지나치게 오래 걸리는가?
• Commit 횟수와 log file sync가 증가했는가?
• Batch를 키운 뒤 PGA·Client Heap·Timeout이 악화됐는가?

최적 Batch Size는 가장 큰 값이 아니라 처리량·Memory·동시성·오류 복구를 함께 만족하는 값입니다.

혼동하기 쉬운 판단

적용 판단 순서

1. Row-by-Row SQL의 Parse·Execute·Round Trip을 구분해 측정합니다.
2. 같은 결과를 One-SQL로 표현할 수 있는지 먼저 검토합니다.
3. 일부 오류를 허용해야 하면 One-SQL의 LOG ERRORS 적용 가능성을 확인합니다.
4. PL/SQL 행별 로직이 남으면 BULK COLLECT ... LIMIT과 FORALL을 적용합니다.
5. SAVE EXCEPTIONS와 SQL%BULK_ROWCOUNT의 업무 의미를 정의합니다.
6. Sparse Collection이면 INDICES OF·VALUES OF와 오류 Index 매핑을 검증합니다.
7. Client에서는 PreparedStatement와 표준 JDBC Batch를 사용하고 Auto-Commit 정책을 명시합니다.
8. Batch Size와 Commit Unit을 독립적으로 결정합니다.
9. Chunk·Unique Key·상태 Table로 재시작과 중복 방지를 설계합니다.
10. 동일 Commit Unit에서 여러 Batch Size를 실측해 최종값을 선택합니다.