티스토리 뷰

기술이야기

Statement Cache

novathinker 2009.05.26 19:02
  1. Statement Cache

- JDBC3.0에 소개된 기능으로 루프내에서 반복적으로 수행되는 Statement에 대한 Oracle Cursor를 캐시하는 기능임

- 이는 Statement가 close될 때 JDBC드라이버가 cursor와 연결된 Statement를 cache하게 되고 다음번 작업에서 같은 Statement를 수행하게 되면 Cache목록에서 이 Statement를 재사용하게 되어 Oracle에서는 soft parsing을 하지 않게 됨.

- Statement가 cache되어 있는 동안 Oracle에서 cursor를 Open하고 있는 지는 다음의 테스트를 통해 알 수 있음

- 수행 source code

package exem.oracle.statementcache;

 

import java.sql.*;

import oracle.jdbc.*;

 

public class HasCursor {

 

    public static void main(String[] args) {

        // TODO Auto-generated method stub

OracleConnection conn1 = OraUtil.getOraConnection(false, 0);

Connection conn2 = OraUtil.getConnection(false);

PreparedStatement pstmt1 = null;

ResultSet ors = null;

String sid=null ;

try {

conn1.setImplicitCachingEnabled(true);

conn1.setStatementCacheSize(2);

for (int i=1; i <=100 ; i++) {

     pstmt1 = conn1.prepareStatement("select /*ImplCursor*/ ?, userenv('sid') from dual");

     pstmt1.setInt(1, i);

     ors = pstmt1.executeQuery();     

     ors.next();

     sid = ors.getString(2);

     ors.close();

     pstmt1.close();

}

System.out.println("\nSID는 "+sid+"\n" );

 

PreparedStatement pstmt2 = conn2.prepareStatement("select sid, sql_text from v$open_cursor where sql_text like '%/*ImplCursor*/%' ");

ResultSet rs = pstmt2.executeQuery();

while (rs.next()) {

     System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\n" );

}

rs.close();     

pstmt2.close();

conn2.close();

conn1.close();

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

 

 

    }

 

}

- 수행 결과

E:\> java -classpath "E:/oracle/product/10.2.0/db_1/jdbc/lib/ojdbc14.jar;." exem.oracle.statementcache.HasCursor

 

SID는 140

 

140 select /*ImplCursor*/ :1, userenv('sid') from dual

- connection을 닫지 않은 상태에서 V$OPEN_CURSOR 뷰를 조회하면 수행 했던 SQL의 커서가 검색됨을 알 수 있음.

- 이는 Statement가 Cache되어 있는 동안 해당 Cursor도 같이 열려 있음을 알 수 있음.

- Oracle 10g의 경우 Session_cached_cursor의 디폴트가 20임

- Statement Cache기능은 connection단위로 구현되어 있으며 java.sql.Connection을 확장한OracleConnection Object 또는 javax.sql.PooledConnection을 구현한 OraclePooledConnection을 통해 사용할 수 있음

- Statement Cache는 Implicit Statement cache와 Explicit Statement Cache로 나뉨

- 또한 Implictit Statement의 경우 PreparedStatement를 사용하는 경우와 OraclePreparedStatement를 사용하는 경우로 나눌 수 있음

- Statement Cache를 사용하는 경우 각 Cache 방법의 변경에 따른 테스트를 수행하여 어떤 결과가 나타나는지 살펴 보고 성능이 개선 되는 지에 대한 분석을 할 것임

 

  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

 

3) 테스트 툴

- 테스트를 위해 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은 다음과 같이 생성하였음

