티스토리 뷰

  1. 테스트 Idea

우리가 Oracle에 SQL을 수행시키게 되면 Oracle에서는 내부적으로 Parse, execute, Fetch라는 과정을 통해 결과를 얻게 된다. 이를 JDBC를 통해 사용할 때 크게 관심을 두지 않는 것 중 하나가 바로 Fetch count이다. 그러나 실상을 따져 보면 전체 SQL시간 중 많은 부분이 fetch에서 소요되는 경우가 많기 때문에 Fetch count를 줄이는 것은 곧 튜닝이 될 수도 있다.

요즈음에는 OLTP, DSS의 개념이 상당히 모호해 졌기 때문에 Web환경에서도 10000건 이상의 결과를 받아내는 SQL을 심심치 않게 찾아 볼 수 있다. 그러나 10000건을 가져올 때 서버와 클라이언트 사이에 통신이 1000번이 발생하게 되는 것을 아는 사람은 그리 많지 않은 듯 하다. 일반적으로 fetch size의 default는 10이다. JDBC는 Oracle에서 결과값을 fetch할 때 한꺼번에 모두 가져오는 것이 아니라 부분 범위로 결과를 나누어서 가져오게 되는데 이 단위를 fetch size라고 한다.

그러나 JDBC 2.0에서는 이 fetch size를 조정하여 성능 개선효과를 가져 올 수 있다. 이러한 기능을 prefetch라고 한다. 이 Fetch size의 적용은 Statement Level에서도 가능하고 Result Set Level에서도 가능하다. 또한 Connection 객체를 얻어 올 때 Connection대신 OracleConnection을 얻어온다면 Connection Level에서도 적용이 가능하다.

이 글에서는 우선 Connection, Statement, Resultset 각 Level에서 이를 사용할 경우 Oracle에서 Fetch Size는 어떻게 표시되는 지에 대해서 알아본 후 Prefetch를 사용할 때 성능 개선이 얼마나 좋아지는 지를 Fetch Size별 성능 개선 및 side effect등을 통해 알아보기로 할 것이다.


  1. 테스트 환경
  • 테스트 환경은 2 Tier로 구성하였음

Client : CPU – Intel() T2400 CPU 1.83Ghz
OS – MS Windows XP Home Edition version 2002 Service pack 2
Memory – 2 GB
JVM – java version "1.6.0_01"
Java(TM) SE Runtime Environment (build 1.6.0_01-b06)
JDBC Driver – Oracle JDBC (ojdbc14.jar)
Oracle – Version 10.2.0.1


Server : CPU - Intel(R) Pentium(R) D CPU 3.00GHz
OS - Asianux release 2.0 (Trinity SP2) Kernel 2.6.9-42.7AXsmp on an x86_64
Memory – 2 GB
Oracle - Version : 10.2.0.3
- Total System Global Area 734003200 bytes
- Fixed Size 2075656 bytes
- Variable Size 251659256 bytes
- Database Buffers 473956352 bytes
- Redo Buffers 6311936 bytes


  1. 테스트 Tool

- 테스트를 위해 Java Application을 수행하여 테스트를 진행하고 테스트 마지막에 Oracle 성능 관련 정보를 수집하는 형식으로 구성

- 우선 Persistent라는 테이블 스페이스와 유저를 생성하였다.

SQL:SYS>CREATE TABLESPACE PERSISTENCE DATAFILE '…\PERSISTENCE01_DBF'

SIZE 1024M AUTOEXTEND ON;


SQL:SYS>CREATE USER PERSISTENCE IDENTIFIED BY PERSISTENCE

DEFAULT TABLESPACE PERSISTENCE TEMPORARY TABLESPACE TEMP;


SQL:SYS>GRANT RESOURCE, CONNECT TO PERSISTENCE;


  • 향후 Persistence유저를 이용하여 테스트를 진행하고 Oracle 성능관련 정보를 수집할 것임
  • Oracle 성능관련 정보는 Stat정보와 Wait Event정보, 그리고 10046 Trace가 사용될 것임.
  • 이 정보를 위해 V$Mystat의 내용과 v$session_Event의 정보를 쿼리하여 특정 테이블에 기록.
  • 그러나 V$뷰를 query하는 데 있어서 Oracle의 성능 정보의 왜곡을 막기 위해 부하를 최소화 해야 할 필요가 있기 때문에 V$의 Base뷰인 X$를 Query하는 방식을 사용하기로 했음.
  • 우선 SYS 유저에 다음과 같은 뷰와 Public synonym을 생성한다

