Database Call 최소화: One-SQL·Array DML·Bulk Binding·JDBC Batch

핵심 요약

Database Call 최소화의 핵심은 한 번의 호출이 더 많은 행을 처리하도록 업무를 집합화하고, 각 처리 경계에서 반복되는 고정비를 줄이는 것입니다.

1순위: One-SQL
→ 한 SQL이 전체 대상 집합을 처리
→ Application↔Database Call과 SQL Execute 횟수를 가장 크게 줄임

2순위: PL/SQL Bulk SQL
→ BULK COLLECT로 여러 행을 Collection에 Fetch
→ FORALL로 Collection Bind를 한 번에 SQL Engine에 전달
→ PL/SQL Engine↔SQL Engine Context Switch 감소

3순위: Standard JDBC Batch·Client Array Bind
→ 동일 PreparedStatement와 다른 Bind 값들을 묶어 전송·실행
→ Network Round Trip과 반복 Execute 요청 감소

피해야 할 기본 구조: Row-by-Row
→ 행마다 Parse·Execute·Round Trip·Context Switch가 반복될 수 있음

Call 수를 줄여도 Table·Index 변경, Constraint·Trigger 검사, Undo·Redo, Lock·ITL, Commit 비용은 남습니다. 따라서 개선 효과는 호출량과 실제 DML 작업량을 분리해 측정해야 합니다.

학습 목표

• User Call, Oracle Net Round Trip, Execute Call, PL/SQL Context Switch의 차이를 설명한다.
• Row-by-Row와 One-SQL의 호출 구조를 비교한다.
• BULK COLLECT, FORALL, Client Array Bind의 방향과 역할을 구분한다.
• FORALL의 Collection·Dynamic SQL·원격 Table·Parallel DML 제한을 설명한다.
• SAVE EXCEPTIONS, SQL%BULK_EXCEPTIONS, SQL%BULK_ROWCOUNT를 이용해 부분 실패를 처리한다.
• Oracle 고유 Update Batching과 Standard JDBC Batching의 현재 지원 상태를 구분한다.
• JDBC Batch의 addBatch·executeBatch·commit·rollback·clearBatch 관계를 설명한다.
• Batch Size와 Commit Unit을 서로 다른 기준으로 설계한다.
• Call 감소와 Buffer Gets·Redo·Undo·Lock 작업량 감소를 구분해 검증한다.

1. Database Call은 여러 경계에서 발생한다

애플리케이션의 Row-by-Row 처리는 하나의 비용만 반복하는 것이 아닙니다.

Application
↕ Oracle Net Round Trip·User Call
Database Server Process
↕ Parse·Execute·Fetch Call
SQL Engine
↕ Context Switch
PL/SQL Engine

user calls는 Login·Parse·Fetch·Execute 같은 Client 요청을 포함하는 Session 통계입니다. Oracle Net Round Trip은 Client와 Server 사이 메시지 왕복량을 보여 줍니다. 두 값은 관련되지만 동일한 통계는 아닙니다.

2. Row-by-Row 처리에서 남는 비용

다음 코드는 PreparedStatement를 한 번 만들므로 SQL Text 재생성과 Hard Parse를 줄일 수 있습니다. 그러나 executeUpdate()는 행 수만큼 호출됩니다.

String sql =
    "UPDATE orders " +
    "SET status = ?, updated_at = SYSTIMESTAMP " +
    "WHERE order_id = ?";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (OrderChange item : changes) {
        ps.setString(1, item.status());
        ps.setLong(2, item.orderId());
        ps.executeUpdate();
    }
}

대상 행 수 = 100,000
PreparedStatement 생성 = 1회
Execute 요청 ≈ 100,000회
Network Round Trip = Driver·Protocol·Auto-Commit 설정에 따라 매우 많을 수 있음

따라서 다음 문장은 틀립니다.

PreparedStatement를 사용했다
= Row-by-Row 비용이 모두 사라졌다

PreparedStatement는 Parse·Cursor 재사용에 유리하지만, 행마다 실행하면 Execute·Round Trip·Lock 획득·오류 처리의 반복은 남습니다.

3. 가장 먼저 One-SQL을 검토한다

업무 규칙을 SQL 집합 연산으로 표현할 수 있다면 Application 또는 PL/SQL Loop보다 One-SQL을 우선합니다.

