티스토리 뷰

기술이야기

Oracle Update Batching

novathinker 2009.05.26 18:59

대량의 DML작업을 하기 위해서는 기본적으로 각각 insert, update, delete문을 만들어 수행하게 된다. 그러나 같은 SQL이라 하더라도 이를 실행(Execute)하는 방법에 따라 그 성능의 차이가 나타날 수 있다.

우리는 일반적으로 SQL을 수행할 때 PreparedStatement라는 객체를 이용하여 작업을 한다. 대량의 DML을 할 때 우리는 PreparedStatement에 SQL을 지정하고 루프내에서 파라메터를 바인딩 하여 실행한다. 그러나 실제로 이 바인딩 된 SQL의 실행은 언제 하느냐에 따라 처리 속도의 차이는 극명하게 나타난다. 우리가 PreparedStatement를 사용하게 되면 SQL의 실행에 있어 두가지 옵션을 가지게 된다.

첫째로 바인딩 이후에 Execute를 하는 경우이다. 이 경우는 루프 내에서 매 건마다 네트웍을 경유하여 Oracle로 하여금 SQL을 처리하게 된다.

다른 하나의 옵션은 바로 Batch Execute를 하는 경우이다. 이 경우는 루프내에서 batch size만큼 바인딩하여 Batch Size에 다다르면 그때 가서 Execute를 하는 경우이다.

그리고 또 한가지 옵션이 있을 수 있다. 그것은 Oracle이 자신의 JDBC 드라이버 내에서 구현한 객체를 이용하는 것이다. 이 기능을 Oracle Update Batching이라고 한다. 이것을 위해 오라클은 JDBC표준인 PreparedStatement를 상속한 OraclePreparedStatement라는 객체를 제공한다. 물론 이것은 JDBC표준이 아니기 때문에 다른 DB에서는 사용할 수 없다.

JDBC Batch를 사용할 경우 명시적으로 Batch Size 에 도달했는지를 판단하여 작업을 수행하는 코드를 삽입해야 한다. 그러나 Oracle Update Batching은 Batch Size를 객체에 알려주기만 하면 알아서 실행을 하게 된다. 이런 편리함과 더불어 성능에 있어서도 유리함이 있다고 알려져 있다.

이 글에서는 Batch를 사용할 때의 성능을 비교함과 동시에 JDBC Standard Batch와 Oracle Update Batch와의 성능 비교, 그리고 Batch Size에 대한 추이 등을 실증해 보도록 하겠다. 기본적으로 이 테스트들은 Insert 작업을 가지고 수행하였다. 일반적으로 대량의 Update나 Delete를 건건이 수행하는 일보다는 하나의 SQL로 조건을 주어 작업하는 경우가 대부분이기 때문이다. 그러나 성능을 비교하는 것이 이 글의 주 목적이기 때문에 이에 대한 테스트도 수행하였음을 미리 밝힌다.

  1. 테스트 환경

테스트 환경은 2 Tier로 구성을 하였다.

Client : CPU – Intel() T2400 CPU 1.83Ghz

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

Memory – 2 GB

Oracle - Version : 10.2.0.3

  • Total System Global Area 314572800 bytes
  • Fixed Size 2072384 bytes
  • Variable Size 251658432 bytes
  • Database Buffers 54525952 bytes
  • Redo Buffers 6316032 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정보 이 두 가지로 구성된다. 이 정보를 위해 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 ;

 

Application은 건건이 Execute를 하는 경우와 JDBC Batch를 사용하는 경우 그리고 Oracle Update Batch를 이용하는 경우를 각각 테스트 할 수 있도록 구성되었으며 컨넥션을 얻어오는 부분은 재활용이 가능하도록 하였다.

 