SQL:SYS>CREATE OR REPLACE VIEW EXEM$MYSTAT AS
SELECT S.KSUSENUM SID,
D.KSUSDNAM STAT,
S.KSUSESTV VALUE
FROM X$KSUMYSTA S,
X$KSUSD D
WHERE BITAND( S.KSSPAFLG , 1 ) !=0
AND BITAND( S.KSUSEFLG , 1 ) !=0
AND S.KSUSESTN = D.INDX
AND S.KSUSESTN<(
SELECT KSUSGSTL
FROM X$KSUSGIF
)
AND S.INST_ID = USERENV('INSTANCE')
AND S.KSUSESTV > 0;

SQL:SYS>CREATE PUBLIC SYNONYM EXEM$MYSTAT FOR EXEM$MYSTAT;

SQL:SYS>CREATE OR REPLACE VIEW EXEM$SESSION_WAIT AS
SELECT S.KSLESSID SID,
D.KSLEDNAM EVENT,
S.KSLESWTS TOTAL_WAITS,
S.KSLESTMO TOTAL_TIMEOUTS,
ROUND( S.KSLESTIM /10000 ) TIME_WAITED,
ROUND( S.KSLESTIM /( 10000 * S.KSLESWTS ) , 2 ) AVERAGE_WAIT,
ROUND( S.KSLESMXT /10000 ) MAX_WAIT,

S.KSLESTIM TIME_WAITED_MICRO,
D.KSLEDCLASS WAIT_CLASS
FROM X$KSLES S ,
X$KSLED D
WHERE S.KSLESWTS != 0
AND S.KSLESENM = D.INDX
AND S.KSLESSID = USERENV('SID')
AND S.INST_ID = USERENV('INSTANCE');

SQL:SYS>CREATE PUBLIC SYNONYM EXEM$SESSION_WAIT FOR EXEM$SESSION_WAIT;


또한 이 정보를 기록할 테이블과 프로시저를 Persistence유저에 생성해 준다.

SQL:PERSISTENCE>CREATE TABLE STATPROF (
TAG VARCHAR2(32),
SID NUMBER,
STAT VARCHAR2(64),
VALUE NUMBER)

SQL:PERSISTENCE>CREATE TABLE WAITPROF (
TAG VARCHAR2(32),
SID NUMBER,
EVENT VARCHAR2(64),
TOTAL_WAITS NUMBER,
TOTAL_TIMEOUTS NUMBER,
TIME_WAITED NUMBER,
AVERAGE_WAIT NUMBER,
MAX_WAIT NUMBER,
TIME_WAITED_MICRO NUMBER,
WAIT_CLASS VARCHAR2(64))

SQL:PERSISTENCE>CREATE OR REPLACE procedure DB_PROFILER(Tag in varchar2, Total_Elapsed in number, Used_Mem in number) IS
BEGIN

INSERT INTO SPROF SELECT TAG, SID, STAT, VALUE FROM EXEM$MYSTAT;

INSERT INTO WPROF SELECT TAG, SID, EVENT, TOTAL_WAITS,
TOTAL_TIMEOUTS, TIME_WAITED,    AVERAGE_WAIT,
MAX_WAIT, TIME_WAITED_MICRO, WAIT_CLASS FROM EXEM$SESSION_WAIT;

INSERT INTO SPROF SELECT TAG, USERENV('SID'), 'TOTAL ELAPSED TIME', TOTAL_ELAPSED FROM DUAL
UNION ALL
SELECT TAG, USERENV('SID'), 'USED MEMORY', USED_MEM FROM DUAL;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM );
END ;

  • Oracle의 성능 관련 정보는 Connection을 얻어 올 때 10046 Event를 시작하고 connection을 close할 때 10046 Event를 종료, 그리고 Stat, Wait정보를 기록하는 방식으로 수집할 것임
  • 이 부분은 application Level에서 구현할 것임
  • Application은 다음과 같이 생성하였음

/*
* Class : OraUtil
* 설명 : Oracle Connection 객체 반환 생성 및 종결
*/

package exem.oracle.prefetch;

import java.sql.*;
import oracle.jdbc.*;
public class OraUtil {
    public static Connection getConnection(boolean trace){
        Connection conn=null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn= DriverManager.getConnection("jdbc:oracle:thin:@serverip: port:SID", "persistence", "persistence");    
            conn.setAutoCommit(false);
            if (trace) {
                pstmt = conn.prepareStatement("alter session set events '10046 trace name context forever, level 12' ");
                pstmt.execute();
                pstmt.close();
            }            

        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;        
    }    

