Fetch Call 최소화 원리 요약

• 부분범위처리 원리
• OLTP 환경에서 부분범위 처리에 의한 성능 개선원리
• Array Size 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
• 프로그램 언어에서 Array 단위 Fetch 기능 활용

(1) 부분범위 처리 원리

23:27:14 SQL> set time on; set timing on; set lines 512 pages 9999; 23:27:17 SQL> 23:27:17 SQL> 23:27:17 SQL> 23:27:17 SQL> 23:27:18 SQL> create table t (    x   number  not null,    y   number  not null    ); 23:27:24   2  23:27:24   3  23:27:24   4   Table created. Elapsed: 00:00:00.00 23:27:24 SQL> insert into t select * from (        select rownum x, rownum y        from   dual        connect by level <= 5000000 ) order by dbms_random.value ;   23:27:28   2  23:27:28   3  23:27:28   4  23:27:28   5  23:27:28   6  23:27:28   7  23:27:28   8  23:27:28   9   5000000 rows created. Elapsed: 00:00:34.20 23:28:03 SQL> 23:28:03 SQL> alter table t add constraint t_pk primary key (x);   Table altered. Elapsed: 00:00:06.32 23:28:32 SQL> 23:28:32 SQL> 23:28:32 SQL> 23:28:32 SQL> alter system flush buffer_cache; alter system flush buffer_cache * ERROR at line 1: ORA-01031: insufficient privileges Elapsed: 00:00:00.00 23:28:37 SQL> set arraysize 5 23:29:19 SQL> select /*+ index(t t_pk) */ x, y from   t where  x >  0 and    y <= 6 ; 23:29:25   2  23:29:25   3  23:29:25   4           X          Y ---------- ----------         1          1         2          2         3          3         4          4         5          5 20초 정도 대기함.         6          6 6 rows selected. Elapsed: 00:00:20.85

• x,y 칼럼 값은 동일. x만 PK 로 인덱스 생성.
• 전체 데이터 중 결과 셋에 부합하는 레코드는 500만건 중 6건
• 5건을 먼저 처리하여 출력한 이후 나머지 레코드 전체를 탐색후 마지막 결과 레코드 1건을 출력함.
• (ArraySize 5 설정)

＊부분범위처리

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

※ 주의 : 결과 집합을 버퍼 캐시에 모두 적재한뒤에 사용자에게 전송하는 형태가 아님

* 부분범위처리의 적정 단위

적정 운반단위 (arraysize)는 각 자료의 양과 필요양에 따라 적절하게 선택되어져야함

네트워크를 통해서 전송해야할 데이터양에 따라 크기 변경 필요.

대량 데이터 : arraysize 大. 전송해야할 총량은 동일하기 때문에 fetch call 감소

일부 데이터 : arraysize 小. 결과 집합의 일부만을 가지고 처리는 경우 불필요한 데이터 전송 낭비를 막음.

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

x>0 and y<=6’  조건으로 쿼리를 수행
* 첫번째 Fetch Call
1) 인덱스를 따라 x 칼럼 값이 1~5인 5개 레코드를 전송받아 Array 버퍼에 넣음.
2) x,y 칼럼의 같기 때문에 y<=6 테이블 필터 조건도 만족
3) 오라클 서버는 arraysize 인 5건의 정보를 찾았기 대문에 클라이언트에 해당 결과 전송
4) array 버퍼에 담긴 5건의 레코드를 화면에 바로 출력

* 두번째 Fetch Call
5) 두번째 Fetch Call 명령을 받자마자 x=6 인 레코드를 찾아서 Oracle Net 으로 내림.
   (현 시점 Oracle 은 남은 레코드에서 ‘x>0 and y <= 6’ 조건을 만족하는 레코드가 더 없을지는 모름
6) 마지막 레코드까지 탐색한 후에 전송할 데이터가 없음을 확인하고 한건 전송을 Oracle Net 에 명령
7) Oracle Net 은 한건만 담은 패킷을 클라이언트에 전송

00:16:22 SQL> select /*+ index(t t_pk) */ x, y from   t where  x >  0 and    y <= 1 ;00:16:23   2  00:16:23   3  00:16:23   4           X          Y ---------- ----------         1          1 Elapsed: 00:00:21.45

조건을 바꾸어 array size 자체보다 적은 resultset 구성을 하면 아예 최초 쿼리부터 결과를 받는데 오래 걸린다는 사실을 확인 할 수 있다.

00:20:42 SQL> select /*+ index(t t_pk) */ x, y from   t where  x >  0 and    mod(y,50) = 0; 00:20:43   2  00:20:43   3  00:20:43   4           X          Y ---------- ----------        50         50       100        100       150        150       200        200       250        250       300        300       350        350       400        400       450        450       500        500       550        550       600        600       650        650       700        700       750        750       800        800       850        850 00:21:21 SQL> select /*+ index(t t_pk) */ x, y from   t where  x >  0 and    mod(y,50000) = 0;00:21:25   2  00:21:25   3  00:21:25   4           X          Y ---------- ----------     50000      50000    100000     100000    150000     150000    200000     200000    250000     250000    300000     300000    350000     350000    400000     400000    450000     450000    500000     500000    550000     550000    600000     600000    650000     650000    700000     700000    750000     750000