/*

* OraUtil.java (connection을 얻어옴)

* /

 

package exem.oracle.batch;

 

import java.sql.*;

 

public class OraUtil {

    public static Connection getConnection(String mod, String act){

        Connection conn=null;

        try {

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

            conn= DriverManager.getConnection("jdbc:oracle:thin:@210.122.227.247:1522:WASDB", "persistence", "persistence");    

            conn.setAutoCommit(false);            

        } catch (Exception e) {

            e.printStackTrace();

        }

        return conn;        

    }

      

    

    public static void cutConnection(Connection conn, String tag, boolean trunc, 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);

            cstmt.execute();

            if (trunc) {

                pstmt = conn.prepareStatement("truncate table oraclebatch");

                pstmt.execute();

                pstmt.close();

            }

        } catch(Exception e) {

            e.printStackTrace();

        } finally {

            try {

                cstmt.close();

             conn.close();

            } catch (Exception e2) {}

        }

    }    

    

}

 

/*

* EachRow.java (한건씩 수행)

* arg : Insert, Update, Delete의 세가지 작업 종류

* /

package exem.oracle.batch;

 

import java.sql.*;

 

 

public class EachRow {

    private static Connection conn;

 

    public static void main(String[] args) {

        int loops = 1000000;

        boolean result = false;

        String act = "EachRow_"+args[0];

        conn=OraUtil.getConnection( "OracleBatch", act);

        long chktime = System.currentTimeMillis();    

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

            insert(loops);

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

            update(loops);

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

            delete(loops);

        }

        

        OraUtil.cutConnection(conn, act,result, (System.currentTimeMillis()-chktime)/1000, Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory() );    

    }

 

    public static void insert(int loops) {

        /*

         * create table oraclebatch (col1 number)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("insert into oraclebatch values(?)");

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

             stmt.setInt(1,i);             

             stmt.executeUpdate();

         }

         conn.commit();

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }

    

    public static void update(int loops) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("update oraclebatch set col1 = col1+1000000 where col1 = ?");

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

             stmt.setInt(1,i);

             stmt.executeUpdate();

         }

         conn.commit();    

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }

    

    public static void delete(int loops) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("delete oraclebatch where col1 = ?");

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

             stmt.setInt(1,i);

             stmt.executeUpdate();         

         }

         conn.commit();

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }            

}

 

/*

* Batch.java (JDBC Batch 작업)

* arg 1 : Insert, Update, Delete의 세가지 작업 종류

* arg 2 : Batch Size

* /

package exem.oracle.batch;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

 

public class Batch {

    private static Connection conn;

 

    public static void main(String[] args) {

        int loops = 1000000;

        int batch = Integer.parseInt(args[1]);

        boolean result = false;

        String act = "StandardBatch_"+args[0]+"_"+args[1];        

        conn=OraUtil.getConnection( "OracleBatch", act);

        long chktime = System.currentTimeMillis();        

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

            result = true;

            insert(loops, batch);

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

            update(loops, batch);

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

            delete(loops, batch);

        }

        

        OraUtil.cutConnection(conn, act,result, (System.currentTimeMillis()-chktime)/1000, Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory() );        

    }

 

    public static void insert(int loops, int batch) {

        /*

         * create table oraclebatch (col1 number)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("insert into oraclebatch values(?)");        

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

             stmt.setInt(1,i);

             stmt.addBatch();             

             if (i%batch==0) {stmt.executeBatch();}         

         }

         stmt.executeBatch();

         conn.commit();    

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }

    

    public static void update(int loops, int batch) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("update oraclebatch set col1 = col1+1000000 where col1 = ?");

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

             stmt.setInt(1,i);

             stmt.addBatch();

             if (i%batch==0) {stmt.executeBatch();}

         }

         stmt.executeBatch();

         conn.commit();

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }

    

    public static void delete(int loops, int batch) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("delete oraclebatch where col1 = ?");

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

             stmt.setInt(1,i);

             stmt.addBatch();

             if (i%batch==0) {stmt.executeBatch();}

         }

         stmt.executeBatch();

         conn.commit();

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }        

 

}

 

/*

* OracleBatch.java (Oracle Update Batch 작업)

* arg 1 : Insert, Update, Delete의 세가지 작업 종류

* arg 2 : Batch Size

* /

package exem.oracle.batch;

 

import java.sql.Connection;

import oracle.jdbc.OraclePreparedStatement;

 

public class OracleBatch {

    private static Connection conn;

 

    public static void main(String[] args) {

        int loops = 1000000;

        int batch = Integer.parseInt(args[1]);

        boolean result = false;

        String act = "OracleBatch_"+args[0]+"_"+args[1];        

        conn=OraUtil.getConnection( "OracleBatch", act);

        long chktime = System.currentTimeMillis();

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

            result = true;

            insert(loops, batch);

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

            update(loops, batch);

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

            delete(loops, batch);

        }

        

        OraUtil.cutConnection(conn, act,result, (System.currentTimeMillis()-chktime)/1000, Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory() );

    }

 

    public static void insert(int loops, int batch) {

        /*

         * create table oraclebatch (col1 number)

         */

        try {

         OraclePreparedStatement ostmt = (OraclePreparedStatement) conn.prepareStatement("insert into oraclebatch values(?)");

         ostmt.setExecuteBatch(batch);

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

             ostmt.setInt(1,i);

             ostmt.executeUpdate();             

         }

         ostmt.sendBatch();

         conn.commit();

         ostmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }

    

    public static void update(int loops, int batch) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

            OraclePreparedStatement ostmt = (OraclePreparedStatement)conn.prepareStatement("update oraclebatch set col1 = col1+1000000 where col1 = ?");

         ostmt.setExecuteBatch(batch);

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

             ostmt.setInt(1,i);

             ostmt.executeUpdate();    

         }

         ostmt.sendBatch();

         conn.commit();

         ostmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }

    

    public static void delete(int loops, int batch) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

            OraclePreparedStatement ostmt = (OraclePreparedStatement)conn.prepareStatement("delete oraclebatch where col1 = ?");

         ostmt.setExecuteBatch(batch);            

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

             ostmt.setInt(1,i);

             ostmt.executeUpdate();    

         }

         ostmt.sendBatch();

         conn.commit();

         ostmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }    

}

 

 

  1. Test1 : 각각 Insert를 수행 vs Batch Insert

