91 // 물리적으로닫지않고 cache에반환만한다. opstmt.close(); } opstmt.setint(3, lowerlimit); opstmt.setint(4, upperlimit); // Execute query rset = opstmt.executequery

Transcription

1 Spring Oracle Korea Magazine *Technology & Developer Technical Tips Oracle JDBC 를이용한성능향상방법쉽게적용할수있는예제들 저자 _ 김정식 Oracle ACE(oramaster@empal.com) JAVA 기반의웹프로젝트를진행하다보면대부분의개발자분들이사용하는 JDBC API들은제한적인것같다. 이번글을통하여 Oracle JDBC에서제공하는성능향상방법들에대해서알아보고, 예제를이용하여개발자분들도쉽게적용할수있게하려고한다. 대표적인성능향상방법으로는동일한구문이반복적으로실행될경우 cache를이용해성능을향상시킬수있는 Statement Caching 기법과매우향상된 Connection Cache 기능을제공하는 Implicit Connection Cache, 다중처리를지원하는 Oracle Update Batching, 운반단위의 fetch 처리를지원하는 Oracle Row Prefetching이있다. 이에대해자세히알아보도록하겠다. 예제파일실행방법 - 예제파일을실행하기위한환경 : Oracle 9i 버전이상, JDK 1.4 버전이상 - 예제소스다운 : - JAVA_HOME 설정 : JDK가설치되어있는디렉토리를환경변수에추가한다. - JDBC_HOME 설정 : ojdbc14.jar 파일이있는디렉토리를환경변수에추가한다. - Oracle HR 사용자계정잠금해제 SQL> conn / as sysdba SQL> ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK; 1. Statement Caching Oracle JDBC는동일한구문이반복적으로실행되는경우미리파싱된정보를재활용함으로써오버헤드를방지하고, 성능을향상시킬수있는 Statement Caching 기능을제공한다. Statement Caching은 Implicit Statement Caching와 Explicit Statement Caching 두가지방법으로구현할수있다. 1 Implicit Statement Caching - Implicit Statement Caching은모든PreparedStatement와 Callable- Statement의 Cache 생성과반환을자동으로관리한다. - OracleConnection의 setimplicitcachingenabled(boolean cache) 메소드를 true로설정한다. - OracleConnection의 setstatementcachesize(int size) 를이용하여 Cache Size를설정한다. - OraclePreparedStatement의 close() 메소드를이용하여 cache에반환한다. - 사용예제 // Statement cache 사이즈를 5로설정 ((OracleConnection) conn).setstatementcachesize(5); // Enable Implicit caching ((OracleConnection) conn).setimplicitcachingenabled(true); // 10 회반복실행 for (int i=1 ; i<=10 ; i++) { //cache에서 statement를검색하여, 존재하면사용하고 // 존재하지않으면 cache에새로운 statement를생성한다. opstmt = (OraclePreparedStatement) conn.preparestatement(mysql); opstmt.setstring(1, empname); opstmt.setint(2, Integer.parseInt(deptId[selectedDept])); opstmt.setint(3, lowerlimit); opstmt.setint(4, upperlimit); // Execute query rset = opstmt.executequery(); // Close the ResultSet

