Fetch Call 최소화

부분범위처리의 원리

결과집합을 전송할 때, 전체 데이터를 쉼 없이 연속적으로 처리하지 않고 사용자로부터 Fetch Call이 있을때마다 일정량씩 나누어서 전송하는 것을 말한다.

DBMS는 데이터를 클라이언트에게 전송할 때 일정량씩 나누어 전송하며, 오라클의 경우 ArraySize(또는 FetchSize) 설정을 통해 운반단위를 조절한다.
그리고 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 클리이언트로부터 추가Fetch Call을 받기 전까지는 그대로 멈춰 서서 기다린다.

또한 클라이언트에게 전송할 량이 끝났어도(오라클은 끝났는지 모름) 운반단위가 차지 않으면 운반단위가 다 찰때까지 대기한후, 끝까지 다 읽은 후에야 더이상의 데이터가 없음을 인지하고 데이터의 나머지를 내보낸다.

이러한 부분범위 처리는 전체 일량을 줄이는 것과는 상관이 없지만, OLTP환경등 일부 데이터만 먼저 보여지고자 할때 유용하게 사용할 수 있다.

h5.※ OLTP(online transaction processing) : 온라인 업무의 처리 형태의 하나이다. 터미널에서 받은 메시지를 따라 호스트가 처리를 하고, 그 결과를 다시 터미널에 되돌려주는 방법을 말한다.

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

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 해오는 메커니즘은 아래와 같다.

1. 최초 rs.next() 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
2. 이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
3. 버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
4. 모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.