첫 번째 테스트는 Batch를 사용하는 것과 건건마다 수행을 하는 것과의 비교를 하는 것이다. 이것은 클라이언트와 서버와의 빈번한 roundtrip이 성능에 얼마나 영향을 끼치고 이 성능 이슈가 단지 네트웍의 문제로 국한되느냐를 알아보기 위한 실험이다.

 

실험은 다음과 같이 진행되었다. Oraclebatch라고 하는 테이블에 PreparedStatement를 사용하여 1,000,000 건의 데이터를 계속해서 insert를 하는데 한 번은 파라메터를 바인딩 할 때마다 실행(execute)하고 다른 한번은 2000건 마다 한 번씩 실행하고 나머지 한 번은 2000건 마다 수행하는 것은 동일하나 PreparedStatement 대신 OraclePreparedStatement를 사용할 것이다.

실험 결과는 다음과 같다.

 

TOTAL_ELAPSED

APP_WAIT

DB_CPU

DB_WAIT

DB_ELAPSED

LOGICAL_READS

PHYSICAL_READS

Each Row

575.25

29.06

44.02

502.17

546.19

1030744

2

JDBC 2000 Batch

1.891

0

0.72

1.24

1.96

21941

2

Oracle 2000 Batch

1.953

0

0.69

1.29

1.98

21932

2

 

- 실험 결과 Batch의 사용여부에 따라 일량과 시간에서 모두 두드러진 차이를 보임

- Oracle의 Memory IO인 Logical reads로 판단할 수 있는 일량의 변화는 50배 가량의 차이를 나타내고 있음

- 아래의 표에서 보면 일량의 변화는 이는 Array Processing에 의한 효과인 것으로 생각됨

buffer is not pinned count

EachRow_Insert

110

OracleBatch_Insert_2000

110

StandardBatch_Insert_2000

110

buffer is pinned count

EachRow_Insert

0

OracleBatch_Insert_2000

0

StandardBatch_Insert_2000

0

execute count

EachRow_Insert

1000137

OracleBatch_Insert_2000

589

StandardBatch_Insert_2000

589

user calls

EachRow_Insert

1000006

OracleBatch_Insert_2000

506

StandardBatch_Insert_2000

506

recursive calls

EachRow_Insert

1643

OracleBatch_Insert_2000

1211

StandardBatch_Insert_2000

1211

redo entries

EachRow_Insert

1006883

OracleBatch_Insert_2000

12509

StandardBatch_Insert_2000

12509

redo size

EachRow_Insert

245082360

OracleBatch_Insert_2000

15761668

StandardBatch_Insert_2000

15761592

 

- Array Processing의 정확한 작동 원리에 대한 것은 알 수 없으나 이는 수행량의 차이와 Redo의 사용량의 차이로 보여짐

- Array Processing을 사용하는 PL/SQL의 Bulk Insert가 같은 방식으로 동작하는지를 확인하기 위해 아래와 같은 프로시저를 사용하여 테스트

CREATE OR REPLACE procedure bulk_insert

IS

 

TYPE NumList IS TABLE OF NUMBER;

 

