대량 DML 튜닝 - 배치 처리와 인덱스 영향
이 장에서 배우는 것
온라인 서점의 야간 정산은 고객이 화면을 누르지 않는 시간에도 데이터베이스 자원을 사용한다. 처리 시간이 길어지면 다음 날 매출 집계가 늦어지고, 업무 시작 시각까지 디스크와 로그 자원을 점유한다. 이때 조회 부분의 실행계획만 살펴보면 원인을 놓치기 쉽다. 같은 주문을 읽더라도 행마다 데이터베이스를 호출하는지, 인덱스를 몇 개 유지하는지, 커밋을 얼마나 자주 수행하는지에 따라 전체 시간이 달라진다.
앞 장에서 읽을 데이터의 범위를 줄였다면, 여기서는 선택한 데이터를 어떤 단위로 기록할지 다룬다. 주문을 정산 테이블에 적재하는 하나의 배치를 건건 처리, 배열 처리, 집합 처리로 바꾸며 비교한다.
- 건건 처리와 집합 처리의 차이를 SQL 실행 횟수와 작업 범위로 설명한다.
- 배열 처리에서 메모리 인출 크기와 트랜잭션 경계를 구분한다.
- 대량 삽입에 포함된 인덱스 유지 비용과 제약 검증 비용을 식별한다.
- 커밋 주기를 복구 가능성, 원자성, 로그 동기화 비용과 함께 결정한다.
- 실행계획, 세션 논리 읽기, 처리 행 수를 같은 조건에서 비교한다.
문제 상황
서점은 매일 새벽 전날 결제 주문을 판매자별 정산의 기초 자료로 적재한다. 기존 프로그램은 주문 한 건을 읽고 수수료를 계산한 뒤 정산 행을 삽입한다. 개발 당시에는 대상이 수백 건이어서 문제가 드러나지 않았다. 거래량이 증가한 뒤에는 행마다 실행하는 삽입과 커밋이 배치 시간을 늘렸다.
정산 테이블에는 주문 번호 기본 키, 정산일과 판매자 번호를 묶은 조회용 인덱스, 판매자별 주문 조회용 인덱스가 있다. 판매자 외래 키와 금액 검사 제약도 존재한다. 한 행을 넣는다는 프로그램의 표현 뒤에 여러 구조를 변경하고 확인하는 작업이 숨어 있다.
실험 데이터는 주문 120,000건이다. 하루에 30,000건씩 네 날짜로 나누고, 주문 번호가 5의 배수인 주문은 취소 상태로 만든다. 2026년 9월 28일의 결제 주문 24,000건을 정산 대상으로 삼는다. 판매자는 100명이며, 정산 금액은 주문 금액에서 10% 수수료를 뺀 값이다. 환불과 정산 정책 변경은 이 실험의 범위에 넣지 않는다.
먼저 커밋 빈도만 바꾸고, 다음에는 삽입 실행 방식을 바꾼다. 마지막으로 조회용 인덱스 두 개를 제거한 상태를 비교한다. 기본 키와 무결성 제약은 마지막 실험에서도 유지한다. 각 실행 전에 적재 테이블을 비워 결과와 작업량을 맞춘다.
| 실행 이름 | 처리 방식 | 업무 커밋 | 보조 인덱스 |
|---|---|---|---|
| ROW_COMMIT | 행마다 INSERT | 24,000회 | 2개 |
| ROW_ONCE | 행마다 INSERT | 1회 | 2개 |
| ARRAY_ONCE | 1,000행씩 배열 바인딩 | 1회 | 2개 |
| SET_FULL | INSERT SELECT 한 문장 | 1회 | 2개 |
| SET_LEAN | INSERT SELECT 한 문장 | 1회 | 없음 |
처리 단위를 바꾸면 무엇이 줄어드는가
건건 처리와 집합 처리
건건 처리는 행마다 데이터 조작문(Data Manipulation Language, DML)을 실행한다. PL/SQL 반복문 안에서 실행하더라도 PL/SQL 실행기와 SQL 실행기 사이를 반복해서 전환한다. 애플리케이션이 원격 접속으로 같은 작업을 하면 통신 왕복 비용까지 붙을 수 있다. 이 실험은 서버 내부 PL/SQL이므로 그 통신 비용은 포함하지 않는다.
집합 처리는 대상 조건과 계산식을 하나의 SQL로 표현한다. INSERT SELECT는 입력 집합을 읽으면서 적재하고, 옵티마이저가 문장 전체의 접근 경로를 선택하게 한다. 반복문에서 하던 계산이 SQL 표현식으로 옮겨질 수 있다면 먼저 검토할 형태다. 다만 한 문장으로 바뀌어도 출력 행마다 필요한 인덱스 변경과 제약 검증이 사라지는 것은 아니다.
대상 주문을 한 번 읽어 반복하는 프로그램과 집합 SQL은 모두 원본 테이블을 한 번 전체 스캔할 수 있다. 따라서 원본 접근 경로가 같다는 사실만으로 개선 효과가 없다고 판단하면 안 된다. 이 사례에서는 삽입 실행 횟수, 실행기 전환, 커밋 횟수와 적재 대상의 유지 비용을 함께 보아야 한다.
배열 인출과 배열 바인딩
배열 처리는 여러 행을 메모리에 모아 SQL에 전달한다. Oracle의 BULK COLLECT는 조회 결과를 컬렉션으로 가져오고, FORALL은 컬렉션의 값을 DML에 묶어 바인딩한다. 애플리케이션에서 사용하는 드라이버의 배치 실행과 목적은 비슷하지만, 실제 전송과 실행 방식은 드라이버에 따라 달라진다.
이 장의 배열 처리는 FETCH LIMIT 1000으로 최대 1,000행을 인출한다. 전체 24,000행을 한꺼번에 저장하지 않으므로 세션의 작업 메모리 사용을 제한할 수 있다. 행에 긴 문자열이 추가되면 같은 1,000행도 메모리 크기가 달라진다. LIMIT은 바이트 한도가 아니다.
FORALL은 INSERT SELECT와 같은 하나의 관계형 입력 집합을 만드는 문법이 아니다. 여러 바인드 값으로 DML을 수행하면서 실행기 전환을 줄이는 기능이다. 행별 계산이나 절차적 판단 때문에 집합 SQL로 표현하기 어려운 부분이 남았을 때 유용하다.
마지막 인출에서 컬렉션이 비었는지 확인한 다음 FORALL을 실행해야 한다. 인출 직후 커서의 종료 상태만 보고 빠져나가면 마지막에 가져온 일부 행을 처리하지 않는 코드가 생길 수 있다. 또한 1,000행씩 가져온다는 사실은 1,000행마다 커밋해야 한다는 뜻이 아니다.
인덱스·제약·커밋을 별도의 비용으로 본다
삽입은 테이블 한 곳만 변경하지 않는다
일반적인 삽입은 테이블 블록뿐 아니라 유지 대상 인덱스의 블록도 변경한다. 키 분포와 여유 공간에 따라 추가 블록 접근이나 분할이 발생할 수 있다. 변경을 복구할 수 있도록 언두와 리두도 생성한다. 실행계획에 보조 인덱스 두 개를 갱신한다는 행이 보이지 않더라도 그 비용이 없는 것은 아니다.
기본 키와 고유 제약은 중복을 검사하고, 외래 키는 참조 대상의 존재를 확인한다. NOT NULL과 CHECK도 입력값을 검증한다. 이들은 비용이면서 데이터 품질을 보장하는 장치다. 논리 읽기를 줄이겠다는 이유만으로 모두 해제하면 정산 오류의 처리 책임이 다른 곳으로 이동한다.
실험에서는 보조 인덱스만 제거해 영향 범위를 좁힌다. 운영에서는 해당 인덱스를 사용하는 조회가 있는지 먼저 확인한다. 적재 후 다시 만든다면 재생성 시간과 임시 공간, 리두, 조회 가능 시점까지 전체 배치 시간에 포함해야 한다. 이 장의 SET_LEAN은 인덱스를 유지하지 않는 비용의 하한을 살펴보는 실험이며, 재생성을 포함한 운영 대안의 최종 성능은 아니다.
직접 경로 삽입(direct-path insert)은 별도의 선택지다. APPEND 힌트를 붙였다고 항상 적용되는 것은 아니며, 적용 제한과 트랜잭션 내 후속 접근 조건을 확인해야 한다. 인덱스 유지도 없어지지 않는다. NOLOGGING 역시 모든 로그를 없애는 스위치가 아니다. 복구 정책까지 실험 변수가 늘어나므로 완성 코드에서는 일반 삽입을 사용한다.
커밋 주기는 복구 단위다
기본적인 동기 커밋은 해당 트랜잭션을 확정하는 데 필요한 리두가 영속화될 때까지 기다린다. 여러 세션의 커밋이 함께 처리될 수 있으므로 커밋 횟수를 물리 쓰기 횟수와 일대일로 대응시키면 안 된다. 그래도 한 세션이 24,000번 커밋하면 한 번 커밋할 때보다 확정 요청과 대기가 반복된다.
커밋은 체크포인트처럼 모든 변경 데이터 블록을 즉시 파일로 쓰는 명령이 아니다. 커밋 빈도를 늘린다고 삽입 자체의 인덱스 변경이 줄어들지도 않는다. 따라서 커밋을 줄인 실험에서 경과 시간은 크게 줄지만 논리 읽기는 비슷하게 나올 수 있다.
한 번 커밋하면 원자성이 분명해지는 대신 큰 트랜잭션의 언두 공간, 실패 시 롤백 시간, 잠금 유지 시간이 필요하다. 나누어 커밋하면 앞부분만 반영된 상태가 생긴다. 운영 배치는 정산일과 주문 번호 같은 업무 키, 확정된 진행 위치, 재실행 시 중복 처리 규칙을 함께 설계해야 한다.
아래 실험은 오류가 없는 고정 입력을 비교하기 위해 네 가지 개선안에서 한 번 커밋한다. 운영에서도 언제나 한 번만 커밋하라는 뜻은 아니다. 정산일 전체를 한 단위로 확정해야 하는지, 판매자 묶음별 확정을 허용하는지부터 결정한다.
Oracle 19c와 MySQL 8의 차이
| 항목 | Oracle 19c | MySQL 8 | 비교할 때의 주의점 |
|---|---|---|---|
| 집합 적재 | INSERT SELECT | INSERT SELECT | 인덱스와 제약 유지 비용은 남는다. |
| 배열 처리 | PL/SQL BULK COLLECT와 FORALL | 같은 PL/SQL 문법은 없다. 드라이버 배치나 다중 행 INSERT 등을 검토한다. | 드라이버가 요청을 실제로 어떻게 묶는지 확인한다. |
| 자동 커밋 | 클라이언트 설정에 따라 달라진다. | 서버 세션의 autocommit 기본값은 켜짐이다. | 호출 API의 트랜잭션 설정도 확인한다. |
| 대량 적재 경로 | 적용 조건을 만족하면 직접 경로 삽입을 사용할 수 있다. | 다중 행 INSERT나 LOAD DATA 등을 사용한다. | APPEND와 일대일 대응하는 힌트로 취급하지 않는다. |
| 실측 계획 | DBMS_XPLAN으로 커서 실행 통계를 확인한다. | 8.0.18부터 EXPLAIN ANALYZE가 있지만 지원 문장과 출력이 다르다. | 이 장의 INSERT 실측 절차를 그대로 옮기지 않는다. |
| 논리 읽기 | 세션 통계와 계획의 Buffers를 활용한다. | InnoDB 및 Performance Schema 관측값을 조합한다. | 엔진 간 숫자를 동일한 블록 읽기 지표로 간주하지 않는다. |
사실 확인에는 Oracle의 대량 SQL과 바인딩 설명, INSERT 문 설명, 세션 통계 설명과 MySQL의 삽입 최적화 설명, EXPLAIN 설명을 참고할 수 있다. 아래 데이터와 코드는 이 사례를 위해 구성한 것이다.
완성 코드
macOS 또는 Linux에서 Oracle 19c에 접속할 수 있는 SQL*Plus를 사용한다. 로컬 운영체제에 데이터베이스가 설치되어 있을 필요는 없다. 아래 코드를 UTF-8 파일 bulk_dml.sql로 저장하고, 같은 이름의 객체가 없는 전용 실습 스키마에서 실행한다. 테이블과 프로시저 생성 권한, 테이블스페이스 할당량이 필요하다.
컴파일 시 경고 검사를 켠다. 저장 프로시저가 동적 성능 뷰를 읽으므로 DBA가 실습 계정에 SYS.V_$MYSTAT, SYS.V_$STATNAME, SYS.V_$SQL, SYS.V_$SQL_PLAN, SYS.V_$SQL_PLAN_STATISTICS_ALL, SYS.V_$SESSION의 SELECT 권한을 직접 부여해야 한다. 역할을 통한 권한만으로는 저장 프로시저 컴파일 조건을 만족하지 못할 수 있다. DBMS_STATS와 DBMS_XPLAN의 실행 권한도 필요하다.
스크립트는 실험마다 B10_SETTLEMENT를 TRUNCATE한다. 따라서 업무 데이터가 있는 스키마에 그대로 적용하는 코드가 아니다. 처리 전후 측정과 결과 검증을 끝낸 뒤 계측 결과를 별도 테이블에 기록한다. 계측 결과 저장에 사용하는 커밋은 출력의 업무 커밋 횟수에서 제외한다.
whenever sqlerror exit failure rollback
set echo off
set verify off
set feedback off
set define off
set serveroutput on size unlimited
set autocommit off
set linesize 220
set pagesize 500
alter session set plsql_warnings = 'ENABLE:ALL';
create table b10_seller (
seller_id number(10) primary key
);
create table b10_orders (
order_id number(12) primary key,
seller_id number(10) not null,
ordered_at date not null,
order_status varchar2(10) not null,
gross_amount number(12,2) not null
);
create table b10_settlement (
order_id number(12),
seller_id number(10) not null,
batch_day date not null,
net_amount number(12,2) not null,
constraint b10_st_pk primary key (order_id),
constraint b10_st_fk foreign key (seller_id)
references b10_seller (seller_id),
constraint b10_st_ck check (net_amount >= 0)
);
create index b10_st_day_ix
on b10_settlement (batch_day, seller_id);
create index b10_st_seller_ix
on b10_settlement (seller_id, order_id);
create table b10_runs (
run_no number primary key,
run_name varchar2(20) not null,
row_count number not null,
commit_count number not null,
elapsed_cs number not null,
logical_reads number not null
);
create table b10_plans (
run_no number not null,
plan_kind varchar2(10) not null,
line_no number not null,
plan_line varchar2(4000)
);
insert into b10_seller (seller_id)
select level
from dual
connect by level <= 100;
insert into b10_orders (
order_id, seller_id, ordered_at, order_status, gross_amount
)
select level,
mod(level - 1, 100) + 1,
date '2026-09-25' + trunc((level - 1) / 30000),
case when mod(level, 5) = 0 then 'CANCELLED'
else 'PAID' end,
10000 + mod(level, 5000)
from dual
connect by level <= 120000;
commit;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'B10_SELLER',
cascade => true
);
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'B10_ORDERS',
cascade => true
);
end;
/
create or replace procedure b10_run (
p_no in pls_integer,
p_name in varchar2,
p_mode in varchar2
) authid definer
is
-- [A] 원본 조회와 배열 선언
cursor c_orders is
select /*+ gather_plan_statistics */ /* B10_SOURCE */
order_id, seller_id, gross_amount
from b10_orders
where ordered_at >= date '2026-09-28'
and ordered_at < date '2026-09-29'
and order_status = 'PAID';
type t_numbers is table of number index by pls_integer;
l_ids t_numbers;
l_sellers t_numbers;
l_amounts t_numbers;
l_rows pls_integer := 0;
l_commits pls_integer := 0;
l_count pls_integer;
l_missing pls_integer;
l_start_reads number;
l_reads number;
l_start_cs number;
l_elapsed_cs number;
l_tag varchar2(30);
-- [B] 현재 세션의 논리 읽기
function read_counter return number is
l_value number;
begin
select m.value
into l_value
from v$mystat m
join v$statname n on n.statistic# = m.statistic#
where n.name = 'session logical reads';
return l_value;
end read_counter;
-- [C] 마지막 실행의 계획을 텍스트로 보관
procedure save_plan (
p_tag in varchar2,
p_kind in varchar2
) is
l_sql_id varchar2(13);
l_child number;
l_line pls_integer := 0;
begin
select sql_id, child_number
into l_sql_id, l_child
from (
select sql_id, child_number
from v$sql
where instr(sql_text, p_tag) > 0
and command_type in (2, 3)
order by last_active_time desc, child_number desc
)
where rownum = 1;
for r in (
select plan_table_output
from table(
dbms_xplan.display_cursor(
l_sql_id,
l_child,
'ALLSTATS LAST +PREDICATE'
)
)
) loop
l_line := l_line + 1;
insert into b10_plans (
run_no, plan_kind, line_no, plan_line
) values (
p_no, p_kind, l_line, r.plan_table_output
);
end loop;
end save_plan;
begin
if p_mode not in ('ROW', 'ARRAY', 'SET')
or p_mode is null then
raise_application_error(-20001, 'Invalid mode');
end if;
-- [D] 초기화는 계측 범위 밖에 둔다.
execute immediate 'truncate table b10_settlement';
l_start_reads := read_counter;
l_start_cs := dbms_utility.get_time;
if p_mode = 'ROW' then
-- [E] 행마다 삽입, 커밋 조건만 분리
l_tag := 'B10_ROW_INSERT';
for r in c_orders loop
insert /*+ gather_plan_statistics */
/* B10_ROW_INSERT */
into b10_settlement (
order_id, seller_id, batch_day, net_amount
) values (
r.order_id,
r.seller_id,
date '2026-09-28',
r.gross_amount - round(r.gross_amount * 0.10, 2)
);
l_rows := l_rows + sql%rowcount;
if p_name = 'ROW_COMMIT' then
commit;
l_commits := l_commits + 1;
end if;
end loop;
elsif p_mode = 'ARRAY' then
-- [F] 배열 크기는 1,000행, 커밋은 루프 밖
l_tag := 'B10_ARRAY_INSERT';
open c_orders;
loop
fetch c_orders bulk collect
into l_ids, l_sellers, l_amounts limit 1000;
exit when l_ids.count = 0;
forall i in 1 .. l_ids.count
insert /*+ gather_plan_statistics */
/* B10_ARRAY_INSERT */
into b10_settlement (
order_id, seller_id, batch_day, net_amount
) values (
l_ids(i),
l_sellers(i),
date '2026-09-28',
l_amounts(i) - round(l_amounts(i) * 0.10, 2)
);
l_rows := l_rows + sql%rowcount;
end loop;
close c_orders;
else
-- [G] 집합 적재
l_tag := 'B10_SET_INSERT';
insert /*+ gather_plan_statistics */
/* B10_SET_INSERT */
into b10_settlement (
order_id, seller_id, batch_day, net_amount
)
select order_id,
seller_id,
date '2026-09-28',
gross_amount - round(gross_amount * 0.10, 2)
from b10_orders
where ordered_at >= date '2026-09-28'
and ordered_at < date '2026-09-29'
and order_status = 'PAID';
l_rows := sql%rowcount;
end if;
if p_name != 'ROW_COMMIT' then
commit;
l_commits := l_commits + 1;
end if;
l_elapsed_cs := dbms_utility.get_time - l_start_cs;
l_reads := read_counter - l_start_reads;
-- [H] 행 수와 주문별 결과를 계측 후 검증
select count(*)
into l_count
from b10_settlement;
select count(*)
into l_missing
from b10_orders o
where o.ordered_at >= date '2026-09-28'
and o.ordered_at < date '2026-09-29'
and o.order_status = 'PAID'
and not exists (
select 1
from b10_settlement s
where s.order_id = o.order_id
and s.seller_id = o.seller_id
and s.batch_day = date '2026-09-28'
and s.net_amount =
o.gross_amount - round(o.gross_amount * 0.10, 2)
);
if l_rows != 24000 or l_count != 24000 or l_missing != 0 then
raise_application_error(-20002, 'Result mismatch');
end if;
-- [I] 계측 기록과 계획 보관
insert into b10_runs (
run_no, run_name, row_count, commit_count,
elapsed_cs, logical_reads
) values (
p_no, p_name, l_rows, l_commits,
l_elapsed_cs, l_reads
);
save_plan(l_tag, 'DML');
if p_mode in ('ROW', 'ARRAY') then
save_plan('B10_SOURCE', 'SOURCE');
end if;
commit;
dbms_output.put_line(
p_name || ' rows=' || to_char(l_rows, 'FM9999990')
|| ' commits=' || to_char(l_commits, 'FM9999990')
);
end b10_run;
/
-- 경고를 포함한 컴파일 진단이 있으면 실행을 중단한다.
declare
l_diagnostics pls_integer;
begin
select count(*)
into l_diagnostics
from user_errors
where name = 'B10_RUN'
and type = 'PROCEDURE';
if l_diagnostics != 0 then
raise_application_error(-20003, 'Check USER_ERRORS for B10_RUN');
end if;
end;
/
begin
b10_run(1, 'ROW_COMMIT', 'ROW');
b10_run(2, 'ROW_ONCE', 'ROW');
b10_run(3, 'ARRAY_ONCE', 'ARRAY');
b10_run(4, 'SET_FULL', 'SET');
end;
/
drop index b10_st_day_ix;
drop index b10_st_seller_ix;
begin
b10_run(5, 'SET_LEAN', 'SET');
end;
/
-- 환경에 따라 달라지는 측정값은 파일로 출력한다.
set termout off
set trimspool on
column run_name format a16
column elapsed_seconds format 9999990.00
column logical_reads format 999999999990
column plan_kind format a10
column plan_line format a170
spool b10_metrics.txt
select run_no, run_name, row_count, commit_count,
elapsed_cs / 100 as elapsed_seconds, logical_reads
from b10_runs
order by run_no;
select run_no, plan_kind, line_no, plan_line
from b10_plans
order by run_no, plan_kind, line_no;
spool off
set termout on
prompt Report: b10_metrics.txt
exit success줄별 해설
[A] 원본 커서는 세 처리 방식이 같은 조건을 사용하도록 만든다. 원본 테이블에는 날짜 조건용 인덱스를 만들지 않았다. 이 데이터에서는 전체 스캔을 기준으로 삽입 방식의 차이를 관찰한다. 날짜의 상한을 다음 날 미만으로 지정하므로 시각이 포함된 데이터로 확장해도 하루 범위의 의미가 유지된다.
[B] session logical reads는 현재 세션의 누적 논리 읽기다. 시작값과 종료값의 차이로 배치 구간을 측정한다. 조회의 일관성 읽기뿐 아니라 변경 작업에 필요한 현재 모드 읽기도 포함하는 지표다. 계측 조회 자체와 구간 내 재귀 작업도 일부 포함되므로 원본 스캔의 Buffers와 같은 값으로 해석하지 않는다.
[C] 주석 표식으로 실험 SQL을 찾아 자식 커서 번호까지 지정한다. 계획은 인덱스를 제거하기 전에 문자열로 보관한다. 따라서 뒤의 DDL로 커서가 무효화되더라도 앞선 계획을 파일에서 볼 수 있다. 전용 실습 스키마에서 혼자 실행한다는 조건이며, 같은 표식의 SQL을 동시에 실행하는 운영 수집기로 사용하지 않는다.
[D] TRUNCATE는 데이터를 초기화하는 DDL이다. 암묵적 커밋을 수반하므로 업무 처리의 트랜잭션 경계 안에 넣지 않는다. 계측도 초기화가 끝난 뒤 시작한다. 캐시는 비우지 않으므로 뒤의 실행이 앞의 실행으로 읽힌 블록을 이용할 수 있다.
[E] 행별 삽입은 SQL 실행 직후 SQL%ROWCOUNT를 누적한다. 첫 실행만 행마다 커밋하고, 두 번째 실행은 반복문이 끝난 뒤 커밋한다. 이 두 결과를 비교하면 삽입 형태를 바꾸지 않은 상태에서 커밋 빈도의 영향을 볼 수 있다.
[F] 배열은 각각 주문 번호, 판매자 번호, 주문 금액을 담는다. 컬렉션이 비었으면 종료하고, 그렇지 않으면 실제 개수만큼 바인딩한다. FORALL 직후 SQL%ROWCOUNT는 그 FORALL에서 처리한 전체 행 수다. 24개의 데이터 묶음을 처리하지만 트랜잭션은 마지막 커밋까지 이어진다.
[G] 집합 삽입은 원본 조회와 금액 계산, 적재를 하나의 문장으로 표현한다. 출력 건수는 DML 직후 저장한다. 계획의 최상위 A-Rows가 삽입 건수를 항상 표시한다고 가정하지 않는다.
[H] 총건수만 맞으면 다른 주문을 넣어도 성공으로 보일 수 있다. 그래서 각 원본 주문과 판매자, 정산일, 정산 금액이 일치하는 행이 있는지도 검사한다. 기본 키가 중복을 막고 총건수와 누락 검사가 함께 통과하므로 예상한 집합과 결과가 일치함을 확인한다. 이 검증은 계측 이후에 실행한다.
[I] 결과 기록과 실행계획 수집은 업무 구간에서 제외한다. 계획 통계 수집 자체의 부담은 업무 구간에 포함된다. 특히 행별 실행은 수집 횟수가 많아 영향을 더 받을 수 있다. 원인을 파악한 뒤에는 수집 힌트를 제거한 별도 시간 측정도 필요하다.
실행 결과
다음 명령은 비밀번호를 명령행에 남기지 않고 접속한다. 접속 식별자 BOOKLAB과 계정 BOOK_BENCH는 준비한 환경에 맞춘다. 스크립트는 @로 실행해야 TERMOUT 설정에 따라 측정 보고서만 파일로 분리된다.
sqlplus -s BOOK_BENCH@BOOKLAB @bulk_dml.sql인증을 마친 뒤 정상 실행 시 스크립트가 콘솔에 출력하는 내용은 다음과 같다. 처리 건수와 업무 커밋 횟수는 코드의 데이터 생성 규칙으로 결정된다.
ROW_COMMIT rows=24000 commits=24000
ROW_ONCE rows=24000 commits=1
ARRAY_ONCE rows=24000 commits=1
SET_FULL rows=24000 commits=1
SET_LEAN rows=24000 commits=1
Report: b10_metrics.txt경과 시간, 논리 읽기와 실제 계획은 현재 작업 디렉터리의 b10_metrics.txt에 기록된다. 이 원고에서 Oracle 인스턴스를 실행해 측정한 값은 없다. 아래 숫자는 해석 방법을 설명하기 위한 가상 측정값이며 프로그램의 예상 출력이 아니다. 독자의 결과가 해당 숫자와 같아야 하는 것도 아니다.
| 실행 이름 | 경과 시간 | 세션 논리 읽기 | 주요 해석 |
|---|---|---|---|
| ROW_COMMIT | 18.40초 | 248,000 | 행별 실행과 잦은 커밋이 함께 존재한다. |
| ROW_ONCE | 4.90초 | 241,000 | 읽기 감소보다 커밋 대기 감소의 영향이 크다. |
| ARRAY_ONCE | 2.30초 | 198,000 | 실행기 전환 감소와 블록 접근 효율 변화를 관찰한다. |
| SET_FULL | 1.80초 | 175,000 | 집합 적재 후에도 인덱스와 제약 비용이 남는다. |
| SET_LEAN | 1.10초 | 103,000 | 보조 인덱스 유지 비용이 제거된 구간이다. |
실행계획도 역할을 나누어 비교한다. 행별 방식에서는 원본 조회 계획과 삽입 계획이 분리된다. 집합 방식에서는 원본 접근이 삽입 계획 아래에 들어간다. 다음은 대표적인 계획 구조를 축약한 설명이며, DBMS_XPLAN의 실제 출력 전체를 재현한 것은 아니다.
ROW_COMMIT / ROW_ONCE / ARRAY_ONCE
원본 조회:
SELECT STATEMENT
TABLE ACCESS FULL B10_ORDERS
삽입:
INSERT STATEMENT
LOAD TABLE CONVENTIONAL B10_SETTLEMENT
SET_FULL / SET_LEAN
INSERT STATEMENT
LOAD TABLE CONVENTIONAL B10_SETTLEMENT
TABLE ACCESS FULL B10_ORDERS원본 접근의 A-Rows는 필터를 통과한 24,000행과 연결해서 읽는다. 집합 삽입에서는 원본 접근의 Starts가 통상 1인지를 확인하고, 필터 조건도 비교한다. 적재 연산의 통계 표시 방식은 하위 버전과 실행 경로에 따라 달라질 수 있으므로, 파일에 기록된 실제 계획을 우선한다.
ALLSTATS LAST는 마지막 실행의 통계다. 행별 삽입의 마지막 실행 한 번과 집합 삽입 한 번은 작업 범위가 다르다. 따라서 두 계획의 마지막 Buffers만 직접 비교해서 개선율을 계산하면 안 된다. 배열 DML의 마지막 실행 통계도 전체 배치 총합으로 단정하지 않는다. 이 실험의 전후 총량 비교에는 B10_RUNS의 세션 차분을 사용한다.
계획에서 SET_FULL과 SET_LEAN의 연산 모양이 같아도 논리 읽기는 달라질 수 있다. 조회용 인덱스 유지가 별도 계획 행으로 나타나지 않을 수 있기 때문이다. 반대로 ROW_COMMIT과 ROW_ONCE의 읽기가 비슷해도 시간이 달라질 수 있다. 계획과 논리 읽기는 커밋 동기화 대기 전체를 대신하지 못한다.
가상 값에서는 ROW_ONCE 대비 SET_FULL의 논리 읽기가 약 27.4% 줄고, 경과 시간은 약 63.3% 줄었다. SET_FULL 대비 SET_LEAN의 읽기는 약 41.1% 줄었다. 실제 판단에서는 같은 초기 상태로 여러 번 수행하고 순서도 바꿔 본다. 중앙값과 변동 폭을 기록하되 운영 데이터베이스의 공유 캐시를 강제로 비우지는 않는다.
실무에서 자주 틀리는 것
인출 크기를 곧바로 커밋 주기로 삼는다
다음 코드는 메모리 사용을 제어하려던 숫자를 업무 확정 단위로 바꾼다. 중간 오류가 나면 앞선 묶음은 이미 확정되어 있다.
-- 잘못된 선택: 업무 경계를 검토하지 않고 매 배열마다 커밋
loop
fetch c_orders bulk collect
into l_ids, l_sellers, l_amounts limit 1000;
exit when l_ids.count = 0;
forall i in 1 .. l_ids.count
insert into b10_settlement
values (l_ids(i), l_sellers(i), date '2026-09-28',
l_amounts(i) - round(l_amounts(i) * 0.10, 2));
commit;
end loop;하루 전체를 하나의 트랜잭션으로 처리하기로 했다면 커밋은 반복문 밖에 둔다. 부분 확정이 필요하면 마지막 확정 업무 키와 재시작 규칙을 먼저 정의한다.
-- 수정: 배열 크기와 업무 확정을 분리
loop
fetch c_orders bulk collect
into l_ids, l_sellers, l_amounts limit 1000;
exit when l_ids.count = 0;
forall i in 1 .. l_ids.count
insert into b10_settlement
values (l_ids(i), l_sellers(i), date '2026-09-28',
l_amounts(i) - round(l_amounts(i) * 0.10, 2));
end loop;
commit;마지막 인출 결과를 처리하기 전에 종료한다
다음 형태는 마지막 부분 묶음을 놓칠 수 있다. 예를 들어 대상이 24,001행이고 인출 크기가 1,000이면 마지막 한 행도 처리해야 한다.
-- 잘못된 종료 위치
fetch c_orders bulk collect
into l_ids, l_sellers, l_amounts limit 1000;
exit when c_orders%notfound;
-- 배열 처리가져온 배열이 비었을 때 종료하면 부분 묶음을 포함해 처리한다.
-- 수정
fetch c_orders bulk collect
into l_ids, l_sellers, l_amounts limit 1000;
exit when l_ids.count = 0;
-- 배열 처리재실행을 위해 무결성 제약부터 해제한다
이미 확정된 주문을 다시 삽입해 기본 키 오류가 나자 기본 키를 해제하면 중복 정산을 허용하게 된다. 빠른 실행과 올바른 결과를 바꿔서는 안 된다.
-- 잘못된 대응
alter table b10_settlement disable constraint b10_st_pk;같은 정산일을 통째로 다시 만들 수 있고 원본이 확정되어 있다면, 해당 날짜 삭제와 재삽입을 한 트랜잭션으로 묶는 방법을 검토한다. 다음 형태는 다른 실행자가 같은 정산일을 동시에 처리하지 않는다는 조건이 필요하다. 삭제에 필요한 언두와 시간도 측정한다.
-- 수정 예: 해당 날짜를 하나의 트랜잭션으로 교체
delete from b10_settlement
where batch_day = date '2026-09-28';
insert into b10_settlement (
order_id, seller_id, batch_day, net_amount
)
select order_id, seller_id, date '2026-09-28',
gross_amount - round(gross_amount * 0.10, 2)
from b10_orders
where ordered_at >= date '2026-09-28'
and ordered_at < date '2026-09-29'
and order_status = 'PAID';
commit;오류를 삼킨 뒤 성공으로 확정한다
행별 예외를 무시하면 정상 종료 메시지 뒤에 누락된 정산이 남을 수 있다. 성공한 행만 확정하는 정책이라면 실패 행의 업무 키와 사유, 재처리 절차가 별도로 필요하다.
-- 잘못된 예외 처리
exception
when others then
commit;배치 전체가 성공해야 하는 정책에서는 최상위 트랜잭션 소유자가 롤백하고 오류를 전달한다. 이미 이전 묶음을 커밋했다면 아래 코드도 그 묶음을 되돌리지는 못한다.
-- 수정: 전체 성공 정책의 최상위 처리기
exception
when others then
rollback;
raise;한눈에 보기
| 조정 대상 | 기대 효과 | 확인할 증거 | 함께 검토할 조건 |
|---|---|---|---|
| 건건 처리를 집합 SQL로 변경 | 반복 실행과 절차적 전환 감소 | 처리 건수, 전체 시간, 세션 논리 읽기 | 계산과 오류 처리의 의미가 같은가 |
| 배열 바인딩 | 실행기 전환 및 전송 부담 감소 | 배열 크기별 시간과 메모리 사용 | 마지막 부분 묶음도 처리하는가 |
| 보조 인덱스 축소 | 삽입 시 인덱스 유지 작업 감소 | 계획과 세션 읽기를 함께 비교 | 조회 영향과 재생성 비용을 포함했는가 |
| 커밋 주기 조정 | 확정 요청과 동기화 대기 감소 | 업무 커밋 횟수와 경과 시간 | 원자성, 언두, 실패 복구를 감당하는가 |
| 측정 범위 통일 | 서로 다른 작업량의 비교 방지 | 동일 입력과 결과 검증 | 초기화·계측·재생성의 포함 범위가 같은가 |
단일 세션에서 처리량을 높였다고 여러 배치를 동시에 실행했을 때도 같은 비율로 빨라지는 것은 아니다. 다음 장에서는 여러 실행자가 같은 데이터와 블록을 변경할 때 나타나는 경합을 살펴본다.
연습 문제
- ROW_COMMIT에서 ROW_ONCE로 바꾼 뒤 논리 읽기는 3%만 줄고 경과 시간은 70% 줄었다. 이 결과가 모순이 아닌 이유와 추가로 확인할 대기 항목을 설명하라.
- 대상이 24,001행일 때 LIMIT 1000인 배열 처리에서 데이터가 있는 인출은 몇 번인가. 빈 배열로 종료하는 인출까지 포함하면 몇 번인가. 업무 커밋을 한 번으로 유지하려면 어디에 둬야 하는가.
- SET_FULL이 80초, 인덱스를 제거한 적재가 45초, 인덱스 두 개의 재생성이 총 50초다. 운영 조회에 두 인덱스가 계속 필요할 때 어떤 시간을 비교해야 하는가. 재생성 방식의 손익 분기 조건도 제시하라.
- 행별 삽입의 ALLSTATS LAST Buffers가 9이고 집합 삽입의 값이 170,000이다. 어느 방식이 유리한지 이 두 숫자로 결정할 수 있는가. 동일 범위의 비교 방법과 결과 검증 방법을 제시하라.
정답과 해설
- 논리 읽기는 블록 접근량을 나타내며 커밋 확정 대기 시간을 직접 표현하지 않는다. 삽입 작업이 비슷해도 커밋 요청이 줄면 경과 시간이 크게 줄 수 있다. Oracle에서는 해당 세션의 log file sync 대기 횟수와 시간을 비교하고, 필요한 경우 로그 쓰기 지연을 함께 확인한다. 경과 시간 감소분 전체를 확인 없이 하나의 대기로 단정하지는 않는다.
- 데이터가 있는 인출은 25번이다. 앞의 24번은 각 1,000행이고 마지막은 1행이다. 완성 코드처럼 빈 컬렉션을 확인해 종료하면 추가 인출을 포함해 총 26번이다. 커밋은 모든 배열 처리가 끝난 뒤에 둔다. LIMIT을 바꾸어도 업무 트랜잭션의 경계는 자동으로 바뀌지 않는다.
- 인덱스를 유지한 80초와 제거 후 적재·재생성을 합친 최소 95초를 비교한다. 후자에는 인덱스 제거와 검증 등 부대 작업 시간도 더해진다. 주어진 조건에서는 인덱스를 유지하는 쪽이 짧다. 적재에서 절약한 35초보다 재생성과 부대 작업의 총시간이 작아야 시간 측면의 이득이 생긴다. 임시 공간과 조회 중단 조건도 별도로 만족해야 한다.
- 결정할 수 없다. 마지막 행별 실행 한 번과 전체 집합 실행 한 번의 작업량이 다르기 때문이다. 같은 대상 건수와 인덱스 조건에서 배치 시작·종료 사이의 세션 논리 읽기 차분과 경과 시간을 비교한다. 결과는 처리 건수만 확인하지 않고 주문별 판매자, 정산일, 금액까지 맞춰야 한다. 행별 Buffers에 행 수를 곱하는 계산은 캐시와 블록 분할 등의 차이를 반영하지 못하므로 실측 총량을 대신하지 못한다.