2 91 // 물리적으로닫지않고 cache에반환만한다. opstmt.close(); } opstmt.setint(3, lowerlimit); opstmt.setint(4, upperlimit); // Execute query rset = opstmt.executequery(); 2 Explicit Statement Caching - Implicit Statement Caching은내부적으로 SQL 문장을 cache의 key로사용하지만, Explicit Statement Caching은사용자가직접 Key를지정하여 Cache의생성과반환을관리한다. - OracleConnection의 setexplicitcachingenabled(boolean cache) 메소드를 true로설정한다. - OracleConnection의 setstatementcachesize(int size) 를이용하여 Cache Size를설정한다. - OraclePreparedStatement의 getstatementwithkey(string key) 메소드를이용하여 cache 키를지정한다. - OraclePreparedStatement의 closewithkey(string key) 메소드를이용하여 cache에반환한다. // Close the ResultSet // statement를 close할때key를지정한다. opstmt.closewithkey( searchemployee ); } 3 성능테스트결과 < 그림 1> Statement Caching Sample - 사용예제 // Statement Cache 사이즈를 5로설정 ((OracleConnection) conn).setstatementcachesize(5); // Enable Explicit caching ((OracleConnection) conn).setexplicitcachingenabled(true); Statement Caching Schemes Performance Metrics on Statement Caching Without Cache With Imp Cache With Exp Cache //10 회반복실행 for (int i = 1; i <= 10; i++) { // Explicit cache key 사용. opstmt = (OraclePreparedStatement) Response Time in milli seconds for 10 iterations ((OracleConnection) conn).getstatementwithkey( searchemployee ); // Explicit cache에 statement가존재하지않으면 cache 생성 if (opstmt == null) { // 이단계는맨처음생성될때만호출된다. // 다음단계부터는 statement 문이 Cache로부터처리된다. opstmt = (OraclePreparedStatement) conn.preparestatement(mysql); } 위 < 그림1> 은 Statement Caching을사용하지않은경우와 Implicit Statement Caching을사용한경우, 그리고 Explicit Statement Caching을사용한경우성능을테스트한결과이다. Explicit Statement Caching을사용했을때가장좋은성능이나온다는것을확인할수있었다. 테스트예제는 sample.zip 파일을다운받아아래파일을참고하면된다. - 예제소스 : /src/oracle/otnsamples/jdbc/stmtcache/stmtcachesample.java - 성능테스트파일 : /bin/01.stmtcache.bat opstmt.setstring(1, empname); opstmt.setint(2, Integer.parseInt(deptId[selectedDept]));

3 Spring Oracle Korea Magazine *Technology & Developer Technical Tips 2. Implicit Connection Caching 일반적으로 connection cache 는약간의물리적데이터베이스연결을풀에 서유지하여많은수의동시사용자가공유및재사용할수있게함으로써연결이요청될때마다새데이터베이스연결을설정하고연결이해제될때마다연결을닫아야하는번거로움을없애는방법이다. connection cache는기본적으로 logical connection을이용하여 physical connection을사용하며, connection cache manager 내부적으로향상된 cache 기법을통해서 physical connection들을관리한다. Oracle Database 10g 부터는 Connection Caching 매커니즘이새로운수준으로향상되었는데이를 Implicit Connection Cache라고한다. 1 Implicit Connection Cache 제공기능 Oracle 9i와 Oracle 10g 에서의지원기능비교 Oracle 9i 모든세션은동일데이터베이스내에속하며, 동일사용자에의해서만인증 stale connection 의갱신불가능 connection 에대한검색기능을지원하지않음 connection reclaim 지원하지않음 투명한 connection cache 접근을제공 - 표준 DataSource의 getconnection() API를이용하여 Connection Cache에접근한다. - 사용예제 ods.setuser( scott ); ods.setconnectioncachingenabled(true); ctx.bind( MyDS, ods); ds = (OracleDataSource) ctx.lookup( MyDS ); conn.getconnection(); // transparent cache creation and access conn.close(); // return connection to the cache ds.close(); // finally, close cache enabled DataSource Connection Cache Manager 제공 - cache의생성부터소멸까지관리 Oracle 10g 여러사용자 암호의인증을지원 connection recycling 및 stale connection 에대한갱신지원 cache 속성을통한 connection 검색지원 사용하지않는 connection 에대한 reclaim 가능, connection 재사용지원 - dynamic하게 cache 환경의재구성이가능 - multiple caches 관리를지원그외에사용자정의속성기반으로 Connection을할당하고요청하는 Connection Attributes 기능과, RAC 환경의 Oracle JDBC에서 Failover 기능을할수있는Fast Connection Failover와부하균형을해주는 Run-time Connection Load Balancing 기능을지원한다. 2 Implicit Connection Cache 사용예제 private static final String CACHE_NAME = CacheSample ; // Datasource 초기화 OracleDataSource ods = new OracleDataSource(); // Connection.properties 파일로부터 connection 정보를로딩 Properties prop = this.loadparams( Connection ); // Database 정보 set ods.setservername(prop.getproperty( HostName )); ods.setservicename( prop.getproperty( SID )); ods.setportnumber(new Integer( prop.getproperty( Port )).intvalue()); ods.setdrivertype( thin ); ods.setuser( prop.getproperty( UserName ) ); ods.setpassword( prop.getproperty( Password )); // Enable caching ods.setconnectioncachingenabled(true); // Set the cache name ods.setconnectioncachename(cache_name); //Connection Cache 초기화 OracleConnectionCacheManager connmgr = OracleConnectionCacheManager.getConnectionCacheManagerInstance(); // Cache Manager에서관리될 cache 값설정 Properties properties = new Properties(); // Cache 최소값설정. properties.setproperty( MinLimit, 1 ); // Cache 최대값설정.