UPDATE orders
SET    status     = 'EXPIRED',
       updated_at = SYSTIMESTAMP
WHERE  status     = 'READY'
AND    expire_at  < SYSTIMESTAMP;

Source 집합이 Staging Table에 있다면 MERGE를 검토할 수 있습니다.

MERGE INTO orders t
USING order_change_stg s
   ON (t.order_id = s.order_id)
WHEN MATCHED THEN
  UPDATE SET
    t.status     = s.new_status,
    t.updated_at = SYSTIMESTAMP;

3.1 One-SQL의 이점

• Parse·Execute·Network Call 최소화
• Optimizer가 전체 Row Set을 보고 Join과 Access Path 선택
• PL/SQL·Application Loop와 중간 데이터 전달 제거
• 하나의 SQL_ID와 실행계획으로 관찰하기 쉬움

3.2 One-SQL에서도 반드시 검증할 내용

• Source Key가 Target Grain에서 유일한가
• 대상 Row를 찾는 Access Path가 적절한가
• 불필요한 Full Scan·Join·Sort가 발생하지 않는가
• 변경되는 Index 수와 유지비용은 얼마인가
• Trigger·Constraint·Foreign Key 검사가 병목이 아닌가
• Undo·Redo·Lock 보유시간과 Rollback 시간이 허용 가능한가
• 업무 전체를 하나의 Transaction으로 처리해야 하는가

One-SQL은 Call 최소화 관점에서 우선순위가 높지만, 잘못된 Access Path로 과도한 Block을 읽거나 장시간 Lock을 유지하면 전체 성능은 나쁠 수 있습니다.

4. Bulk SQL과 Bulk Binding의 정확한 의미

Oracle PL/SQL의 Bulk SQL은 PL/SQL Engine과 SQL Engine 사이의 통신 오버헤드를 줄입니다.

BULK COLLECT
→ SQL Engine에서 여러 Query Row를 PL/SQL Collection으로 한 번에 반환

FORALL
→ PL/SQL Collection의 Bind 값을 SQL Engine에 Batch로 전달
→ 하나의 DML 문을 서로 다른 Collection 값으로 반복 실행

Oracle 공식 기준으로 Query 또는 DML이 4개 이상의 Row에 영향을 줄 때 Bulk SQL이 유의미한 성능 향상을 줄 수 있습니다. 이는 절대 임계값이 아니라 Row-by-Row 전환 비용이 눈에 띄기 시작하는 실무 지침입니다.

4.1 중요한 제한

• Bulk SQL은 Remote Table에 사용할 수 없습니다.
• Bulk SQL을 사용하면 Parallel DML은 비활성화됩니다.
• FORALL은 Client Program 문법이 아니라 Server-Side PL/SQL 문법입니다.
• Client는 Host Array를 PL/SQL Anonymous Block에 Bulk Bind할 수 있습니다.

따라서 Parallel DML이 핵심인 대용량 작업을 FORALL로 바꾸기 전에 전체 처리 전략을 비교해야 합니다.

5. BULK COLLECT와 LIMIT

업무 로직 때문에 PL/SQL 단계가 필요할 때 Cursor Fetch를 묶습니다.

DECLARE
    CURSOR c_target IS
        SELECT order_id
        FROM   orders
        WHERE  status = 'READY';

    TYPE t_order_id IS TABLE OF orders.order_id%TYPE;
    l_order_ids t_order_id;
BEGIN
    OPEN c_target;

    LOOP
        FETCH c_target
        BULK COLLECT INTO l_order_ids
        LIMIT 1000;

        EXIT WHEN l_order_ids.COUNT = 0;

        FORALL i IN 1 .. l_order_ids.COUNT
            UPDATE orders
            SET    status     = 'PROCESSED',
                   updated_at = SYSTIMESTAMP
            WHERE  order_id   = l_order_ids(i);
    END LOOP;

    CLOSE c_target;
    COMMIT;
END;
/

BULK COLLECT가 0건을 반환해도 NO_DATA_FOUND가 발생하지 않습니다. Collection이 비었는지 확인해야 합니다.

5.1 LIMIT의 Trade-off

적정 LIMIT은 Row 폭, 동시 Session 수, PGA, DML 시간, 오류 처리, 재시작 단위를 실제 부하로 측정해 정합니다.