ARGS NumList:=NumList();

 

buf number := 1;

 

begin

 

FOR A IN 1 .. 1000000 LOOP

args.extend;

ARGS(A) := A;

 

if mod(a,2000)=0 then

FORALL I IN buf .. buf + 1999

EXECUTE IMMEDIATE

'INSERT INTO ORACLEBATCH VALUES(:1)'

USING ARGS(I) ;

buf := a - 1;

end if;

 

END LOOP;

 

 

db_profiler('Bulk_Insert');

 

commit;

 

 

 

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE( SQLERRM );

END ;

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

SQL> exec bulk_insert;

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:02.65

- 프로시저를 수행하여 얻은 결과는 다음과 같음

Bulk_Insert

CPU used by this session

182

execute count

599

physical reads

2

recursive calls

1749

redo entries

12518

redo size

15766356

session logical reads

21996

SQL*Net roundtrips to/from client

12

user calls

19

- 300건의 batch작업과 비교해 보면 수행시간은 절반으로 줄었지만 일량은 거의 비슷한 수준

- 수행시간이 줄어든 이유는 Oracle의 CPU 사용시간도 두 Batch의 평균인 0.7초에서 0.18초로 줄긴 했지만 Oracle의 대기 시간이 현격히 줄었기 때문임

- Batch작업과 PL/SQL의 작업에서 또 하나의 차이는 User Calls와 Recursive Calls의 차이임. 두 지표를 합친 수치는 모두 비슷하나 Batch작업의 경우 User Calls의 비중이 높고 PL/SQL의 경우는 Recursive Calls의 비중이 높음.

- 이것이 실제 Oracle이 작업한 시간을 감소시킨 원인으로 보여짐

- 그밖에 Redo의 사용이나 Memory IO등의 작업, 그리고 수행횟수 등에서는 큰 차이가 나타나지 않는 것으로 보아 JDBC를 통해서 작업한 Batch작업과 PL/SQL을 사용하는 Bulk Insert의 내부적인 구현은 비슷하다고 보여짐

- Bulk Insert의 핵심은 Array Processing으로 이것을 사용하는 것이 성능 개선에 도움을 주는 것을 다시 한번 확인 할 수 있음

- Array Processing이란 SQL에 바인딩할 때 사용하는 변수를 Array형태로 삽입하여 수행하는 것으로 한 번의 DB Call로 여러 건의 데이터를 처리할 수 있도록 하는 것

- 다시 1건씩 수행한 작업과 batch작업의 테스트로 돌아와서 수행시간을 살펴보면 전체 수행시간에서 아래의 그래프 처럼 Oracle의 대기 시간이 굉장히 큰 영향을 주고 있음을 알 수 있음

 

- Oracle의 대기시간을 세부 Oracle Wait Event로 나타내 보면 아래의 그래프와 같이 나타남

 

 

EVENT

WAIT_TIME

TOTAL_WAITS

EachRow_Insert

SQL*Net message from client

499.21

1000003

log file switch completion

1.05

9

log file switch (checkpoint incomplete)

1

3

SQL*Net message to client

0.9

1000003

log file sync

0.01

1

OracleBatch_Insert_2000

SQL*Net message from client

1.19

503

SQL*Net more data from client

0.09

2495

StandardBatch_Insert_2000

log file sync

0.01

1

SQL*Net message from client

1.12

503

 

- Oracle은 대부분의 시간을 SQL*Net message from client라는 이벤트를 대기하면서 보내고 있음

- 이 이벤트는 말 그대로 client로부터 메시지를 기다리는 것으로 Oracle이 Response이후에 Application에서 다음 Request를 할 때까지 대기하는 시간으로 이는 application과 Oracle 사이의 roundtrip의 정보임

SQL*Net roundtrips to/from client

EachRow_Insert

1000003

OracleBatch_Insert_2000

503

StandardBatch_Insert_2000

503

- 실제로 Oracle이 남겨놓은 통계정보를 보면 Rondtrup의 횟수와 SQL*Net message from client 이벤트 발생횟수가 동일함을 알 수 있음

- 미루어 보면 Client와 server와의 빈번한 roundtrip이 성능에 있어 상당한 악영향을 끼치는 것을 확인 할 수 있음

 

 

  1. Test2 : Batch size에 따른 성능비교

- 실행시 Batch를 사용하는 것이 성능을 개선시킨다는 것은 알았으나 Batch Size를 어떻게 해야 하는 지에 대한 기준이 명확하지 않음