4 93 properties.setproperty( MaxLimit, 15 ); // cache 명과, datasource, cache properties를지정하여새로운 cache 생성 connmgr.createcache(cache_name, ods, properties); Connection conn = ods.getconnection();. conn.close() Oracle Connection Cache Manager를사용하는완성된예제는아래파일을참고하면된다. - 예제소스 : /src/oracle/otnsamples/jdbc/conncache/conncachebean.java 3. Update Batching JDBC 2.0부터는 Statement, PreparedStatement 인터페이스의 addbatch(string sql) 메소드를이용하여여러건의데이터를처리할때처리속도를향상시킬수있었다. 한번에여러레코드를 INSERT, UPDATE, DELETE 할경우addBatch() 를이용하여자바에서도 Oracle 데이터베이스에서의 Array Processing 처리를할수있어성능향상을가져왔는데, Oracle JDBC에서는 Oracle-specific model의 addbatch() 기능을제공하여, 더욱더많은성능향상을가져올수있다. s.executebatch(); s.addbatch( insert into dept values ( 26, HR, Mongolia ) ); s.executebatch(); conn.commit(); ps.close();... 2 Oracle Update Batching - 대량의작업일수록성능이향상된다. - 적절한배치사이즈를지정해주어야한다. Oracle에서는 10 정도를추천하지만실제테스트결과 100 정도의사이즈를지정했을때좀더빠른성능을보였다. - Oracle 데이터베이스에서만사용할수있다. - Standard Update Batching보다성능이좋지만, PreparedStatement에서만지원된다. Connection conn = ds.getconnection(); //auto-commit을 false로설정 conn.setautocommit(false); PreparedStatement ps = conn.preparestatement( insert into dept values (?,?,?) ); 1 Standard Update Batching - JDBC2.0을지원하는모든데이터베이스에서사용가능하지만 Oracle-specific model에비해대량의작업일수록처리속도가많이차이난다. - OutOfMemory Error 방지를위하여 executebatch() 를중간에수동으로실행시켜주어야한다. Connection conn = ds.getconnection(); //auto-commit을 false로설정한다. conn.setautocommit(false); Statement s = conn.createstatement(); s.addbatch( insert into dept values ( 23, Sales, USA ) ); s.addbatch( insert into dept values ( 24, Blue Sky, Montana ) ); s.addbatch( insert into dept values ( 25, Applications, India ) ); // 수동으로 batch를실행해야한다. //batch size를설정한다. ((OraclePreparedStatement)ps).setExecuteBatch (3); //queue size #1 ps.setint(1, 23); ps.setstring(2, Sales ); ps.setstring(3, USA ); ps.executeupdate(); // 실행을위해 JDBC queues에넣는다. //queue size #2 ps.setint(1, 24); ps.setstring(2, Blue Sky ); ps.setstring(3, Montana ); ps.executeupdate(); // 실행을위해 JDBC queues에넣는다. //queue size #3 ps.setint(1, 25); ps.setstring(2, Applications ); ps.setstring(3, India );