6. FORALL의 문법과 Collection 제한

FORALL은 하나의 INSERT, UPDATE, DELETE, MERGE를 Collection 값으로 여러 번 실행합니다.

FORALL i IN 1 .. l_order_ids.COUNT
    UPDATE orders
    SET    status = 'PROCESSED'
    WHERE  order_id = l_order_ids(i);

6.1 연속·희소 Collection

1 .. COUNT는 해당 범위의 Collection Element가 모두 존재할 때 사용합니다. Element가 삭제돼 Index가 비연속이면 다음 절을 검토합니다.

FORALL i IN INDICES OF l_order_ids
    DELETE FROM orders
    WHERE  order_id = l_order_ids(i);

VALUES OF는 별도의 PLS_INTEGER Index Collection이 가리키는 순서와 Element를 사용합니다.

6.2 Dynamic SQL 제한

Dynamic SQL의 USING 절에서 FORALL Index로 참조하는 값은 다음처럼 Collection Element의 단순 참조여야 합니다.

-- 가능
USING l_values(i)

-- 불가
USING UPPER(l_values(i))

표현식이 필요하면 미리 별도 Collection에 계산 결과를 저장합니다.

7. FORALL 오류 처리와 Rollback 범위

7.1 SAVE EXCEPTIONS가 없는 경우

FORALL 내부의 한 DML에서 처리되지 않은 예외가 발생하면 FORALL은 중단되고, 같은 FORALL 문에서 앞서 수행한 변경도 Rollback됩니다. FORALL 이후의 DML은 실행되지 않습니다.

이 동작을 PL/SQL Subprogram 전체의 자동 Rollback과 혼동하면 안 됩니다. 일반적으로 Subprogram이 처리되지 않은 예외로 종료됐다고 해서 Subprogram 이전의 모든 변경이 자동 Rollback되는 것은 아니며, Invoker가 Transaction 결과를 결정합니다.

7.2 SAVE EXCEPTIONS를 사용한 경우

DECLARE
    e_bulk_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
    FORALL i IN 1 .. l_order_ids.COUNT SAVE EXCEPTIONS
        UPDATE orders
        SET    status = 'PROCESSED'
        WHERE  order_id = l_order_ids(i);
EXCEPTION
    WHEN e_bulk_errors THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(
                'index=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
                ', code=' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE
            );
        END LOOP;
        RAISE;
END;
/

SAVE EXCEPTIONS를 사용하면 실패 정보를 저장하면서 나머지 DML을 계속 시도하고, FORALL 완료 후 ORA-24381을 발생시킵니다.

• SQL%BULK_EXCEPTIONS(i).ERROR_INDEX: 실패한 DML의 FORALL 반복 번호
• SQL%BULK_EXCEPTIONS(i).ERROR_CODE: 양수 형태의 Oracle Error Code
• 메시지 변환: SQLERRM(-ERROR_CODE)
• SQLERRM 메시지에는 일부 치환 인자가 포함되지 않을 수 있으므로 업무 Key와 입력값을 별도 기록

희소 Collection이나 VALUES OF를 사용하면 ERROR_INDEX를 업무 Collection Index로 단순 대입하지 말고 Bounds Clause의 매핑을 따라야 합니다.

7.3 성공 Row 수 확인

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

Implicit Cursor Attribute는 다음 SQL이 실행되면 바뀌므로 필요한 값은 즉시 Local Variable에 저장합니다.

7.4 부분 실패 정책

기술적으로 성공한 Row가 존재한다는 사실과 업무적으로 Commit 가능한지는 별개입니다.

전체 원자성이 필요함
→ 모든 변경 Rollback

독립 Row 처리 가능
→ 성공 Row Commit + 실패 Row 보정 Queue

재시도 가능
→ 업무 Key·Idempotency Key·처리 상태로 중복 실행 방지

8. Standard JDBC Batching

현재 Oracle JDBC에서는 Standard JDBC Batching을 사용해야 합니다. 과거 Oracle 고유 Update Batching은 12.1에서 Deprecated됐고 12.2 이후 No-Op이므로 현재 Driver에서는 지정한 Batch Size가 적용되지 않고 사실상 1행씩 처리될 수 있습니다.

8.1 적합한 형태

Standard JDBC Batch는 동일 PreparedStatement를 서로 다른 Bind 값으로 반복할 때 가장 효과적입니다.