- Oracle Manual에서는 Batch size를 5~30사이로 하는 것이 성능에 좋으며 그 이상은 성능에 부정적인 영향을 준다고 함

- 그래서 위의 application에서 batch size를 10부터 100000까지 다양하게 테스트를 수행

- batch Size별 소요시간은 70까지는 급격한 감소를 보이다가 70~100까지는 오히려 수행시간이 증가하고 그 이후부터는 완만하게 감소하고 있음을 알 수 있음.

- JDBC Standard Batch와 Oracle Update Batch의 경우 평균 수행시간은 각각 7.39초와 7.32로 아주 근소한 차이로 Oracle Update Batch가 빠른 것으로 보임

 

Oracle Update Batch

JDBC Standard Batch

10~90

20.34

20.24

100~900

5.57

5.96

1000~9000

2.34

2.05

10000~90000

1.77

1.99

100000

0.89

1.29

- 또한 Batch Size가 커질수록 Oracle update Batch의 수행시간이 JDBC Batch보다 점점 유리하다는 것을 알 수 있음

- 그러나 Batch Size를 크게 잡으면 수행시간에서는 큰 이득이 있을지는 모르지만 Application이 사용하는 Memory의 크기가 증가하는 side effect가 발생

- 아래의 그래프는 사용하는 Memory의 추이를 나타내는 것

- Batch Size의 크기에 따라 Memory의 크기는 수행시간의 추이와는 반비례하고 있는 것으로 나타남

- 이 테스트의 경우 3000건부터 급격하게 증가하고 있으며 300건부터 2000건 까지는 비슷한 메모리 사용 추이를 보여주고 있음

- 수행 시간은 100건 이후로 완만하게 감소하고 있고 메모리의 증가는 2000건까지 비슷한 추이를 보여주고 있기 때문에 이 테스트 케이스에서 가장 최적의 Batch Size는 2000으로 볼 수 있음

- 그러나 2000의 Batch Size가 항상 최적이 아님을 명심해야 함. Network을 타고 다니는 데이터의 양과 application과 Oracle간의 Network환경, Oracle의 버전 등 여러 가지 변수가 이를 변경시킬 수 있기 때문에 상황에 맞도록 이를 찾아보아야 함

 

  1. Test 3: Update와 Delete의 경우

- Insert와는 달리 Update와 Delete의 경우는 하나의 SQL을 통해 집합적으로 데이터를 처리 할 수 있음

- 내부적으로 하나의 SQL로 처리할 경우와 한 건씩 Update, Delete하는 경우, 그리고 Insert에서 최적으로 나타났던 2000의 Batch size를 가지고 Batch작업을 수행하는 경우를 각각 비교해 볼 것임

- 이를 위해 새로운 Application을 작성

package exem.oracle.batch;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

 

public class OneSql {

    private static Connection conn;

 

    public static void main(String[] args) {

        int loops = 1000000;

        boolean result = false;

        String act = "OneSql_"+args[0];

        conn=OraUtil.getConnection( "OracleBatch", act);

        long chktime = System.currentTimeMillis();    

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

            update(loops);

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

            delete(loops);

        }

        OraUtil.cutConnection(conn, act,result, (System.currentTimeMillis()-chktime)/1000, Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory() );

    }

    

    public static void update(int loops) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("update oraclebatch set col1 = col1+1000000");

         stmt.executeUpdate();

         conn.commit();    

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }

    

    public static void delete(int loops) {

        /*

         * create table oraclebatch (col1 number)

         * create unique index oraclebatch_idx1 on oraclebatch(col1)

         */

        try {

         PreparedStatement stmt = conn.prepareStatement("delete oraclebatch");

         stmt.executeUpdate();         

         conn.commit();

         stmt.close();

        } catch (Exception e) {

            e.printStackTrace();

        }        

    }    

}

- 또한 Batch라 하더라도 where절에는 1건씩 찾아서 작업을 하도록 되어 있으므로 Unique인덱스를 생성하기로 함

create unique index oraclebatch_idx1 on oraclebatch(col1);

 

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

TAG

TOTAL_ELAPSED

APP_WAIT

DB_CPU

DB_WAIT

DB_ELAPSED

LOGICAL_READS

OneSql_Update

72.078

1.068

51.38

19.63

71.01

7129828

EachRow_Update

669.502

35.992

107.77