5 Spring Oracle Korea Magazine *Technology & Developer Technical Tips ps.executeupdate(); //queue의사이즈가 batch 사이즈 3과같다. //database에처리요청을보낸다. //queue size #1 ps.setint(1, 26); ps.setstring(2, HR ); ps.setstring(3, Mongolia ); ps.executeupdate(); // 실행을위해 JDBC queues에넣는다. 1 Standard Fetch Size - Statement, PreparedStatement, CallableStatement, ResultSet 객체의 setfetchsize(int rows) 메소드를이용하여 fetch size를설정하고얻을수있다. Connection conn = DriverManager.getConnection( jdbc:oracle:thin:@localhost:1521:ora 10G, scott, tiger ); ((OraclePreparedStatement)ps).sendBatch(); // database에처리요청을보낸다. conn.commit(); ps.close();... 3 Standard Update Batching과 Oracle Update Batching 성능테스트 Statement stmt = conn.createstatement(); stmt.setfetchsize(100); ResultSet rset = stmt.executequery( SELECT ename FROM emp ); while( rset.next () ) System.out.println( rset.getstring (1) ); 데이터건수 Standard Update Batching Oracle Update Batching 단위 900건 milliseconds 9000건 milliseconds 90000건 milliseconds 위표결과는필자가직접테스트한결과로데이터건수가많을수록 Oracle Update Batching의성능이좋은것을확인할수있다. 예제소스는아래파일을참고하면된다. - 예제소스 /src/oracle/otnsamples/jdbc/updbatch/updatebatching.java /src/oracle/otnsamples/jdbc/updbatch/basicbatching.java 4. Standard Fetch Size and Oracle Row Prefetching JDBC 2.0부터 Statement, PreparedStatement, CallableStatement, ResultSet 객체의 setfetchsize(int rows) 메소드를설정하여데이터베이스커서로부터한번에반환받을수있는데이터 Rows의수를지정할수있다. 기본값은 10으로되어있으며, fetch size를적절하게설정을하면 Query 작업수행시서버와의 round trip 을감소시켜성능향상을가져온다. JDBC의 Standard Fetch와 Oracle의 Row Prefetch는기본적으로비슷하지만, Oracle JDBC를사용하고있다면 OracleConnection의 setdefault- RowPrefetch(int value) 메소드를이용해서모든 Statement의기본fetch size를쉽게설정해대량의데이터를조회하는시스템에서효과적으로사용할수있다. rset.close() stmt.close(); 2 Oracle Row Prefetching - OracleStatement, OraclePreparedStatement, OracleCallableStatement 객체의 setrowprefetch(int rows) 메소드를이용하여 prefetch value를설정한다. - OracleConnection 객체의 setdefaultrowprefetch(int value) 메소드를이용하여 prefetch value를설정한다. Connection conn = DriverManager.getConnection( jdbc:oracle:thin:@localhost: ORA10G, scott, tiger ); //connection의 default prefetch value를설정한다. ((OracleConnection)conn).setDefaultRowPrefetch(7); Statement stmt = conn.createstatement(); ResultSet rset = stmt.executequery( SELECT ename FROM emp ); while( rset.next () ) System.out.println( rset.getstring (1) );

6 95 // 다음 statement에대해서 prefetch value를새로설정한다. ( (OracleStatement)stmt ).setrowprefetch (2); rset = stmt.executequery( SELECT ename FROM emp ); while( rset.next() ) System.out.println( rset.getstring (1) ); stmt.close(); 3 Oracle Row Prefetching 성능테스트아래 < 그림2> 와 < 그림3> 은 Oracle JDBC의 prefetch value를기본값과 100, 200으로설정하여 642건의데이터와 438,272건의데이터를테스트한결과이다. < 그림3> 에서보는봐와같이데이터가많은경우 prefetch value를지정하지않았을때와 200으로지정했을때엄청난속도차이가나는것을확인할수있다. - 예제소스 : /src/oracle/otnsamples/jdbc/stmtcache/stmtcachesample.java - 성능테스트파일 : /bin/01.stmtcache.bat 글을마치며, JAVA 기반의웹프로젝트에서개발자분들이애플리케이션의성능향상을높이기위한방법으로는두가지관점으로접근해볼수가있다. 첫번째는효율적인 SQL 문장을작성하는것이다. SQL 문장을효율적으로작성하기위해서는끊임없는학습과많은경험이중요하다. 두번째로는적재적소에맞게 JDBC API를사용하는것이다. 오라클데이터베이스를사용한다면, Oracle JDBC에서지원해주는효율적인기능들을사용해보기바란다. 아래 Reference에보면Oracle 데이터베이스에서사용할수있는유용한기능들이많이있으니확인해보기바란다. < 그림 2> 642 건의데이터를테스트한결과 < 그림 3> 건의데이터를테스트한결과 Reference - Advanced JDBC Sample /advanced/advanced.html - Basic JDBC Sample /basic/basic.html - JDBC3.0 Sample /jdbc30/index.html - JDBC How-To Documents /jdbc/index.html - Oracle10g JDBC Samples /oracle10g/index.html - Oracle JDBC Center