String sql =
    "UPDATE orders " +
    "SET status = ?, updated_at = SYSTIMESTAMP " +
    "WHERE order_id = ?";

conn.setAutoCommit(false);

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    int batchSize = 1000;
    int pending = 0;

    for (OrderChange item : changes) {
        ps.setString(1, item.status());
        ps.setLong(2, item.orderId());
        ps.addBatch();
        pending++;

        if (pending == batchSize) {
            int[] counts = ps.executeBatch();
            pending = 0;
        }
    }

    if (pending > 0) {
        ps.executeBatch();
    }

    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

Oracle AI Database 26ai Driver는 addBatch 사용 시 Database와 Pipeline을 시작해 응답시간과 처리량을 개선할 수 있습니다. 그러나 이것이 Transaction Commit이나 오류 정책을 자동으로 처리한다는 의미는 아닙니다.

8.2 Batch에 적합한 조건

• 하나의 PreparedStatement에 동일 SQL Text 사용
• Bind 위치·Data Type·Metadata가 안정적
• Row마다 Bind 값만 다름
• Auto-Commit 비활성화
• Update Count와 오류 처리 정책 정의
• Driver·Framework가 실제 addBatch·executeBatch를 호출하는지 확인

Generic Statement와 CallableStatement도 표준 Batch API를 사용할 수 있으나 Oracle 구현에서 진정한 Batching 이점은 PreparedStatement 중심으로 기대해야 합니다.

9. JDBC Batch의 실행·Commit·Rollback 의미

9.1 addBatch와 executeBatch

• addBatch: Pending Statement Batch에 작업을 추가
• executeBatch: Pending Batch를 Database로 보내 처리
• commit: 이미 실행된 Batch와 Non-Batch DML을 Commit

commit은 아직 executeBatch하지 않은 Pending Batch를 자동 실행하지 않습니다.

9.2 rollback과 clearBatch

Connection Rollback은 Database에서 이미 실행된 DML을 Rollback하지만, Statement Object에 남아 있는 Pending Batch를 반드시 비우지는 않습니다. 재사용하기 전에 clearBatch()를 명시적으로 호출하거나 Statement를 폐기합니다.

executeBatch 성공 후 rollback
→ Database 변경 취소

addBatch만 수행 후 rollback
→ Pending Batch가 자동으로 비워진다고 가정하면 안 됨
→ clearBatch 또는 Statement Close 필요

9.3 BatchUpdateException

Batch 중 하나가 실패하면 Oracle Standard Batching은 처리를 중단하고 BatchUpdateException을 발생시킵니다. Exception의 Update Counts는 오류 전 성공한 작업 수와 각 작업의 영향 Row 수를 제공할 수 있습니다.

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

오류 후 성공 작업을 Commit할지 Rollback할지는 업무 원자성에 따라 결정합니다. Update Count만으로 업무 Key를 알 수 없으므로 Batch 입력 순서와 업무 Key를 함께 관리해야 합니다.

9.4 매우 큰 Batch와 Memory

한 Batch에 100,000행 이상처럼 과도한 작업을 넣으면 Bind 변환과 Buffer Memory 문제가 커질 수 있습니다. 26ai Driver는 기본적으로 매우 큰 Batch를 자동 분할하지 않으며, CONNECTION_PROPERTY_MAX_BATCH_MEMORY의 기본값 0은 최대 Batch 변환 Memory 제한이 없음을 뜻합니다.

따라서 Batch Size는 무조건 크게 하지 않고 다음을 함께 측정합니다.

• Bind Row 크기와 LOB·Long Data 포함 여부
• Client Heap·Direct Memory
• Driver Batch 변환 Memory
• Network Latency와 Packet 크기
• 오류 발생 시 재처리 비용

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

Batch Size
→ 한 번의 executeBatch에 묶는 Bind Row 수
→ Network·Driver·Memory 효율의 기술 단위

Commit Unit
→ 하나의 업무 Transaction으로 확정하는 Row 범위
→ 원자성·재시작·Lock·Undo의 업무 단위

예를 들어 다음 구성이 가능합니다.

Batch Size = 1,000
Commit Unit = 10,000

executeBatch 10회
→ 하나의 Transaction으로 COMMIT 1회

10.1 Batch Size가 너무 작을 때