525.74

633.51

13717622

StandardBatch_Update_2000

73.328

0.878

64.11

8.34

72.45

13733520

OracleBatch_Update_2000

69.016

0

62.88

6.33

69.21

13732866

 

 

TAG

TOTAL_ELAPSED

APP_WAIT

DB_CPU

DB_WAIT

DB_ELAPSED

LOGICAL_READS

OneSql_Delete

37.734

0.804

31.66

5.27

36.93

4069726

EachRow_Delete

622.765

32.355

78.1

512.31

590.41

7065255

StandardBatch_Delete_2000

43.062

0.882

40.42

1.76

42.18

7064747

OracleBatch_Delete_2000

45.703

0.733

40.43

4.54

44.97

7064570

 

- 당연하겠지만 update나 Delete모두 OneSql의 경우 건건마다 수행하는 경우에 비해 상당한 성능 개선 효과가 나타난 것으로 보임

- 이도 또한 DB의 Wait을 보면 그 이유를 판단할 수 있음

TAG

EVENT

WAIT_TIME

TOTAL_WAITS

EachRow_Update

SQL*Net message from client

511.76

1000003

db file sequential read

5.69

3937

log file switch (checkpoint incomplete)

3.96

11

log file switch completion

3.23

29

SQL*Net message to client

1.04

1000003

events in waitclass Other

0.03

10

log file sync

0.02

3

OneSql_Update

db file sequential read

13.28

2128

log file switch (checkpoint incomplete)

8.68

20

db file scattered read

2.45

131

log file switch completion

0.46

24

SQL*Net message from client

0.31

4

db file parallel read

0.1

1

buffer busy waits

0.04

1

log file sync

0.01

1

 

 

TAG

EVENT

WAIT_TIME

TOTAL_WAITS

EachRow_Delete

SQL*Net message from client

509.49

1000003

log file switch (checkpoint incomplete)

0.99

2

SQL*Net message to client

0.91

1000003

log file switch completion

0.84

21

db file sequential read

0.08

3115

OneSql_Delete

db file sequential read

4.25

3808

log file switch completion

1.59

22

log file switch (checkpoint incomplete)

1

3

log file switch completion

0.6

25

log buffer space

0.5

3

db file scattered read

0.27

127

SQL*Net message from client

0.2

4

SQL*Net message from client

0.16

4

db file scattered read

0.15

115

log buffer space

0.1

1

db file sequential read

0.04

61

log file sync

0.01

1

log file sync

0.01

1

 

- Update, Delete 모두 SQL*Net message from client라는 이벤트를 과다하게 겪고 있음

- One SQL의 Delete작업을 제외하고는 Logical Read의 양이 비슷한 것으로 보아 이도 또한 application과 Oracle사이의 roundtrip이 상당한 영향을 미치는 것으로 보임

- One SQL의 Delete의 경우 일량이 다르게 나타나는 이유는 Stat지표를 보면 다음과 같은 차이를통해 알 수 있음

buffer is not pinned count

OneSql_Delete

2

EachRow_Delete

6

buffer is pinned count

OneSql_Delete

998485

EachRow_Delete

0

- Oracle은 Buffer cache의 특정 블록을 하나의 query 수회 이상 방문할 경우 해당 블록에 Pin을 걸어 놓게 되고 이를 다시 Memory read할 경우 Logical read의 수치가 증가하는 것이 아니고 Buffer is pinned count라는 통계지표의 수치가 증가함

- 그러므로 Logical read가 차이가 난다 하더라고 그만큼의 일량은 수행한 것으로 판단할 수 있음

 

 

  1. 결론

- Batch를 사용 여부는 성능에 큰 영향을 미침

-이는 단지 Network 부하를 줄인다는 의미뿐만이 아니라 Oracle의 처리량을 줄이는 효과도 가지고 오기 때문에 성능이 개선됨

- 성능에 있어서 중요한 것은 SQL을 Call하는 횟수를 가능한 줄여야 한다는 것

- Update, delete의 경우는 하나의 SQL을 통해 다수의 row들을 처리할 수 있기 때문에 굳이 Batch를 사용하지 않아도 됨

 

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

Statement Cache  (0) 2009.05.26
Oracle Update Batching  (0) 2009.05.26
성능에서의 풍선효과  (0) 2009.04.14
Transaction 중심의 성능 분석법  (0) 2009.04.01
댓글
댓글쓰기 폼