- 테스트를 위해 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.statementcache;

 

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:@210.122.227.247:1522:WASDB", "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, int sess){

        OracleConnection oraConn = null;

        PreparedStatement pstmt = null;

        try {

            Class.forName("oracle.jdbc.OracleDriver");

            oraConn= (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@210.122.227.247:1522:WASDB", "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();

            }

            pstmt = oraConn.prepareStatement("alter session set session_cached_cursors="+sess);            

            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, int latches) {

        CallableStatement cstmt = null;        

        PreparedStatement pstmt = null;

        try {

            cstmt = conn.prepareCall("{call db_profiler2 (?,?,?,?)}" );

            cstmt.setString(1, tag);

            cstmt.setLong(2, total_elapsed);

            cstmt.setLong(3, used_mem);

            cstmt.setLong(4, latches);

            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, int latches) {

        CallableStatement cstmt = null;        

        PreparedStatement pstmt = null;

        try {

            cstmt = oraConn.prepareCall("{call db_profiler2 (?,?,?,?)}" );

            cstmt.setString(1, tag);

            cstmt.setLong(2, total_elapsed);

            cstmt.setLong(3, used_mem);

            cstmt.setLong(4, latches);            

            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) {}

        }

    }        

    

public static int getLibLatchs() {

     Connection conn = getConnection(false);

     PreparedStatement pstmt = null;

     ResultSet rs=null;

     int rst=0;

     try {

         pstmt = conn.prepareStatement("select gets+misses from V$LATCH where name = 'library cache' ");

         rs = pstmt.executeQuery();

         rs.next();

         rst = rs.getInt(1);

     } catch (Exception e){

         e.printStackTrace();

     } finally { try {

         rs.close();

         pstmt.close();

         conn.close(); } catch(Exception x) {}

     }

     return rst;

}

    

}

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

/*

* Class : DoTest

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

*/

 

package exem.oracle.statementcache;

 

import oracle.jdbc.OracleConnection;

 

 

public class DoTest {

 

static String act = "";

    /**

     * @param args

     */

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        act = args[0];

        if (args[0].equals("NoCache")){

            noCache();

        } else if (args[0].equals("OraImpl")){

            oraImplCache();

        } else if (args[0].equals("OraExpl")) {

            oraExplCache();

        } else if (args[0].equals("Impl")) {

            implCache();

        } else if (args[0].equals("SessionCache")) {

            sessionCache();

        } else if (args[0].equals("InLoop")) {

            inLoop();

        }

    }

 

    public static void noCache(){

        OracleConnection conn=null;

    conn = OraUtil.getOraConnection(true, 0);

    int latches = OraUtil.getLibLatchs();

        long chktime = System.currentTimeMillis();    

        StmtCache.noCache(conn);

         OraUtil.cutOraConnection(conn, act, true, true, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory(), OraUtil.getLibLatchs()-latches );

    }

 

    public static void implCache(){

        OracleConnection conn=null;

    conn = OraUtil.getOraConnection(true, 0);

    int latches = OraUtil.getLibLatchs();     

        long chktime = System.currentTimeMillis();    

        StmtCache.implCache(conn);

         OraUtil.cutOraConnection(conn, act, true, true, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory(), OraUtil.getLibLatchs()-latches );

    }

    

    public static void oraImplCache(){

        OracleConnection conn=null;

    conn = OraUtil.getOraConnection(true, 0);

    int latches = OraUtil.getLibLatchs();     

        long chktime = System.currentTimeMillis();    

        StmtCache.oraImplCache(conn);

         OraUtil.cutOraConnection(conn, act, true, true, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory(), OraUtil.getLibLatchs()-latches );

    }

 

    public static void oraExplCache(){

        OracleConnection conn=null;

    conn = OraUtil.getOraConnection(true, 0);

    int latches = OraUtil.getLibLatchs();     

        long chktime = System.currentTimeMillis();    

        StmtCache.oraExplCache(conn);

         OraUtil.cutOraConnection(conn, act, true, true, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory(), OraUtil.getLibLatchs()-latches );

    }

 

    public static void sessionCache(){

        OracleConnection conn=null;

    conn = OraUtil.getOraConnection(true, 10);

    int latches = OraUtil.getLibLatchs();     

        long chktime = System.currentTimeMillis();    

        StmtCache.sessionCache(conn);

         OraUtil.cutOraConnection(conn, act, true, true, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory(), OraUtil.getLibLatchs()-latches );

    }

 

    public static void inLoop(){

        OracleConnection conn=null;

    conn = OraUtil.getOraConnection(true, 10);

    int latches = OraUtil.getLibLatchs();     

        long chktime = System.currentTimeMillis();    

        StmtCache.inLoop(conn);

         OraUtil.cutOraConnection(conn, act, true, true, (System.currentTimeMillis()-chktime), Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory(), OraUtil.getLibLatchs()-latches );

    }    

    

}

 

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

