Fetch Call 최소화 (by treeone) [2010.04.11]
Fetch Call 최소화
부분범위처리의 원리
결과집합을 전송할 때, 전체 데이터를 쉼 없이 연속적으로 처리하지 않고 사용자로부터 Fetch Call이 있을때마다 일정량씩 나누어서 전송하는 것을 말한다.
DBMS는 데이터를 클라이언트에게 전송할 때 일정량씩 나누어 전송하며, 오라클의 경우 ArraySize(또는 FetchSize) 설정을 통해 운반단위를 조절한다.
그리고 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 클리이언트로부터 추가Fetch Call을 받기 전까지는 그대로 멈춰 서서 기다린다.
또한 아래와 같이 클라이언트에게 전송할 량이 끝났어도(오라클은 끝났는지 모름) 운반단위가 차지 않으면 운반단위가 다 찰때까지 대기한후, 끝까지 다 읽은 후에야 더이상의 데이터가 없음을 인지하고 데이터의 나머지를 내보낸다.
이러한 부분범위 처리는 전체 일량을 줄이는 것과는 상관이 없지만, OLTP환경등 일부 데이터만 먼저 보여지고자 할때 유용하게 사용할 수 있다.
흔히 Array Fetch를 얘기할 때, Array 버퍼가 서버 측(Server Side)에 할당된다고 생각한다.
서버 측 Array 버퍼에 데이터가 차면 전송한다는 설명이다. 하지만 필자가 아는 한 Array 버퍼는 클라이언트(Client Side)에 위치하며, 서버 측에서는 SUD에 버퍼링이 이루어진다.
오라클에서 데이터를 전송하는 단위는 ArraySize에 의해 결정된다. 하지만 내부적으로 데이터는 네트워크 패킷 단위로 단편화되어 여러 번에 걸쳐 나누어 전송된다. 부분범위처리 내에 또 다른 부분범위처리가 작동하는 것이다.
이렇게 패킷으로 단편화해야 유실이나 에러가 발생했을 때 부분 재전송을 통해 복구할 수 있다.
OSI 7 레이어(Application, Presentation, Network, Data Link, Physical)가 있는데, 오라클서버와 클라이언트는 Application레이어에 위치하며, 그 아래에 있는 에리어를 통해 서로 데이터를 주고받는다.
SDU(Session Data Unit) : 네트워크를 통해 전송하기 전에 Oracle Net이 데이터를 담아 두려고 사용하는 버퍼이다.
서버 측에서 클라이언트로부터 Fetch Call을기다리는데, Oracle Net이 서버 프로세스로 부터 전송명령을 받을 때까지 대기하는 곳이다. Oracle Net은 서버 프로세스로부터 전송요청을 받기 전이라도 SDU가 다 차면 버퍼에 쌓인 데이터를 전송하는데, 이때 클라이언트로부터 Fetch Call을 기다리지 않고 곧이어 데이터를 받아 SDU를 계속 채워나간다.
TDU(Transport Data Unit) : Transport 레이어 데이터 버퍼에 대한 규격이다. 물리적인 하부 레이어로 내려보내기 전에 데이터를 잘게 쪼개어 클라이언트에게 전송되는 도중에 유실이나 에러가 없도록 제어하는 역할을 한다.
SUD와 TDU 사이즈는 TNSNAMES.ORA, LISTENER.ORA 파일에서 아래와 같이 설정 가능하며, 이들의 기본 설정 값은 2KB이다.
(SDU=2048)(TDU=2048)
OLTP 환경에서 부분범위처리에 의한 성능개선 원리
이 내용은 우리가 대용량데이터베이스에서 지겹게 해 온 내용이다.
아래 테스트 코드로 테스트 해 보자
-- 테이블 생성
create table t (
x NUMBER not null
, y NUMBER not null ) ;
-- 데이터 insert(랜덤으로)
insert into t
select *
from (
select rownum x, rownum y
from dual
connect by level <= 5000000
)
order by dbms_random.value
;
alter table t add
constraint t_pk primary key (x);
alter system flush buffer_cache;
-- 6건 검색
select /*+ index(t t_pk) */ x, y
from t
where x > 0
and y <= 6 ;
-- 1건 검색
select /*+ index(t t_pk) */ x, y
from t
where x > 0
and y <= 1 ;
그림 5-6(페이지376) 을 참조하여 보기 바란다.
앞에서 설명했던 내용의 연장이다. 책에 있는 내용을 빌어 설명하자면
첫번째 Fetch Call에서는 인덱스를 따라 x컬럼 값이 1~5인 5개의 레코드는 테이블 필터 조건인 y<=6조건도 만족한다.
오라클 서버는 이 5개 레코드를 아주 빠르게 찾았으므로 지체 없이 전송 명령을 통해 클라이언트에게 전송하고, 클라이언트는 Array 버퍼에 담긴 5개의 레코드를 곧바로 화면에 출력한다.
문제는 두번째 Fetch Call에서 발생한다. 두번째 Fetch Call 명령을 받자마자 x=y=6인 레코드를 찾아 Oracle Net으로 내려보낸다. 이제 조건에 더이상 만족하는 레코드가 없다는 사실을 오라클은 모르기 때문에 계속 인덱스를 스캔하면서 테이블을 엑세스해 본다. 끝까지 가 본 후에야 더는 전송할 데이터가 없음을 인식하고 그대로 한 건만 전송하도록 Oracle Net에 명령을 보낸다.
ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
대량 데이터를 내려받을 때 ArraySize를 크게 설정하면
- Fetch Call 횟수가 줄어 네트워크 부하가 감소하고, 쿼리 성능이 향상
- 서버 프로세스가 읽어야 할 블록 개수까지 줄어든다.
ArraySize를 조정하는데 왜 블록 I/O가 줄어드는 것일까? 직접 테스트 해보자
SQL> create table test as select * from all_objects;
테이블이 생성되었습니다.
SQL> set autotrace traceonly statistics;
SQL> set arraysize 2;
SQL> select * from test;
47094 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23871 consistent gets
530 physical reads
0 redo size
6247169 bytes sent via SQL*Net to client
259498 bytes received via SQL*Net from client
23548 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
47094 rows processed
ArraySize를 2로 설정하고 47094로우를 가져오게 함.
읽은 블록개수 : 23871
Featch 횟수 : 23548
===> Fetch할 때마다 2개의 로우(47094/23548 = 1.9999....) 씩 읽는것을 알 수 있다.
SQL 트레이스를 이용해도 같다. 각 항목을 매치시켜 보자.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23548 0.27 1.54 0 23871 0 47094
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23550 0.27 1.58 0 23871 0 47094
| db block gets | current |
| consistent gets | query |
| physical reads | disk |
| SQL*Net roundtrips to/from client | Fetch count |
| rows processed | Fetch rows |
ArraySize를 계속 늘려서 위와같은 테스트를 반복해 보자.
ArraySize를 키울수록 Fetch Count는 횟수가 줄고 더불어 Block I/O까지 주는것을 볼수 있다. 즉, 반비례다.
보기쉽게 그래프로 정리해 놓았다.(책내용임)
그러나 ArraySize를 키운다고 같은 비율로 Fetch Count와 Block I/O가 줄지 않는다는 것을 확인할 수 있다.
따라서, 무작정 크게 설정한다고 좋은것만은 아니며, 오히려 리소스 낭비가 된다.
이 내용은 각 사이트에 맞게 테스트하여 설정해야 하겠다.
그럼 ArraySize가 늘면서 블록I/O가 감소하는 원리는?
아래 그림은 책의 예시이며, 10개의 행으로 구성된 3개의 블럭이 있다고 가정한다.
- 총 30개의 레코드이므로 ArraySize를 3으로 설정하면 Fetch 횟수는 10이고, Block I/O는 12번 발생한다.
- 1번블록 : 2~4번째 Fetch에서 반복 엑세스(흠... 1번째 Fetch는 한번만 읽는가?? 맞는듯...)
- 2번블록 : 4~7번째 Fetch에서 반복 엑세스
- 3번블록 : 7~10번째 Fetch에서 반복 엑세스
- 만약 ArraySize를 30으로 설정하면 Fetch횟수는 1로 줄어든다.
프로그램 언어에서 Array 단위 Fetch 기능 활욜
지금까지는 SQL*Plus 중심으로만 설명했는데, PL/SQL을 포함한 프로그램 언어에서 어떻게 ArraySize를 제어하는지 확인하자.
Cursor FOR Loop문을 사용할 경우
- 9i까지(4절 Array Processing의 Bulk Collect 구문을 사용하지 않는 한) 한 로우씩 처리
- 10g부터 자동으로 100개씩 Array Processing
Cursor FOR Loop문의 커서
- Open, Fetch, Close가 내부적으로 일루어지는 것이 특징
- Implicit Cursor FOR Loop, Explicit Cursor FOR Loop 두가지 형태가 있다. (두개 다 Array Fetch 효과 얻음)
Implicit Cursor FOR Loop
declare
l_object_name big_table.object_name%type;
begin
for item in ( select object_name from big_table where rownum <= 1000 )
loop
l_object_name := item.object_name;
dbms_output.put_line(l_object_name);
end loop;
end;
/
Explicit Cursor FOR Loop
declare
l_object_name big_table.object_name%type;
cursor c is select object_name from big_table where rownum <= 1000;
begin
for item in c
loop
l_object_name := item.object_name;
dbms_output.put_line(l_object_name);
end loop;
end;
/
-- sql트레이스는 동일하다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 24 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 24 0 1000
Cursor FOR Loop가 아닌 일반 커서사용
declare
cursor c is
select object_name
from test where rownum <= 1000;
l_object_name test.object_name%type;
begin
open c;
loop
fetch c into l_object_name;
exit when c%notfound;
dbms_output.put_line(l_object_name);
end loop;
close c;
end;
-- sql 트레이스 결과
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1001 0.00 0.00 0 1003 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1003 0.00 0.00 0 1003 0 1000
위의 결과 Cursor FOR Loop를 사용하지 않으면 Array단위 Fetch가 작동하지 않음을 알 수 있다.
- JAVA 프로그램에서 ArraySize를 조정하는 방법.(앞에서 이미 했다..)
String sql = "select id,pw from customer";
PreparedStatment stmt = conn.prepareStatment(sql);
Stmt.setFetchSize(100); //여기!!!
ResultSet rs = stmt.executeQuery();
// rs.setFetchSize(100); -- ResultSet에서 조정할 수도 있다.
while(rs.next()){
......
}
rs.close();
stmt.close();
- JAVA에서 FetchSize 기본 값은 10이다.(앞에서 이미 했졍)
- 대량 데이터를 Fetch 할 때 이 값을 100~500 정도로 늘려 주면 기본 값을 사용할 때보다 데이터베이스 Call 부하를 1/10 ~ 1/50로 줄일 수 있다.
- FetchSize를 100으로 설정했을 때 데이터를 Fetch 해오는 메커니즘은 아래와 같다.
- 최초 rs.next() 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
- 이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
- 버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
- 모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.