20180226 Fetch Call

부분 범위 처리

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

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

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

클라이언트에게 전송할 할당량을 다 채웠더라도 오라클은 모르기 때문에 해당 Arraysize까지 다 찰때까지 데이터를 스캔한다.

그림으로 비유하면 아래와 같다.

대량의 데이터를 내려받을 때 Arraysize를 크게 설정하면 두가지 이점이 있다고 한다.

(1) Fetch call 횟수가 줄어 네트워크 부하가 감소하고 쿼리 성능이 향상된다.

(2) 서버 프로세스가 읽어야 할 블록 개수 감소

테이블을 만들었다. 해당 테이블 DOUBLE_TB 에는 17만건의 데이터가 존재한다.

SQL> SELECT count(*) FROM DOUBLE_TB;
 
  COUNT(*)
----------
    175799

여기서 set arraysize [array_size] 을 줄 수 있으며 해당 array_size의 최대값은 5000이다. 우선적으로 10을 주고 set autotrace trace stat 을 통해 쿼리를 실행시키고 실행통계를 얻어오자.

SQL> set arraysize 10
SQL> set autotrace trace stat
SQL> SELECT * FROM DOUBLE_TB;
 
175799 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      20246  consistent gets
          0  physical reads
          0  redo size
   24500469  bytes sent via SQL*Net to client
     193893  bytes received via SQL*Net from client
      17581  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     175799  rows processed

내용을 살펴보자.

- 20번째 줄 : 175799개의 로우를 가져왔다. (rows processed)

- 12번째 줄 : 20246개의 블록을 읽었다. (consistent gets)

- 17번째 줄 : 17581 는 Fetch count이다.

전체 데이터 수 / Fetch count = 175799 / 17581 = 9.99937XXX 이렇게 나온다. 약 10의 값이다. 따라서 1회 Fetch 할 때마다 10개씩 읽는 것을 확인할 수 있다.

이제 ArraySize 를 조절해서 내용을 확인해보자. 여기서 신경써야할 부분을 총 세가지이다.

(1) rows processed - 가져온 로우의 수

(2) consistent gets - 읽은 블록의 수

(3) SQL*Net roundtrips to/from client - Fetch Count (횟수)

- set arraysize 500

SQL> set arraysize 500
SQL> SELECT * FROM DOUBLE_TB;
 
175799 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3309  consistent gets
          0  physical reads
          0  redo size
   21296061  bytes sent via SQL*Net to client
       4385  bytes received via SQL*Net from client
        353  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     175799  rows processed

- set arraysize 1000

SQL> set arraysize 1000
SQL> SELECT * FROM DOUBLE_TB;
 
175799 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3134  consistent gets
          0  physical reads
          0  redo size
   21263325  bytes sent via SQL*Net to client
       2449  bytes received via SQL*Net from client
        177  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     175799  rows processed

- set arraysize 5000

SQL> set arraysize 5000
SQL> SELECT * FROM DOUBLE_TB;
 
175799 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2998  consistent gets
          0  physical reads
          0  redo size
   21237285  bytes sent via SQL*Net to client
        909  bytes received via SQL*Net from client
         37  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     175799  rows processed

이렇게 arraysize 를 조절하면서 확인한 결과 Fetch Count(SQL*Net roundtrips) 가 계속해서 줄어드는 것을 확인할 수 있다. 10에서 500으로 갈때에는 블록 I/O(consistent gets) 가 크게 감소하는 것을 확인 가능하지만 그 이후부터는 감소하는 폭이 미비하다.

아래의 그림은 arraysize 와 block I/O 의 내용을 그림으로 표현한 것이다.

정리

(1) 대량의 데이터 파일을 전송한다면 arraysize를 크게하여 fetch call 횟수를 줄여주는 것이 유리

(2) 소량의 데이터 파일은 arraysize를 작세 설정하는 것이 유리

(3) arraysize 를 크게 준다고하여도 Fetch Count 와 Block I/O 가 같은 비율로 줄어들지 않는다.

(4) arraysize 를 무작정 키우다가는 리소스를 낭비할 수 있다.

결과적으로 조회할 데이터가 일정량 이상이고 수행빈도가 높다면 필수적으로 페이지 처리 구현을 해야한다.

자바 프로그램에서 ResultSet 에 setFetchSize() 메소드를 통해 arraysize를 조절가능하다. 아래에는 해당 내용이다.

try {
    // 조회 쿼리
    preparedStatement = connection.prepareStatement(query);
    resultSet = preparedStatement.executeQuery();
            
    System.out.println("OracleRemoteConnector : resultSet.setFetchSize(10000)");
    resultSet.setFetchSize(10000);
            
    while(resultSet.next()){
        String docSeq = String.valueOf(resultSet.getInt(DOC_SEQ));
        String title = resultSet.getString(TITLE);
        String registerDate = resultSet.getString(REG_DT);
                
        String[] rowDatas = {docSeq, title, registerDate};
        allRowsData.add(rowDatas);
    }
} 
catch (SQLException e) {
    e.printStackTrace();
}

순서

1. 최초 resultSet.next() 호출 시 한번에 setFetchSize() 개수만큼 가져와서 클라이언트 Array 버퍼에 캐싱한다.

2. 이후 resultSet.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.

3. 버퍼에 캐싱되어있는 데이터를 모두 소진한 후 setFetchSize() 개수 + 1이 되는 시점, 즉 새로운 데이터가 필요로 하는 경우 resultSet.next() 호출 시 다시 해당 개수만큼 들고온다.

4. 모든 결과 집합을 다 읽을 때까지 2번과 3번의 과정을 반복한다.

[ 참고링크 ]

[ SQL PLUS - Array size ]

[ 제 3절 데이터베이스 Call과 네트워크 부하 ]

[ Fetch Call 최소화 ]