    public static OracleConnection getOraConnection(boolean trace){
        OracleConnection oraConn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            oraConn= (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@ serverip: port:SID", "persistence", "persistence");    
            oraConn.setAutoCommit(false);
            if (trace) {
                pstmt = oraConn.prepareStatement("alter session set events '10046 trace name context forever, level 12' ");
                pstmt.execute();
                pstmt.close();
            }            

        } catch (Exception e) {
            e.printStackTrace();
        }

        return oraConn;        
    }    

    public static void cutConnection(Connection conn, String tag, boolean trace, boolean profile, long total_elapsed, long used_mem) {
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        try {
            cstmt = conn.prepareCall("{call db_profiler (?,?,?)}" );
            cstmt.setString(1, tag);
            cstmt.setLong(2, total_elapsed);
            cstmt.setLong(3, used_mem);
            if (profile) {    cstmt.execute(); }
            if (trace) {
                pstmt = conn.prepareStatement("alter session set events '10046 trace name context off' ");
                pstmt.execute();
                pstmt.close();
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            try {
                cstmt.close();
             conn.close();

            } catch (Exception e2) {}
        }
    }    

    public static void cutOraConnection(OracleConnection oraConn, String tag, boolean trace, boolean profile, long total_elapsed, long used_mem) {
        CallableStatement cstmt = null;        
        PreparedStatement pstmt = null;
        try {
            cstmt = oraConn.prepareCall("{call db_profiler (?,?,?)}" );
            cstmt.setString(1, tag);
            cstmt.setLong(2, total_elapsed);
            cstmt.setLong(3, used_mem);
            if (profile) {    cstmt.execute(); }
            if (trace) {
                pstmt = oraConn.prepareStatement("alter session set events '10046 trace name context off' ");
                pstmt.execute();
                pstmt.close();
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            try {
                cstmt.close();
                oraConn.close();
            } catch (Exception e2) {}
        }
    }       
   
}

  • 테스트를 수행하는 부분임

/*
* Class : DoTest
* 설명 : 인수에 따라 해당 Method를 호출하여 수행하도록 함
*/

package exem.oracle.prefetch;

import java.sql.Connection;
import oracle.jdbc.OracleConnection;

public class DoTest {

static String act = "";
    public static void main(String[] args) {
        act = args[0]+"_";
        if (args[0].equals("Prefetch")){
            prefetch();
        } else if (args[0].equals("PFSet") && args.length == 4 ){
            prefetchSet( Integer.parseInt(args[1]), Integer.parseInt(args[2]), Integer.parseInt(args[3]));
        }
    }

    public static void prefetch(){
        Connection conn=null;
int[] fetsz = {0, 20, 30, 40, 50, 60, 70, 80, 90, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000 };
        for (int i=0; i<fetsz.length; i++) {
            conn = OraUtil.getConnection(true);
            long chktime = System.currentTimeMillis();    
            Prefetch.prefetchTest(conn, fetsz[i]);
            OraUtil.cutConnection(conn, act+fetsz[i], true, true, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory() );
        }
    }    

    public static void prefetchSet(int connfsz, int pstmtfsz, int rsfsz){
        OracleConnection oraConn=null;
        oraConn = OraUtil.getOraConnection(true);
        long chktime = System.currentTimeMillis();    
        Prefetch.prefetchSetTest(oraConn, connfsz, pstmtfsz, rsfsz);
        OraUtil.cutOraConnection(oraConn, act, true, false, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory() );        
    }

}


  • 실제 Test를 수행하는 Method가 담겨 있는 부분은 다음과 같음

/*
* Class : Prefetch
* 설명 : 실제 세부 테스트를 수행
*/

package exem.oracle.prefetch;

import java.sql.*;
import oracle.jdbc.OracleConnection;

public class Prefetch {
    static PreparedStatement pstmt = null;
    static ResultSet rs = null;
public static void prefetchTest(Connection conn, int fetsz){
     try {
         pstmt = conn.prepareStatement("select * from prefetch");
         if (fetsz>0) { pstmt.setFetchSize(fetsz); }        
         rs = pstmt.executeQuery();
         while(rs.next()) { }
     } catch(Exception e){
         e.printStackTrace();
     } finally {
try {
     rs.close();
pstmt.close();
} catch(Exception e2) {
     e2.printStackTrace();
}
     }
}

public static void prefetchSetTest(OracleConnection oraConn, int connfsz, int pstmtfsz, int rsfsz){    
     try {
         if (connfsz > 0) {oraConn.setDefaultRowPrefetch(connfsz);} // connection에 Fetch siz를 설정, 0이 들어오면 수행하지 않음
         String header = "/* CONN Level Fetch Size = "+ connfsz + " , STMT Level Fetch Size = "+pstmtfsz+" , RS Level Fetch Size = "+rsfsz+" */ \n";
         pstmt = oraConn.prepareStatement(header + "select * from prefetch where num between 1 and 100");
         if (pstmtfsz > 0) {pstmt.setFetchSize(pstmtfsz);} // PreparedStatement에 Fetch siz를 설정, 0이 들어오면 수행하지 않음
         rs = pstmt.executeQuery();
         if (rsfsz > 0) {rs.setFetchSize(rsfsz);} // ResultSet에 Fetch siz를 설정, 0이 들어오면 수행하지 않음
         while(rs.next()) { }
     } catch (Exception e){
         e.printStackTrace();
     } finally {
try {
     rs.close();
pstmt.close();
} catch(Exception e2) {
     e2.printStackTrace();
}
     }
}
}

  • 마지막으로 데이터의 생성은 다음과 같이 하였음

CREATE TABLE PREFETCH
AS SELECT ROWNUM NUM, A.*
FROM DBA_OBJECTS A, DBA_OBJECTS B
WHERE ROWNUM <= 1000000;

CREATE UNIQUE INDEX PREFETCH_IDX1 ON PREFETCH(NUM);



  1. 테스트 : 각 Level에서 Fetch size를 세팅하고 sql을 수행
  • Connection, Statement, ResultSet에서 각각 fetch size를 세팅하고 각각 인덱스를 통해 100건의 데이터를 가져오는 테스트를 진행
  • Connection Level의 fetch size는 java.sql.Connection객체에서는 세팅이 불가능하고 oracle.jdbc.OracleConnection객체에서 설정이 가능
  • 이는 Oracle JDBC를 이용할 경우 활용할 수 있는 기능으로 setDefaultRowPrefetch method를 사용하여 설정
  • Statement Level에서는 PreparedStatement객체의 setFetchSize method를 이용하여 설정이 가능함
  • Result Set Level에서는 setFetchSize method를 이용하여 설정이 가능함
  • 테스트 결과는 다음과 같음
  • 여기서 0은 setting작업을 수행하지 않고 default로 사용한다는 의미
  • Fetch Process는 fetch된 건수 – 연속 발생 횟수로 표시하였음

순번

Connection

Statement

ResultSet

Fetch Count

Logical Reads

Fetch Process

1 

10 

20 

10 

10 

22 

20-1, 10-8, 0-1 

2 

10 

20 

20 

6 

14 

20-5, 0-1

3 

10 

20 

50 

3 

9 

20-1, 50-1, 30-1 

4 

10 

50 

10 

7 

16 

50-1, 10-5, 0-1 

5 

10 

50 

20 

4 

11 

50-1, 20-2, 10-1 

6 

10 

50 

50 

3 

8 

50-2, 0-1 

7 

20 

10 

10 

11 

24 

10-1, 0-1 

8 

20 

10 

20 

6 

15 

10-1, 20-4, 10-1 

9 

20 

10 

50 

3 

9 

10-1, 50-1, 40-1 

10 

20 

20 

10 

10 

22 

20-1, 10-8, 0-1

11 

20 

20 

50 

3 

9 

20-1, 50-1, 30-1 

12 

20 

50 

10 

7 

16 

50-1, 10-5, 0-1 

13 

20 

50 

20 

4 

11 

50-1, 20-2, 10-1 

14 

20 

50 

50 

3 

8 

50-2, 0-1 

15 

50 

10 

10 

11 

24 

10-10, 0-1 

16 

50 

10 

20 

6 

15 

10-1, 20-4, 10-1 

17 

50 

10 

50 

3 

9 

10-1, 50-1, 40-1 

18 

50 

20 

10 

10 

22 

20-1, 10-8, 0-1

19 

50 

20 

20 

6 

14 

20-5, 0-1 

20 

50 

20 

50 

3 

9 

20-1, 50-1, 30-1 

21 

50 

50 

10 

7 

16 

50-1, 10-5, 0-1 

22 

50 

50 

20 

4 

11 

50-1, 20-2, 10-1 

23 

10 

10 

10 

11 

24 

10-10, 0-1 

24 

20 

20 

20 

6 

14 

20-5, 0-1 

25 

50 

50 

50 

3 

8 

50-2, 0-1 

26 

50 

0 

0 

3 

8 

50-2, 0-1 

27 

0 

50 

0 

3 

8 

50-2, 0-1 

28 

0 

0 

50 

3 

9 

10-1, 50-1, 40-1 

29 

0 

0 

0 

11 

24 

10-10, 0-1 

30 

50 

0 

20 

4 

11 

50-1, 20-2, 10-1 


  • 테스트를 통해 알 수 있는 것 네가지는 다음과 같음
  • 첫째, ReseultSet의 Fetch Size는 두번째 fetch이후부터 적용됨
  • 테스트 결과에서 26번과 30번 테스트를 제외하고 Fetch Process를 살펴보면 처음에 fetch된 크기는 거의 대부분 Statement의 fetch size가 설정된 경우 그 크기와 동일하고 두번째 fetch부터 ResultSet의 Fetch Size가 적용된 것을 알 수 있다. 설정이 되지 않은 경우는 10으로 Statement의 default fetch size를 사용하였음.
  • 이는 execute할 때 이미 설정된 fetch size를 사용하고 나서 resultset의 fetch size를 적용하기 때문
  • 둘째 FetchSize의 설정은 Overriding됨
  • 또한 26번과 30번 테스트를 제외하고 모든 테스트에서 ResultSet을 설정하기 전, 즉 첫번째 fetch는 Statement Level의 Fetch Size를 적용받는데 이는 Connection Level의 fetch size 설정이 적용되지 않는 것이 아니라 Statement Level의 Fetch Size로 overriding되는 것임
  • 26번과 30번 테스트에서는 Connection Level의 fetch size로 첫번째 fetch를 수행되었는데 특히 30번 테스트에서 두번째 fetch 부터 ResultSet의 fetch size가 적용된 것을 보면 connection Level의 fetch size가 설정되고 있음을 알 수 있음
  • 이 Overriding에 대해 테스트를 한가지 더 해보도록 하겠는데 기존의 application에서는 connection level à Statement Level à ResultSet Level로 설정이 되는 순서를 가지고 있었는데 이를 Statement Level à ResultSet Level à connection Level로 그리고 Statement Level à connection level à ResultSet Level로 순서를 변경하여 수행해 보도록 할 것임
  • 테스트 결과는 다음과 같음

Statement

ResultSet

Connection

Fetch Count

Logical Reads

Fetch Process

20

10

50

10

22

20-1, 10-8, 0-1

Statement

Connection

ResultSet

Fetch Count

Logical Reads

Fetch Process

20

50

0

6

14

20-5, 0-1

  • 이 테스트를 보면 Statement Level에서 Fetch Size를 설정한 이후 Connection Level의 fetch Size의 변경은 아무런 영향을 주지 못하고 있는 것으로 판단됨
  • 이번에는 Statement Level의 위치를 변경시켜서 테스트를 수행해 보았음

Connection

ResultSet

Statement

Fetch Count

Logical Reads

Fetch Process

50

10

2

7

16

50-1, 10-5, 0-1

  • 이도 역시 ResultSet Level의 설정을 바꾸지 못하고 있음을 알 수 있음
  • 이것은 OracleConnection 객체에서 PreparedStatement 객체로 그리고 ResultSet객체로 이미 상속을 받은 이후에는 다시 그 상위 객체의 설정이 변경되어도 참조하지 않기 때문임
  • 셋째, fetch는 더 이상 가져올 데이터가 없는 것을 확인할 때 까지 진행됨
  • 위의 테스트에서 마지막 fetch의 경우를 살펴 보면 모두가 fetch size보다 적은 수의 건수이거나 아니면 0건일 경우로 완료한다는 사실을 알 수 있음
  • Oracle은 더 이상 가져올 것이 없다는 것을 감지할 때 까지 fetch를 하기 때문에 이러한 경우가 발생하는데 일단 Fetch size를 채울 수 있으면 그 다음 row가 있는지 없는지 검사하지 않는 다는 것을 알 수 있음
  • 넷째, 어느 Level에서 fetch size를 설정해도 같은 fetch size일 경우 Oracle의 일량에는 차이가 없음
  • 해당 query는 5 block에 걸쳐 저장되어 있음 (Date Block 2+ Index Block 3)

SQL>SELECT dbms_rowid.rowid_block_number( ROWID ) Block_num,
SQL> COUNT( * )
SQL>FROM prefetch
SQL>WHERE num BETWEEN 1 AND 100
SQL>GROUP BY dbms_rowid.rowid_block_number( ROWID );

           BLOCK_NUM             COUNT(*)
-------------------- --------------------
             24021                      9
             24020                     91

2 rows

SQL>analyze index prefeth_idx1 compute statistics;

Index analyzed

SQL>select blevel from user_indexes where index_name = 'PREFETCH_IDX1';

              BLEVEL
--------------------
                   2

1 row

*************************************************************************************
   Rows Row Source Operation
------- ---------------------------------------------------
    100 TABLE ACCESS BY INDEX ROWID PREFETCH (cr=5 pr=0 pw=0 time=733 us)
    100 INDEX RANGE SCAN PREFETCH_IDX1 (cr=3 pr=0 pw=0 time=190 us)(object id 58148)


  • 위의 테스트를 Logical Reads의 순으로 정렬을 해보면 다음과 같음

Connection

ResultSet

Statement

Fetch Count

Logical Reads

Fetch Process

10 

50 

50 

3 

8 

50-2, 0-1 

50 

0 

0 

3 

8 

50-2, 0-1 

0 

50 

0 

3 

8 

50-2, 0-1 

50 

50 

50 

3 

8 

50-2, 0-1 

20 

50 

50 

3 

8 

50-2, 0-1 

10 

20 

50 

3 

9 

20-1, 50-1, 30-1 

0 

0 

50 

3 

9 

10-1, 50-1, 40-1 

50 

20 

50 

3 

9 

20-1, 50-1, 30-1 

50 

10 

50 

3 

9 

10-1, 50-1, 40-1 

20 

20 

50 

3

9 

20-1, 50-1, 30-1 

20 

10 

50 

3 

9 

10-1, 50-1, 40-1 

50 

50 

20 

4 

11 

50-1, 20-2, 10-1 

20 

50 

20 

4 

11 

50-1, 20-2, 10-1 

10 

50 

20 

4 

11 

50-1, 20-2, 10-1 

50 

0 

20 

4 

11 

50-1, 20-2, 10-1 

50 

20 

20 

6 

14 

20-5, 0-1 

10 

20 

20 

6 

14 

20-5, 0-1 

20 

20 

20 

6 

14 

20-5, 0-1 

20

10 

20 

6 

15 

10-1, 20-4, 10-1 

50 

10 

20 

6 

15 

10-1, 20-4, 10-1 

10 

50 

10 

7 

16 

50-1, 10-5, 0-1 

50 

50 

10 

7 

16 

50-1, 10-5, 0-1

20 

50 

10 

7 

16 

50-1, 10-5, 0-1 

10 

20 

10 

10 

22 

20-1, 10-8, 0-1 

20 

20 

10 

10 

22 

20-1, 10-8, 0-1 

50 

20 

10 

10 

22 

20-1, 10-8, 0-1 

0 

0 

0

11 

24 

10-10, 0-1 

10 

10 

10 

11 

24 

10-10, 0-1 

20 

10 

10 

11 

24 

10-1, 0-1 

50 

10 

10 

11 

24 

10-10, 0-1 

  • 이 표를 보면 Logical Reads라는 일량을 나타내는 지표는 어느 Level에서 fetch size를 결정하느냐에 상관 없이 단지 fetch count에 따라 달라짐을 알 수 있음
  • 또한 마지막 fetch에서 데이터를 가져오는지 여부에 따라 일량이 달라짐을 알 수 있음
  • 위의 경우에서 다음과 같은 공식이 나타날 수 있음
    마지막 fetch시 데이터가 있는 경우 : fetch_count *2 + 3 = logical_reads
    마지막 fetch시 데이터가 없는 경우 : fetch_count *2 + 2 = logical_reads
  • 위의 네가지 사실을 바탕으로 어느 단계에서 fetch Size를 설정하는 것이 가장 좋을지 생각해 보면 Statement Level이라고 말할 수 있음
  • 그 이유는 Connection Level에서 설정을 할 경우 Statement Level과 같은 효과를 볼 수도 있고 Connection을 획득하고 있는 동안에는 따로 설정을 할 필요는 없지만 이것이 JDBC표준이 아니고 Oracle JDBC를 사용해야 하기 때문에 Oracle에 의존적으로 될 가능성이 있음
  • 그리고 Result Set의 경우는 같은 조건이라면 Oracle의 일량을 줄이는 것이 좋은 방법이기 때문에 회피하는 것이 좋음. 각 Query마다 1블록이라도 Memory Read가 더 발생한다면 하루 백만 건을 수행할 경우 백만 블록의 불필요한 일량이 발생하기 때문.
  • 만약 50건을 가져올 때 Fetch size를 20으로 설정할 경우 Result Set Level에만 설정을 한다면 10, 20, 20, 0 이 되어 4번 fetch가 발생할 것임. 테스트에 입각하여 계산해 본다면 이 경우 4*2+2=10 block의 logical IO가 발생함. 만약 Statement Level에서 설정할 경우 20, 20, 10이 되어 3번의 fetch가 발생. 이 경우 3*2+3 = 9 block이 발생하므로 1 block의 차이가 나타남
  • 그래서 결국 Statement Level에서 설정한다면 위와 같은 단점을 극복할 수 있음. 또한 하나의 Statement에서 여러 건의 Resultset을 사용할 경우 ResultSet마다 각기 다른 fetch size를 적용하지 않는다면 Method call자체도 줄일 수 있는 장점이 있음
  • 그렇다면 이 테스트에서 최적의 결과를 가져오려면 어떻게 해야할까? 여기서 최적의 결과란 Oracle의 경우 Logical Read의 수치가 5이고 Fetch Count가 1인 경우
  • 이 경우 답은 statement level에서 100을 넘는 수의 fetch size를 설정하는 것임
  • 그래서 101로 fetch size를 설정하고 그 결과를 Trace를 수행하였음

/* CONN Level Fetch Size = 0 , STMT Level Fetch Size = 101 , RS Level Fetch Size = 0 */
select * from prefetch where num between 1 and 100

   call  count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
  Parse      1     0.00       0.04          0          0          0          0
Execute      1     0.00       0.00          0          0          0          0
  Fetch      1     0.00       0.00          0          5          0        100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
  total      3     0.00       0.04          0          5          0        100

JOSS:oracle:/home/oracle/admin/WASDB/udump:!> grep "FETCH #" wasdb_ora_3139.trc
FETCH #2:c=0,e=467,p=0,cr=5,cu=0,mis=0,r=100,dep=0,og=1,tim=1156787372453409



  1. 테스트 : PreFetch의 사용 효과
  • Prefetch를 설정하여 사용하는 경우와 Default로 사용하는 경우 성능 차이는 어떠할까?
  • Statement Level에서 fetch size를 600으로 설정하고 비교해 보았음
     

    TOTAL
    ELAPSED

    APP
    WAIT

    DB
    CPU

    DB
    WAIT

    DB
    EALPSED

    USE_MEM
    (MB)

    logical
    reads

    Physical
    reads

    Prefetch_0

    63.172

    4.202

    7.07

    51.9

    58.97

    1.05

    110899

    0

    Prefetch_600

    4.704

    0.104

    1.2

    3.4

    4.6

    1.76

    13256

    0



  • 소요 시간 및 logical reads는 약 15배 정도 차이가 나고 그에 반해 메모리 사용은 1.7배 증가한 것으로 판단됨
  • 또한 Oracle의 Wait Event의 현황은 다음과 같음

TAG

EVENT

WAIT_TIME

TOTAL_WAITS

Prefetch_0

SQL*Net message from client

51.81

100005

Prefetch_0

SQL*Net message to client

0.09

100005

Prefetch_600

SQL*Net message from client

3.34

1671

Prefetch_600

SQL*Net more data to client

0.06

4999

  • 역시 Roundtrip에 의한 비용이 상당한 것으로 나타나고 있음
  • 아래는 Server에서 Application으로 보내진 데이터 양을 의미함. 같은 백만건의 데이터를 fetch했음에도 불구하고 Network을 타고 전송된 양은 4MB의 차이가 나고 있음을 알 수 있음

Prefetch_0

Mbytes sent via SQL*Net to client

14.28

Prefetch_600

Mbytes sent via SQL*Net to client

10.47

  • 역시 적절한 fetch Size를 설정하여 사용하는 것은 성능상 상당한 이점이 있는 판단할 수 있음
  • 그렇다면 적절한 fetch size는 얼마만큼일까? 테스트를 통해 알아보자

  1. 테스트 : Fetch Size별 성능의 비교
  • 테스트는 같은 query를 Statement Level에서 설정하여 수행하는 식으로 진행
  • default부터 90000으로 설정한 것 까지 36가지의 시나리오를 가지고 진행함
  • 테스트 결과는 다음과 같음

TAG

TOTAL
ELAPSED

APP

WAIT

DB

CPU

DB

WAIT

DB

EALPSED

USE_MEM
(MB)

session logical reads

Prefetch_0

63.172

4.202

7.07

51.9

58.97

1.05

110899

Prefetch_20

32.484

2.834

3.48

26.17

29.65

0.45

61304

Prefetch_30

22.25

1.6

2.85

17.8

20.65

0.71

44760

Prefetch_40

22.359

1.279

2.43

18.65

21.08

0.93

36449

Prefetch_50

19.25

1.04

2.14

16.07

18.21

0.64

31454

Prefetch_60

18.078

0.968

1.89

15.22

17.11

0.88

28197

Prefetch_70

18.907

0.847

1.82

16.24

18.06

0.88

25782

Prefetch_80

17.235

0.795

1.67

14.77

16.44

1.6

24021

Prefetch_90

16.141

0.641

1.68

13.82

15.5

1.24

22651

Prefetch_100

15

0.69

1.51

12.8

14.31

1.61

21536

Prefetch_200

8.875

0.265

1.43

7.18

8.61

1.55

16566

Prefetch_300

7.453

0.143

1.32

5.99

7.31

1.46

14916

Prefetch_400

6.234

0.164

1.24

4.83

6.07

1.42

14080

Prefetch_500

5.391

0.041

1.26

4.09

5.35

2.03

13583

Prefetch_600

4.704

0.104

1.2

3.4

4.6

1.76

13256

Prefetch_700

4.515

0.055

1.21

3.25

4.46

1.74

13015

Prefetch_800

4.36

0.03

1.24

3.09

4.33

1.81

12839

Prefetch_900

3.797

0.007

1.21

2.58

3.79

2.36

12703

Prefetch_1000

3.704

0

1.21

2.5

3.71

2.5

12589

Prefetch_2000

3.219

0.049

1.14

2.03

3.17

3.15

12094

Prefetch_3000

2.969

0

1.2

1.81

3.01

2.39

11928

Prefetch_4000

2.906

0

1.18

1.74

2.92

2.98

11845

Prefetch_5000

2.828

0

1.19

1.67

2.86

3.57

11791

Prefetch_6000

2.781

0

1.17

1.63

2.8

3.97

11762

Prefetch_7000

2.766

0

1.18

1.63

2.81

4.58

11737

Prefetch_8000

2.875

0

1.17

1.74

2.91

5.19

11720

Prefetch_9000

2.688

0

1.19

1.55

2.74

5.82

11707

Prefetch_10000

2.687

0

1.17

1.54

2.71

6.35

11694

Prefetch_20000

2.672

0

1.15

1.53

2.68

12.33

11645

Prefetch_30000

2.719

0.009

1.14

1.57

2.71

18.31

11629

Prefetch_40000

2.703

0

1.13

1.59

2.72

24.3

11620

Prefetch_50000

2.75

0.02

1.1

1.63

2.73

30.22

11615

Prefetch_60000

2.766

0

1.12

1.65

2.77

36.2

11612

Prefetch_70000

2.703

0.013

1.11

1.58

2.69

42.19

11610

Prefetch_80000

2.734

0.014

1.1

1.62

2.72

48.17

11608

Prefetch_90000

2.813

0.013

1.12

1.68

2.8

54.1

11607

  • Elapsed Time의 추이와 Memory 사용량 추이 그리고 Logical Read의 추이는 다음과 같음

  • 여기서 알 수 있는 것은 일량과 수행 시간은 그 변화의 패턴이 동일하다는 것을 알 수 있음
  • 수행시간의 경우 Default(10)에서 200까지는 급격한 감소추세이며 그 이후 부터는 완만하게 감소함
  • 사용 메모리의 추이를 보면 1000까지는 완만한 증가를 보이다가 그 이후부터는 증가의 폭이 점점 커짐을 알 수 있음
  • 여기서 최적의 Fetch Count를 찾아보자 최적의 Fetch Count를 찾는데 중요한 요소는 수행시간과 메모리 사용량
  • 수행시간의 경우 최소 200 이상에서 찾아야 하고 메모리 사용량의 경우는 최대 1000까지 찾아 보아야 함
  • 이 범위 내에서 수행시간의 편차가 줄어들면서 메모리도 2MB를 넘지 않는 시점인 600정도가 가장 적절하다고 생각됨
  • 그러나 이 수치는 절대적인 것은 아니며 시스템의 환경 및 데이터의 상황, application의 query상황에 따라 다른 기준이 적용될 수 있음

'기술이야기' 카테고리의 다른 글

Jeus의 Thread Stop  (0) 2008.05.09
[JVM Internal] Method Area  (2) 2008.03.20
[JVM Internal] namespace  (0) 2008.02.25
댓글
댓글쓰기 폼