• Execute Call과 Round Trip 증가
• Driver Batching 효과 감소
• Fixed Call Cost가 처리시간을 지배

10.2 Batch Size가 너무 클 때

• Client·Driver·Server Memory 사용 증가
• BatchUpdateException의 실패 위치 분석과 재시도 범위 증가
• 한 번의 Execute 지연과 Timeout 위험 증가
• Lock·Undo가 Commit까지 장시간 유지될 수 있음

10.3 Commit을 지나치게 자주 할 때

• log file sync와 Redo Flush 요청 증가
• 업무 원자성 훼손과 부분 성공 상태 발생
• 재시작·중복 방지 로직 복잡화
• Undo가 빠르게 재사용되는 환경에서 장기 Query의 Read Consistency에 불리할 수 있음

Commit Unit은 임의의 성능 숫자보다 함께 성공하거나 취소해야 하는 업무 범위에서 먼저 결정합니다.

11. Call을 줄여도 남는 DML 작업량

Call 최적화 이후에도 다음 비용은 변경 Row·Block에 따라 계속 발생합니다.

• Table Block Current Get과 변경
• 관련 B-Tree·Bitmap Index Entry 유지
• Primary·Unique·Foreign Key Constraint 검사
• Row·Statement Trigger 실행
• Undo·Redo 생성
• Lock·ITL 사용과 경합
• Commit Redo Flush

따라서 executions가 크게 감소했다는 이유만으로 전체 튜닝이 끝난 것이 아닙니다.

12. 측정과 검증

12.1 Session Call 통계

SELECT n.name,
       s.value
FROM   v$sesstat s
JOIN   v$statname n
  ON   n.statistic# = s.statistic#
WHERE  s.sid = :sid
AND    n.name IN (
         'user calls',
         'SQL*Net roundtrips to/from client',
         'parse count (total)',
         'execute count',
         'user commits',
         'redo size',
         'db block changes'
       )
ORDER BY n.name;

통계명은 Release와 Client 표현에 따라 Oracle Net Services round-trips처럼 표시될 수 있으므로 V$STATNAME에서 실제 이름을 확인합니다.

12.2 SQL별 효율

SELECT sql_id,
       executions,
       parse_calls,
       rows_processed,
       buffer_gets,
       disk_reads,
       ROUND(rows_processed / NULLIF(executions, 0), 2) AS rows_per_execute,
       ROUND(buffer_gets / NULLIF(rows_processed, 0), 2) AS gets_per_row,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1000, 2) AS ms_per_execute
FROM   v$sqlstats
WHERE  sql_id = :sql_id;

• rows_per_execute 증가: 한 Execute가 더 많은 Row를 처리했는지 확인
• gets_per_row: Call 감소 후에도 Row당 Buffer 작업이 과다한지 확인
• redo size, db block changes: DML 물리 작업량 확인
• user commits, log file sync: Commit 정책 영향 확인

V$SQLSTATS와 Session 통계는 누적값이므로 동일 조건의 Test 전후 Delta 또는 AWR Delta를 사용합니다.

12.3 Trace에서 확인할 항목

• Parse·Execute·Fetch Call 수
• Execute당 Row 수
• CPU·Elapsed·Wait
• SQL*Net message to/from client 분포
• Commit 횟수와 log file sync

단순히 총 Elapsed만 비교하지 말고 Call 구조와 Row Source 작업량이 함께 개선됐는지 확인합니다.

13. 진단·적용 절차

1. 동일 업무가 Row-by-Row로 반복되는가?
2. 하나의 Set-Based SQL로 표현 가능한가?
3. Source Grain·Key·Access Path가 정확한가?
4. PL/SQL 절차가 필요하면 BULK COLLECT·FORALL이 가능한가?
5. FORALL의 원격 Table·Parallel DML·Collection 제한을 확인했는가?
6. Application 반복 DML이면 Standard JDBC Batch를 사용했는가?
7. Auto-Commit·Pending Batch·Rollback·clearBatch 동작을 설계했는가?
8. Batch Size와 Commit Unit을 분리했는가?
9. 부분 실패·업무 Key·Idempotency·재시작 정책이 있는가?
10. Call 수와 Buffer·Redo·Undo·Lock을 같은 부하로 재측정했는가?

14. 혼동하기 쉬운 판단