첫번째 쿼리 : 처음부터 끝까지 쉬지않고 결과를 출력
두번째 쿼리 : Array Size 5를 채우는 시간 간격이 있기 대문에 출력 시간에 차이가 발생함.

※ OLTP 성 업무에선 쿼리 결과 집합이 아주 많더라도 그 중 일부만 Fetch 하고 멈출때가 있음.
   부분 범위 처리가 가능한 업무에 한해서 출력 대상 건수가 많을 수록 응답시간이 짧아지는 효과를 볼 수 있음.


참고) SQL*Plus One-Row Fetch
SQL*Plus를 사용하게 되면 Array size에 상관없이 첫번째 Row를 Fetch하는 과정이 추가됨

(3) ArraySize 조정에 의한 Fetch Call 감소 및 블록I/O 감소 효과

대량의 데이터를 처리할때 ArraySize를 크게 설정할수록 Fetch Call 횟수가 줄어 네트워크 부하가 감소하고 쿼리 성능이 향상되고  서버프로세스가 읽을 블록 개수가 줄어들게된다.

00:21:30 SQL> create table test as select * from all_objects; Table created. Elapsed: 00:00:09.91 SQL> set autotrace traceonly statistics; SQL> set arraysize 2; SQL> select * from scott.test; 48122 rows selected. Statistics ----------------------------------------------------------         21  recursive calls          0  db block gets      24524  consistent gets        696  physical reads          0  redo size    9470745  bytes sent via SQL*Net to client     265184  bytes received via SQL*Net from client      24062  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      48122  rows processed

전체 48122 건의 데이터를 24062 번의 fetch call 을 이용해서 조회함. (1 Fetch 시 1.99건의 레코드)

00:43:00 SQL> 00:43:00 SQL> set autotrace traceonly statistics;    set arraysize 5; select * from scott.test; 00:47:12 SQL> 00:47:12 SQL> 00:47:12 SQL> 00:47:12 SQL> 48122 rows selected. Elapsed: 00:00:01.16 Statistics ----------------------------------------------------------          0  recursive calls          0  db block gets      10183  consistent gets          0  physical reads          0  redo size    6785649  bytes sent via SQL*Net to client     106388  bytes received via SQL*Net from client       9626  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      48122  rows processed 00:47:13 SQL> 00:47:13 SQL>

ArraySize 변경을 하면서 실행 통계를 수집하면 아래와 같이 결과를 얻을 수 있다.



ArraySize 를 키운다고 같은 비율로 Fetch Count가 줄어들지는 않음.
ArraySize 100 이후 부터 급격하게 Fetch Call 감소양이 줄어들기 때문에 100정도가 적당한 값.

ArraySize 에 따른 Block I/O 변동

10개의 레코드로 구성된 3개의 블록을 가정.
총 30개의 레코드를 대상으로 쿼리가 수행되고 ArraySize가 3이라면 3개씩 10번의 Fetch가 일어나게 되고
I/0는 블록별 3+3+3+1 / 2+3+3+2 / 1+3+3+3  형태로 레코드를 가져가므로 총 12번의 블록 i/o가 발생.
Arraysize가 10이라면 3번 Fetch Call / 3회의 Block I/O 발생
Arraysize가 30이라면 1번 Fetch Call / 3회의 Block I/O 발생

(4) 프로그램 언어에서 Array 단위 Fetch 기능 활용

PL/SQL 에서 커서를 열고 레코드를 Fetch

9i이전  : 한번에 한로우만 처리 (Bulk Collect 미사용 환경)

10g이후 : 자동으로 100개씩 Array Fetch (Cursor FOR Loop 에서만)

“

for item in cursor

loop

… …

end loop;

“

* 사전 준비

SQL> conn scott/tiger; Connected. SQL> create table big_table 2  as  3  select level object_name from dual  4  connect by level <2000;    Table created.

* Implicit Cursor For Loop

