PL/SQL에서 대량의 행을 처리할 때 BULK COLLECT와 FORALL을 쓰라는 이야기는 널리 알려져 있다. 그런데 LIMIT 값을 얼마로 둘지는 의견이 갈린다. 100이라는 숫자가 관행처럼 쓰이는데 근거가 명확하지 않은 경우가 많다.
왜 LIMIT이 필요한가
먼저 LIMIT 없는 BULK COLLECT가 왜 위험한지부터 짚는다.
declare
type t_rec is table of orders%rowtype;
v_rec t_rec;
begin
select * bulk collect into v_rec from orders;
end;
/이 코드는 테이블 전체를 한 번에 메모리로 올린다. 1천 건일 때는 문제가 없지만 500만 건이면 세션 메모리가 감당하지 못한다. 결과는 ORA-04030이다.
ORA-04030: out of process memory when trying to allocate ...더 나쁜 점은 이게 개발 환경에서 재현되지 않는다는 것이다. 테스트 데이터가 적으면 잘 돈다. 운영에서 데이터가 쌓인 어느 날 터진다. 그래서 건수가 고정적으로 작다는 확신이 없다면 LIMIT은 항상 붙인다.
반대편의 문제
그렇다고 LIMIT을 너무 작게 잡으면 BULK COLLECT를 쓰는 의미가 줄어든다. 이 기법의 효과는 PL/SQL 엔진과 SQL 엔진 사이의 왕복 횟수를 줄이는 데서 나온다.
100만 건을 처리한다고 하면 LIMIT 100은 왕복 1만 번, LIMIT 1000은 1천 번, LIMIT 5000은 200번이다. 한 줄씩 처리하는 커서 루프에 비하면 100도 이미 100배 개선이지만, 거기서 1000으로 올리면 한 번 더 크게 줄어든다.
실제로는 어디쯤인가
왕복 횟수 감소 효과는 선형이 아니다. 100에서 1000으로 갈 때의 개선 폭이 크고, 1000에서 10000으로 갈 때는 훨씬 작다. 반면 메모리 사용량은 LIMIT에 비례해서 계속 늘어난다.
그래서 일반적으로 500에서 1000 사이가 무난한 출발점으로 쓰인다. 행의 크기가 작으면 더 올려도 되고, LOB이나 긴 문자열이 섞여 있으면 낮춰야 한다.
정확한 값은 환경마다 다르므로 실제로 재보는 것이 맞다. 세션 메모리 사용량은 이렇게 확인한다.
select s.sid, s.username, st.value as pga_bytes
from v$session s
join v$sesstat st on st.sid = s.sid
join v$statname sn on sn.statistic# = st.statistic#
where sn.name = 'session pga memory max'
and s.sid = sys_context('userenv','sid');LIMIT 값을 바꿔 가며 이 값과 수행 시간을 함께 보면 자기 환경의 적정선이 나온다. 대개 어느 지점부터는 시간이 거의 안 줄고 메모리만 늘어난다. 그 직전이 적정값이다.
기본 형태
declare
type t_rec is table of orders%rowtype;
v_rec t_rec;
cursor c1 is select * from orders where stat_yn = 'N';
begin
open c1;
loop
fetch c1 bulk collect into v_rec limit 1000;
exit when v_rec.count = 0;
forall i in 1 .. v_rec.count
update orders set stat_yn = 'Y' where ord_no = v_rec(i).ord_no;
commit;
end loop;
close c1;
end;
/exit when v_rec.count = 0의 위치가 중요하다. FETCH 뒤에 두어야 마지막 묶음이 누락되지 않는다. exit when c1%notfound를 쓰면 마지막 묶음을 처리하지 않고 빠져나갈 수 있다. 오라클이 남은 행을 가져오면서 동시에 NOTFOUND를 설정하기 때문이다.
FORALL에서 에러 처리
FORALL은 기본적으로 한 건에서 에러가 나면 전체가 중단되고 롤백된다. 나머지를 계속 처리하고 싶다면 SAVE EXCEPTIONS를 쓴다.
begin
forall i in 1 .. v_rec.count save exceptions
update orders set stat_yn = 'Y' where ord_no = v_rec(i).ord_no;
exception
when others then
if sqlcode = -24381 then
for j in 1 .. sql%bulk_exceptions.count loop
log_error('BULK', sql%bulk_exceptions(j).error_code,
'idx=' || sql%bulk_exceptions(j).error_index, null);
end loop;
else
raise;
end if;
end;ORA-24381은 “벌크 작업 중 일부에서 에러가 발생했다”는 의미의 집합 에러다. 개별 에러는 sql%bulk_exceptions에서 인덱스와 에러 코드로 꺼낸다. error_index는 몇 번째 원소에서 났는지를 가리키므로 해당 키 값을 찾아 남길 수 있다.
메모리를 줄이는 방법
전체 행을 다 가져올 필요가 없는 경우가 많다. 업데이트 대상 키만 필요하다면 %rowtype 대신 필요한 컬럼만 담는다.
declare
type t_no is table of orders.ord_no%type;
v_no t_no;
begin
null;
end;
/컬럼 20개짜리 행 전체를 담는 것과 숫자 컬럼 하나만 담는 것은 메모리 차이가 크다. 같은 LIMIT이라도 훨씬 안전해진다. 컬렉션을 다 쓴 뒤 v_rec.delete로 비워 주는 것도 긴 루프에서는 도움이 된다.
정리
LIMIT은 메모리와 왕복 횟수 사이의 절충값이다. 100이라는 관행적인 숫자는 안전하지만 보수적이고, 대개 500~1000 구간에서 더 나은 균형이 나온다.
정하는 순서는 이렇다. 먼저 담을 컬럼을 최소화한다. 그다음 LIMIT을 몇 단계로 바꿔 가며 수행 시간과 세션 PGA를 함께 측정한다. 시간이 더 이상 줄지 않는 지점 직전을 고른다. 그리고 LIMIT 없는 BULK COLLECT는 건수가 고정적으로 작다는 확신이 있을 때만 쓴다.