/*

* Class : StmtCache

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

*/

 

package exem.oracle.statementcache;

 

import oracle.jdbc.*;

import java.sql.*;

 

public class StmtCache {

    public static void noCache(OracleConnection oraConn) {        

        PreparedStatement pstmt = null;

        ResultSet rs= null;

        try {

            for (int i = 0 ; i <= 10000 ; i++) {

                pstmt = oraConn.prepareStatement(" select /* No Cache */ ? from dual ");

                pstmt.setInt(1, i);

                rs = pstmt.executeQuery();

                pstmt.close();

            }                        

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

    }

 

    public static void implCache(OracleConnection oraConn) {        

        PreparedStatement pstmt = null;

        ResultSet rs= null;

        try {

            oraConn.setImplicitCachingEnabled(true);

            oraConn.setStatementCacheSize(10);

            for (int i = 0 ; i <= 10000 ; i++) {

                pstmt = oraConn.prepareStatement("select /* Implicit Cache */ ? from dual ");

                pstmt.setInt(1, i);

                rs = pstmt.executeQuery();

                pstmt.close();

            }                                    

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

    }

    

    public static void oraImplCache(OracleConnection oraConn) {        

        OraclePreparedStatement pstmt = null;

        ResultSet rs= null;

        try {

            oraConn.setImplicitCachingEnabled(true);

            oraConn.setStatementCacheSize(10);

            for (int i = 0 ; i <= 10000 ; i++) {

                pstmt = (OraclePreparedStatement)oraConn.prepareStatement("select /* OraImplicit Cache */ ? from dual ");

                pstmt.setInt(1, i);

                rs = pstmt.executeQuery();

                pstmt.close();

            }                                    

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

    }

 

    public static void oraExplCache(OracleConnection oraConn) {

        OraclePreparedStatement pstmt = null;

        ResultSet rs= null;

        String key = "EXPLICIT_KEY" ;

        try {

            oraConn.setExplicitCachingEnabled(true);

            oraConn.setStatementCacheSize(10);

            for (int i = 0 ; i <= 10000 ; i++) {

                pstmt = (OraclePreparedStatement)oraConn.getStatementWithKey(key);

                if (pstmt==null) {

                    pstmt = (OraclePreparedStatement)oraConn.prepareStatement( "select /* OraExplicit Cache */ ? from dual ");

                }

                pstmt.setInt(1, i);

                rs = pstmt.executeQuery();

                pstmt.closeWithKey(key);

            }                                    

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

    }

 

    

    public static void sessionCache(OracleConnection oraConn) {        

        PreparedStatement pstmt = null;

        ResultSet rs= null;

        try {

            for (int i = 0 ; i <= 10000 ; i++) {

                pstmt = oraConn.prepareStatement("select /* Session Cached Cursor 10 */ ? from dual ");

                pstmt.setInt(1, i);

                rs = pstmt.executeQuery();

                pstmt.close();

            }                                    

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

    }

 

    public static void inLoop(OracleConnection oraConn) {        

        PreparedStatement pstmt = null;

        ResultSet rs= null;

        try {

            pstmt = oraConn.prepareStatement(" select /* InLoop */ ? from dual ");

            for (int i = 0 ; i <= 10000 ; i++) {

                pstmt.setInt(1, i);

                rs = pstmt.executeQuery();

            }                        

            pstmt.close();

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

    }

    

}

  • Oracle의 Stat 및 Wait에 대한 통계정보를 수집하기 위해 다음과 같은 Procedure를 생성함

CREATE OR REPLACE procedure DB_PROFILER2(Tag in varchar2, Total_Elapsed in number, Used_Mem in number, LIB in number)

IS

BEGIN

 

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

 

INSERT INTO WAITPROF 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 STATPROF SELECT TAG, USERENV('SID'), 'TOTAL ELAPSED TIME', TOTAL_ELAPSED FROM DUAL

UNION ALL

SELECT TAG, USERENV('SID'), 'USED MEMORY', USED_MEM FROM DUAL

UNION ALL

SELECT TAG, USERENV('SID'), 'LIBRARY CACHE LATCHS', LIB FROM DUAL;

 

COMMIT;

 

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE( SQLERRM );

END ;

 

4) 테스트

- 앞서 얘기했듯 Statement Cache란 JDBC에 Oracle의 Open Cursor와 연결된 Statement 객체를 저장해 놓고 있는 기능이라고 하였음

- 이 Statement Cache의 성능상의 개선 효과를 알아보기 위해 여러가지 테스트를 수행하였음.

- 테스트 시나리오는 다음과 같음

- "select ? from dual"이라는 SQL을 1~10000까지의 변수를 binding하여 수행하는 작업을 여러가지 방법으로 수행

- 먼저 각 binding마다 PreparedStatement instance를 생성하여 수행 후 Close하는 것을 반복하는 작업 (NoCache)

- PreparedStatement 객체를 생성한 후 Loop 내에서는 binding작업과 수행을 반복하고 Loop작업이 끝난 후 Close(InLoop)

- Implicit Statement Cache 설정 후 PreparedStatement Instance를 생성, 수행, Close를 반복(Impl)

- 위의 작업과 동일하나 PreparedStatement가 아닌 OraclePreparedStatement객체로 작업 수행 (OraImpl)

- Explicit Statement Cache 설정 후 OraclePreparedStatement Instance를 생성, 수행, Close를 반복(OraExpl)

- Cache를 하지 않고 Oracle의 Session_Cached_Cursors를 10으로 설정하고 수행(SessionCache)

- 이 6가지 방법에 대한 테스트를 수행할 예정임.

- 여기서 짚고 넘어갈 점은 첫째 Standard JDBC의 Connection 객체를 사용할 수 없고 이를 확장한 OracleConnection기능을 사용해야 한다는 것

- 둘째 Explicit Statement Cache도 OralcePreparedStatement에서 구현되어 있음. PreparedStatement에서는 Implicit Statement Cache만 구현되어 있음. 참고로 Implicit Statement Cache는 SQL문장을 Key로 하기 때문에 PreparedStatement와 CallableStatement 및 이를 상속한 객체들만 사용이 가능

- 셋째 Oracle 10g의 경우 Session_cached_cursors라는 파라메터가 default로 20으로 설정되어 있으나 테스트를 위해 모두 Connection을 맺은 이후 0으로 변경하여 수행함

- 넷째 Oracle의 Session Cached Cursor에 대해서 좀 알아보자. 일단 Cursor란 특정 SQL에 대한 SQL Area를 의미함. SQL을 수행할 때 SQL AREA의 정보를 참조하게 됨.

- 그러므로 SQL을 Execute하기 위해서는 반드시 Cursor가 생성됨은 물론 Open되어 있어야 함

- Parse라는 것은 결국 Execute를 위한 SQL Area 즉 Cursor의 탐색 혹은 생성이 목적임.

- 만약 재사용할 Cursor가 없으면 여러 dictionary정보를 이용하여 메모리를 할당 받아 Cursor를 생성하고 Open해야 하는데 이를 Hard Parse라고 함

- 반면 Cursor가 있다면 해당 Cursor의 정보를 탐색하여 찾아가기만 하면 됨 이를 Soft Parse라고 함 그러나 soft Parse라 하더라도 SQL이 반복적으로 수행되면 Session cursor의 반복적인 열고 닫음으로 인해 Oracle내부에서는 latch등을 획득해서 Library cache를 탐색하고 이를 사용할 수 있도록 Pin/lock을 걸어야 함. 즉 Performance에 영향을 주게 됨

- Oracle은 동일한 Cursor를 사용하는 SQL이 세 번 이상 Parse하는 경우를 체크하여 Session cursor 내에 해당 Cursor를 이동 시키고 그 이후의 SQL 수행에 대해서는 Session Cursor에 cache되어 있는 Cursor를 찾게 됨

- Cache된 Session Cursor는 LRU로 관리되면 이 Cache의 크기는 SESSION_CACHED_CURSORS라는 parameter에 의해 결정됨

- 테스트 결과는 다음과 같음

TEST

TOTAL_ELAPSED

APP_WAIT

DB_CPU

DB_WAIT

DB_EALPSED

USED_MEM(MB)

LIBRARY CACHE LATCHS

PARSE COUNT

InLoop

7.188

0.188

1.69

5.31

7

0.44

71

1

NoCache

14.562

0.892

3.35

10.32

13.67

1.16

40047

10001

Impl

7.204

0.294

1.64

5.27

6.91

0.99

44

1

OraImpl

7.235

0.265

1.64

5.33

6.97

1

51

1

OraExpl

7.187

0.117

1.75

5.32

7.07

0.99

48

1

SessionCache

14.343

0.943

3.1

10.3

13.4

0.66

545

10001

 

select /* InLoop */ :1

from

dual

 

 

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.01 0 0 0 0

Execute 10001 0.82 0.83 0 0 0 0

Fetch 10001 0.26 0.24 0 0 0 10001

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

total 20003 1.08 1.08 0 0 0 10001

 

********************************************************************************

 

select /* No Cache */ :1

from

dual

 

 

call count cpu elapsed disk query current rows

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

Parse 10001 0.15 0.17 0 0 0 0

Execute 10001 0.81 0.81 0 0 0 0

Fetch 10001 0.33 0.32 0 0 0 10001

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

total 30003 1.30 1.32 0 0 0 10001

 

********************************************************************************

 

select /* Implicit Cache */ :1

from

dual

 

 

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 10001 0.81 0.82 0 0 0 0

Fetch 10001 0.26 0.23 0 0 0 10001

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

total 20003 1.07 1.06 0 0 0 10001

 

********************************************************************************

 

select /* OraImplicit Cache */ :1

from

dual

 

 

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 10001 0.80 0.82 0 0 0 0

Fetch 10001 0.24 0.23 0 0 0 10001

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

total 20003 1.05 1.06 0 0 0 10001

 

********************************************************************************

 

select /* OraExplicit Cache */ :1

from

dual

 

 

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.02 0 0 0 0

Execute 10001 0.80 0.82 0 0 0 0

Fetch 10001 0.27 0.23 0 0 0 10001

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

total 20003 1.07 1.08 0 0 0 10001

 

********************************************************************************

 

select /* Session Cached Cursor 10 */ :1

from

dual

 

 

call count cpu elapsed disk query current rows

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

Parse 10001 0.08 0.09 0 0 0 0

Execute 10001 0.97 0.80 0 0 0 0

Fetch 10001 0.31 0.30 0 0 0 10001

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

total 30003 1.37 1.21 0 0 0 10001

 

- 각 방법 별로 수행시간에서 두 배정도의 차이가 남을 알 수 있음

- 우리가 하나의 SQL을 수행하기 위해서는 Connection을 맺고 Statement 객체를 생성시켜 SQL을 수행하면 Oracle은 해당 SQL을 Parsing, Execute, Fetch과정으로 통해 결과를 ResultSet에게 건네주게 됨

- 이 과정에서 우리는 Connection 객체의 생성 및 Oracle과의 연결 그리고 Execute, Fetch는 동일한 조건으로 고정하였음

- 그러므로 이 차이를 나타내는 것은 Statement 객체의 생성 및 Execute 그리고 이에 따른 Parse작업에서 기인한다고 볼 수 있음

- 이 경우 아래의 그림과 같이 병목 지점을 잡을 수 있을 것임

- Application에서는 Statement Instance를 생성하거나 Cache과정에서 발생할 수 있음.

- 이는 전체 수행시간 중 DB 작업에서 사용한 전체 시간을 제한 나머지 시간, 즉 위의 표에서 APP_WAIT라고 표현된 부분의 수치를 가지고 판단할 수 있음

- 이를 그래프로 표시하면 다음과 같이 나타나는데 NoCache와 SessionCache의 경우 다른 Cache를 사용한 부분 및 InLoop보다 많게는 8배의 시간 차가 나는 것으로 보임

- 특히 InLoop는 Statement의 객체 생성을 한번만 하고 있고 Statement Cache를 사용하는 경우는 동일한 Statement의 경우 JDBC내에 Cache된 Statement를 재사용함. 이 경우는 매번 Statement 객체를 재 생성하는 경우에 비해 월등한 성능 개선 효과가 있음을 알 수 있음

- 또한 Implicit Statement Cache의 경우는 InLoop나 Explicit Statement Cache에 비해 다소 시간차이가 나는 것을 알 수 있는데 Cache된 객체를 검색하는데 사용한 시간이 더해졌을 것이라는 추측이 가능함

- 그러나 특기할 만한 것은 InLoop보다도 Explicit Statement Cache가 더 성능이 빠른 것으로 나타난 것임. 이는 Application 수행시간의 측정이 전체 시간 – DB 시간이라는 간접적인 측정을 통한 것에서 나온 오차 때문으로 생각됨. 아마도 여러 번의 테스트를 수행해서 평균을 내어 본다면 InLoop와 비슷하게 나타날 것으로 생각됨

- 그러나 확실한 것은 주어진 Key를 이용한 Explicit Statement Cache방법이 Cache Lookup에서 성능상 유리한 방법이라는 것

 

- Application과 Oracle사이의 Network에서도 병목이 발생할 수 있음

- 실제로 전체 수행시간의 70%이상을 점유하고 있는 DB_WAIT는 모두가 Network관련한 Event임

TEST

EVENT

WAIT_TIME

TOTAL_WAITS

InLoop

SQL*Net message from client

5.3

10006

SQL*Net message to client

0.01

10006

NoCache

SQL*Net message from client

10.3

20006

SQL*Net message to client

0.02

20006

Impl

SQL*Net message from client

5.26

10006

SQL*Net message to client

0.01

10006

OraImpl

SQL*Net message from client

5.32

10006

SQL*Net message to client

0.01

10006

OraExpl

SQL*Net message from client

5.31

10006

SQL*Net message to client

0.01

10006

SessionCache

SQL*Net message from client

10.28

20006

SQL*Net message to client

0.02

20006

- 이 Event중에서도 Application에서 Oracle로 향하는 Wait Event가 거의 대부분을 차지

- SQL*Net message from client라는 Event는 한번의 수행이 끝나고 다음 수행 때 까지의 Oracle이 대기하는 시간을 의미함

- NoCache와 SessionCache를 제외하고는 모두 비슷한 시간을 나타내고 있음. 이 차이는 바로 Total Waits 즉 Wait 발생 회수에 의한 것임

- 이 Wait Event의 발생은 Parse와 Execute 때 발생하는 것으로 판단됨

TEST

PARSE COUNT

EXECUTE COUNT

Kbytes received via SQL*Net from client

InLoop

1

10001

655.12

NoCache

10001

10001

1153.17

Impl

1

10001

655.12

OraImpl

1

10001

655.12

OraExpl

1

10001

655.12

SessionCache

10001

10001

1319.21

- 위의 표는 Parse 및 Execute횟수와 Network을 통해 Application에서 Oracle로 전송된 데이터의 양을 나타낸 것으로 NoCache와 SessionCache의 경우 Parse의 횟수가 부가적으로 더해져 Network관련 대기 및 일량이 증가하고 있는 것으로 판단됨

- 또한 Network관련 정보만 보았을 때 InLoop와 Statement Cache를 사용한 경우는 같은 작업으로 생각할 수 있을 정도로 흡사함.

 

- Oracle에서 성능에 대한 지표로 사용할 수 있는 것은 실제 Oracle이 이 작업에서 소요한 시간과 Parse시점에서 사용할 수 밖에 없는 자원 즉 Library cache latch에 대한 획득 작업임

TEST

DB_CPU

LIBRARY CACHE LATCHS

PARSE COUNT

session cursor cache hits

InLoop

1.69

71

1

1

NoCache

3.35

40047

10001

1

Impl

1.64

44

1

1

OraImpl

1.64

51

1

1

OraExpl

1.75

48

1

1

SessionCache

3.1

545

10001

10001

- Parse를 매번 수행하는 경우와 그렇지 않은 경우 Oracle의 CPU 시간에서도 약 2배 정도의 시간이 사용되는 것을 알 수 있음.

- 또한 Library cache latch의 획득에 대한 결과를 보면 Statement Cache를 사용하는 경우와 InLoop의 경우 Latch획득 시도 회수가 상당히 적기 때문에 Oracle 자체의 부하도 최소화되고 있다고 생각해 볼 수 있음

- NoCache의 경우 평균 1건을 Parsing하는데 4번 정도의 Latch획득이 이루어지는 것으로 보임.

- SessionCache의 경우 Latch획득에 있어서는 상당한 이점이 있는 것으로 보이지만 DB의 수행시간을 크게 줄여주지는 못하고 있음을 알 수 있음

- Statement Cache를 사용하는 경우는 InLoop의 경우와 같이 Oracle로 하여금 항상 Cursor를 Open한 상태로 유지하여 언제라도 Execute 할 수 있도록 준비하고 있는 것으로 생각해 볼 수 있음

- NoCache의 경우는 Cursor를 열고 닫는 작업 및 이를 탐색하는 작업이 반복되고 있으며 이는 성능상에서 좋지 않은 결과를 가지고 온다고 생각해 볼 수 있음

- SessionCache의 경우는 Cursor를 열고 닫는 작업 및 이를 탐색하는 작업이 반복되기는 하지만 Cursor정보가 Session Cursor로 cache되기 때문에 그 이후의 작업에 대해서는 Library Cache에 대한 직접적인 작업이 수행되지 않게 됨. 이는 session cursor cache hits라는 지표와 Latch 획득 수치를 보면 알 수 있음.

- 그러므로 SessionCache의 경우 수행 시간에 있어서는 큰 이득은 없을 지 몰라도 향후 Library Cache에 관련된 성능 이슈에서는 비교적 자유로울 수 있다는 이점을 가지고 있음

 

5) Statement Cache를 사용하면 왜 성능이 좋아질까?

- 이상의 테스트를 정리하여 보면 다음과 같음

- Statement Cache는 Statement 객체를 Oracle의 Cursor과 연결 지어 이를 Open된 상태로 유지하고 만약 같은 요청이 들어오게 되면 이 객체를 재활용하여 바로 Execute함

- Application입장에서는 Statement의 객체를 Cache하고 이를 탐색하는 부하만 존재하게 되는데 이는 Statement Instance를 생성하고 Close하는 작업이 반복되는 것에 비교하면 큰 성능상의 이점을 가져다 줌.

- 또한 Implicit방식 보다 Explicit방식을 사용할 경우 이를 탐색하는 부하를 더욱 줄일 수 있음

- Statement Cache를 사용함으로써 결국 Parse를 줄일 수 있기 때문에 Parse Call을 위한 Network 부하 및 Latch 획득에 따른 부하를 최소화 할 수 있음

- 또한 Oracle에서 제공하는 Session Cursor Caching기능을 곁들여 사용하게 되면 약간의 성능상의 이득도 있지만 무엇보다도 Library Cache에 대한 사용을 회피할 수 있기 때문에 시스템 전반에 있어 위험 요소를 줄일 수 있으며 개별 세션도 Library Cache의 부하가 심각할 때 상대적으로 파급되는 영향을 최소화 할 수 있음

 

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

Oracle JDBC Driver  (0) 2009.05.26
Statement Cache  (0) 2009.05.26
Oracle Update Batching  (0) 2009.05.26
성능에서의 풍선효과  (0) 2009.04.14
댓글
댓글쓰기 폼