00:47:13 SQL> 00:47:13 SQL> alter session set sql_trace=true; Session altered. Elapsed: 00:00:00.00 01:02:43 SQL> declare l_object_name scott.big_table.object_name%type; begin  for item in ( select object_name from scott.big_table where rownum <= 1000 )  loop    l_object_name := item.object_name;    dbms_output.put_line(l_object_name);  end loop; end; /01:03:13   2  01:03:13   3  01:03:13   4  01:03:13   5  01:03:13   6  01:03:13   7  01:03:13   8  01:03:13   9  01:03:13  10   PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 devodbt1:[/oracle/oradev/diag/rdbms/oradev/ORADEV/trace]tkprof ORADEV_ora_63439162.trc report TKPROF: Release 11.2.0.3.0 - Development on Tue May 15 01:04:45 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. devodbt1:[/oracle/oradev/diag/rdbms/oradev/ORADEV/trace]cat report.prf TKPROF: Release 11.2.0.3.0 - Development on Tue May 15 01:04:45 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Trace file: ORADEV_ora_63439162.trc Sort options: default ******************************************************************************** count    = number of times OCI procedure was executed cpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executing disk     = number of physical reads of buffers from disk query    = number of buffers gotten for consistent read current  = number of buffers gotten in current mode (usually for update) rows     = number of rows processed by the fetch or execute call ******************************************************************************** SQL ID: 4tk6t8tfsfqbf Plan Hash: 0 alter session set sql_trace=true call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        0      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        1      0.00       0.00          0          0          0           0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** declare l_object_name scott.big_table.object_name%type; begin  for item in ( select object_name from scott.big_table where rownum <= 1000 )  loop    l_object_name := item.object_name;    dbms_output.put_line(l_object_name);  end loop; end; 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           1 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.00       0.00          0          0          0           1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** SQL ID: 34puyrbt9t87n Plan Hash: 701644536 SELECT OBJECT_NAME FROM SCOTT.BIG_TABLE WHERE ROWNUM <= 1000 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         13          0        1000 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total       13      0.00       0.00          0         13          0        1000 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS   (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------      1000       1000       1000  COUNT STOPKEY (cr=13 pr=0 pw=0 time=430 us)      1000       1000       1000   TABLE ACCESS FULL BIG_TABLE (cr=13 pr=0 pw=0 time=299 us cost=3 size=25987 card=1999) ********************************************************************************

* Explicit Cursor For Loop

01:10:37 SQL> declare l_object_name scott.big_table.object_name%type;  cursor c is select object_name from scott.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; /01:10:38   3  01:10:38   4  01:10:38   5  01:10:38   6  01:10:38   7  01:10:38   8  01:10:38   9  01:10:38  10  01:10:38  11   PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 01:10:38 SQL> ******************************************************************************** declare l_object_name scott.big_table.object_name%type;  cursor c is select object_name from scott.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; 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           1 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.00       0.00          0          0          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** SQL ID: d270h271pxjd3 Plan Hash: 701644536 SELECT OBJECT_NAME FROM SCOTT.BIG_TABLE WHERE ROWNUM <= 1000 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         13          0        1000 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total       13      0.00       0.00          0         13          0        1000 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS   (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------      1000       1000       1000  COUNT STOPKEY (cr=13 pr=0 pw=0 time=695 us)      1000       1000       1000   TABLE ACCESS FULL BIG_TABLE (cr=13 pr=0 pw=0 time=188 us cost=3 size=25987 card=1999) ********************************************************************************

* 일반 커서 사용

01:19:49 SQL> cursor c is  select object_name  from scott.big_table where rownum <= 1000;  l_object_name scott.big_table.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; /SP2-0734: unknown command beginning "cursor c i..." - rest of line ignored. 01:19:49 SQL> ******************************************************************************** SQL ID: d270h271pxjd3 Plan Hash: 701644536 SELECT OBJECT_NAME FROM SCOTT.BIG_TABLE WHERE ROWNUM <= 1000 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        2      0.00       0.00          0          0          0           0 Execute      2      0.00       0.00          0          0          0           0 Fetch     1012      0.00       0.00          0       1015          0        2000 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total     1016      0.00       0.00          0       1015          0        2000 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS   (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------      1000       1000       1000  COUNT STOPKEY (cr=13 pr=0 pw=0 time=695 us)      1000       1000       1000   TABLE ACCESS FULL BIG_TABLE (cr=13 pr=0 pw=0 time=188 us cost=3 size=25987 card=1999)

* Java 프로그램에서 ArraySize 조정의 예제

import java.sql.*; import java.util.*; public class OracleFetchTest {    public OracleFetchTest() {    }    public static void main(String[] args) { Connection conn = null; System.out.println("SQL Test"); // Loading Oracle Driver Class try {    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); } catch (Exception e) {    System.out.println(e);    System.exit(0); } // Get Connection from Oracle DB Server try {    conn = java.sql.DriverManager    .getConnection("jdbc:oracle:thin:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=42.8.164.53)(PORT=2012))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=ORADEV)))"); } catch (Exception e) {    System.out.println(e);    System.exit(0); } System.out.println("Connection established"); // Query Execution try {    Statement stmt = conn.createStatement();    stmt.setFetchSize(100); // Statement 에서 조정    ResultSet rs = stmt    .executeQuery("SELECT OBJECT_NAME FROM SCOTT.BIG_TABLE WHERE ROWNUM <= 1000");    // rs.setFetchSize(100); // ResultSet 에서 조정도 가능함    while (rs.next()) { System.out.println(rs.getString("OBJECT_NAME")); StringTokenizer Tok = new StringTokenizer( rs.getString("OBJECT_NAME"), "-"); int n = 0; while (Tok.hasMoreElements())    System.out.println("" + ++n + ": " + Tok.nextElement());    } } catch (Exception e) {    System.out.println(e);    System.exit(0); }    } }

Java FetchSize 기본값은 10.
대량 데이터 로드시 이 값을 100~500 정도로 늘려주면 기본값을 사용할때보다 Call 부하를 1/10 ~ 1/50으로 줄이는게 가능함.