05 Fetch Call 최소화

05 Fetch Call 최소화

1) 부분범위처리 원리
# 1억건의 테이블 조회
- 쿼리툴에서 ( sql developer, Orange, Toad 등등 ) select 명령으로 1억건의 테이블 조회시 엄첨 빠르게 나온다.

# 1억건의 테이블 조회가 빠르게 수행되는 이유
- Fetch size 만큼 일단 사용자에게 출력해주고 ( Fetch size가 1000이라면 쿼리툴에 일단 상위 1000 line만 출력됨 )
  ,사용자가 Fetch call을 유발하는 행동 ( 마우스 스크롤을 하여, 1000 line 이후 라인을 보려는 행동 )을 하면
  다음 Fetch size 만큼 일단 사용자에게 출력해주기 때문에 빠르다. 즉 Fetch size만큼씩만 사용자에게 우선적으로
  보여주고 전체 요청내용을 바로 보여주지 않기 때문에 빠른것이다. 이렇게 Fetch size 만큼씩 사용자에게 순차적으로
  결과를 전달하는것이 부분범위 처리이다.

  이처럼 쿼리 결과집힘을 전송할 때 전체 데이터를 쉽 없이 연속적으로 처리하지 않고 시용자로부터 Fetch Call이
  있을 때마다 일정량씩 나누어서 전송하는 것을 이른바 '부분범위처리'라고 한다

# 적당한 Array size ( Fetch size )
- 서비스 용도 ( 실제 서비스 사용자가 서비스에 따라 몇 건정도 빠르게 보고 서비스를 이용하는 것인지 ) 파악해
  Array size ( Fetch size )를 지정하는것이 적당하다.

# Array buffer
- 서버에서 출력된 데이터를 담는 메모르 공간을 Array buffer라하며 보통 Client 서버에 이러한 Array buffer가 생성된다.

# SDU, TDU
- 오라클에서 데이터를 전송히는 단위는 Array Size( Fetch size )에 의해 결정
- Array Size( Fetch size )단위로 전송이 될 때 데이터는 네트워크 패킷단위로 나뉘어 여러번 걸쳐 나누어 전송된다.
  Array Size( Fetch size )단위로 전송되는 부분범위 처리안에서, 네트워크 패킷단위로 나뉘어
  또 일종의 부분범위 처리로 데이터가 전송된다.

  ArraySize가 100이고 한 로우당 1MB를 차지한다면 한번 Fetch 할 때마다 100MB를 전송해야 하는데，
  이를 하나의 패킷으로 묶어 한 번에 전송하지는 않는다. 네트워크를 통해 큰 데이터를 전송할 때는 작은 패킷들로
  단펀화해야 하며, 그래야 유실이 나 에러가 발생했을 때 부분 재전송을 통해 복구할 수 있다.

---------------------------------------------------------------------------------------------------------------------------------
                                                                        OSI7 계층

   
---------------------------------------------------------------------------------------------------------------------------------

  오라클 서버와 클리이언트는 OSI7 계층에서 Application 레이어에 위치하며, 그 아래에 있는 레이어를 통해 데이터를
  전달하여 다른 서버간 데이터를 주고받는다. SDU( Session Data Unit)는 Session 계층 데이터 벼퍼에 대한 규격으로서 
  네트워크를 통해 전송하기 전에 Oracle Net이 데이터를 담아 두려고 사용하는 버퍼다.

  예를 들어 ArraySize를 5로 설정하면 
  - 클라이언트 측에는 서벼로부터 전송받은 5개 레코드를 담을 Array buffer를 할당한다. 
  - 서버 측에서는 Oracle Net으로 데이터를 내려 보내다가 5건당 한 번씩 전송 명령을 전송하고 클라이언트로부터 
    다음 Fetch Call을 기다리는 과정이 있는데 Oracle Net이 서버 프로세스로부터 전송명령을 받을 때까지 데이터를
    버퍼링(Session 계층으로 내려 쓰는)하는 곳이 SDU다. 
  - Oracle Net은 서버 프로세스로부터 전송요청을 받기 전에라도 SDU가 다 차면 버퍼에 쌓인 데이터를 하위 계층에
    전송하는데, 이때는 클라이언트로부터 Fetch Call을 기다리지 않고 곧이어 데이터를 받아 SDU를 계속 채워 나간다.

  TDU(Transport Data Unit)는 Transport 레이어 데이터 버퍼에 대한 규격이다.
  물리적인 하부 레이어로 내려보내기 전에 데이터를 잘게 쪼개어 클라이언트에게 전송되는 도중에 유실이나 에러가
  없도록 제어하는 역할을 한다.
  SDU와 TDU 사이즈는 TNSNAMES.ORA, LISTENER.ORA 파일에서 아래와 같이 설정 가능하며, 이들의
  기본설정값은 2KB다. (SDU=2048)(TDU=2048)

   ex) 결과집합        : 18 건
        각 로우 용량   : 900 byte
        Array size       : 5 건
        SDU              : 2048 byte
        TDU              : 1048 byte
     1) Client 서버에 Array buffer(5건 공간) 생성 
     2) 5건 * 900 byte = 4500 byte
     3) 4500 byte의 데이터가 순차적으로 SDU에 전달
     4) SDU에 4500 byte의 데이터가 전달되다가 SDU에 2048 byte가 다 차게 된다면
     5) TDU로 데이터가 전송되기 시작한다.
     6) TDU에 1048 byte가 다 차게된다면, Network 계층으로 1048 byte의 데이터가 넘어가고,
     7) Network 계층, Datalink 계층, Physical 계층을 통해 Client server에 1048 byte의 데이터가 넘어가게 된다.
     8) Client server에 넘어간 1048 byte의 데이터는  Array buffer에 쌓이게 된다.     
      fetch1 : (1048 + 1000) + (1048 + 1000) + 404 = 4,500 ( 5개 패킷 )
      fetch2 : (1048 + 1000) + (1048 + 1000) + 404 = 4,500 ( 5개 패킷 )
      fetch3 : (1048 + 1000) + (1048 + 1000) + 404 = 4,500 ( 5개 패킷 )
      fetch4 : (1048 + 1000) = 2,700 ( 2개 패킷 )     
      패킷은 헤더 정보를 포함하므로 패킷 단편화를 줄이면 네트워크 트래픽도 줄어들게 된다.

