5._Fetch_Call_최소화

조회 수 3334 추천 수 0 2013.09.06 23:29:12
박영창 *.101.40.130

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/tracetkprof 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/tracecat 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으로 줄이는게 가능함.


박영창

2012.05.15 01:47:15
*.101.40.130

구글 Docs 링크 ) https://docs.google.com/document/d/14jE7pW9WnPVijRdUWqvIcTQFINjQ_vwV9iaghi36cCU/edit