2) OLTP환경에서 부분범위처리에 의한 성능개선 원리

# Fetch 출력과정 정리
  - Server Process에서 Fetch size만클 Fetch
  - Fetch된 데이터가 패킷으로 나뉘어 Client Process에 전송
  - Client Process에서 Array buffer에 Array size만큼 다차게 되면 출력

# Array buffer를 빨리 채울수록 성능이 좋다
[1] select /*+ index(t t_pk) */ * from t where x> 0 and mod(y, 50) = 0
[2] select /*+ index(t t_pk) */ * from t where x> 0 and mod(y, 50000) = 0
- Fetch size ( & Array size )가 10일 때 1번 쿼리는 인덱스를 통해 테이블에 500건에 대해 접근했을 때 
  Client 프로세스의 Array buffer가 다채워져 출력된다.
- Fetch size ( & Array size )가 10일 때 2번 쿼리는 인덱스를 통해 테이블에서 500000건에 대해 접근했을 때
  Client 프로세스의 Array buffer가 다채워져 출력된다.
- 즉 2번 쿼리가 1번 쿼리보다 490500건을 더 읽고 사용자에게 출력이 되므로 2번 쿼리가 더 느리다.
- 동일한 테이블에서 결과집합의 갯수가 많을 수록 Array buffer가 빨리 채워진다.
  즉, Array buffer가 빨리 채워질수 있도록 쿼리를 작성 해야한다.

# SQL PLUS
 - one row fetch
   SQL PLUS에는 처음 Fetch시에만 Array size만큼 Fetch되지 않고, 
   1개의 로우만 fetch가 되어 Array buffer에 담기게 된다. 두번 째 Fetch부터는 Array size만큼 Fetch가 된다.

# Array size가 5일 때, Array buffer가 다 찰때 까지 대기
 - SQLPLUS를 사용해 Select 구문으로 1건 출력시 : 2번의 Fetch 발생
     1번째 Fetch 1개 로우가 Array buffer에 전송 - 1건 쌓임 대기
     2번째 Fetch 0개 로우가 Array buffer에 전송 - 나머지 데이터 모두 검색 후 더는 전송할 데이터가 없음을 인식하고 
                                                                그대로 1건만 전송하도록 Oracle Net에 명령을 보냄, Oracle Net은 
                                                                한 건만 담은 패킷을 클라이언트에게 전송          
   - SQLPLUS를 사용해 Select 구문으로 6건 출력시 : 3번의 Fetch 발생
     1번째 Fetch 1개 로우가 Array buffer에 전송 - 1건 쌓임
     2번째 Fetch 5개 로우가 Array buffer에 전송 - 기존 1건 이후에 Array buffer에 4건 쌓여 출력 후, 출력되지 않은 
                                                                나머지 1건 쌓임
     3번째 Fetch 0개 로우가 Array buffer에 전송 - 나머지 데이터 모두 검색 후 더는 전송할 데이터가 없음을 인식하고
                                                                그대로 1건만 전송하도록 Oracle Net에 명령을 보냄, Oracle Net은
                                                                한 건만 담은 패킷을 클라이언트에게 전송                               
- Array Size 조정에 의한 Fetch Call 감소 및 블록 I/O 감소효과
  >> Array Size가 클수록 Block I/O가 작아지지만, 어느정도 임계점이 있다.
       Row Data의 사이즈에 따라. Block I/O가 작아질수 있는 적정 Array Size를 정하는것이 좋다.
 [1] Array size 3일 경우 - Block I/O : 10회, Fetch Call 10회

 [2] Array size 5일 경우 - Block I/O : 6회, Fetch Call 6회

 [3] Array size 10일 경우 - Block I/O : 3회, Fetch Call 3회

 [4] Array size 30일 경우 - Block I/O : 3회, Fetch Call 1회

 [5] Array size 40일 경우 - Block I/O : 3회, Fetch Call 1회


3) 프로그램 언어에서 Array 단위 Fetch기능 활용
[1] JAVA 
 >> 자바에서 Array size 지정시 사용하는 구문 : var.setFetchSize(100);  
---------------------------------------------------------------------------------------------------------------------------------
   rs.setFetchSize(100);
   while ( rs.next() ) { 
      int empno = rs.getInt (l);
      String ename = rs.getString(2);
      System.out.println (empno + ":" + ename);
   }   
---------------------------------------------------------------------------------------------------------------------------------

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

[2] PL/SQL
# PL/SQL에서 Cursor For loop 구문이 있다면 / 없다면
                        Array size가 자동으로 100 / 1이 된다.
---------------------------------------------------------------------------------------------------------------------------------
# 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;
/

# Non Cursor For Loop
declare 
   cursor c is select object_name from big_table where rownum <= 1000;
   l_object_name big_table.object_name%type;
begin
   open c;
   loop 
      fetch c into 1_object_name;
      exit when c%notfound;
      dbms_output.put_line(l_object_name);
   end loop;
   close c;
end;
/
---------------------------------------------------------------------------------------------------------------------------------