[JDBC] Oracle JDBC를 이용한 성능향상 방법

1. Statement Caching

① Implicit Statement Caching

// Statement cache 사이즈를 5로 설정
((OracleConnection) conn).setStatementCacheSize(5);
// Enable Implicit caching
((OracleConnection) conn).setImplicitCachingEnabled(true);

② Explicit Statement Caching

// Statement Cache 사이즈를 5로 설정
((OracleConnection) conn).setStatementCacheSize(5);
// Enable Explicit caching
((OracleConnection) conn).setExplicitCachingEnabled(true);

 

2. Implicit Connection Caching

private static final String CACHE_NAME =“ CacheSample”;
// Datasource 초기화
OracleDataSource ods = new OracleDataSource();

// Enable caching
ods.setConnectionCachingEnabled(true);
// Set the cache name
ods.setConnectionCacheName(CACHE_NAME);

// cache, datasource, cache properties를 지정하여 새로운 cache생성
connMgr.createCache(CACHE_NAME, ods, properties);
Connection conn = ods.getConnection();

 

3. Update Batching

① Standard Update Batching

Connection conn = ds.getConnection();
conn.setAutoCommit(false);

Statement s = conn.createStatement();

s.addBatch( “insert into dept values‘ ( 26’,‘HR’,‘Mongolia’)”);
s.executeBatch();

conn.commit();
s.close();

② Oracle Update Batching

Connection conn = ds.getConnection();
conn.setAutoCommit(false);

PreparedStatement ps =
conn.prepareStatement(“ insert into dept values (?, ?, ?)”);

((OraclePreparedStatement)ps).setExecuteBatch (3);

ps.executeUpdate();
((OraclePreparedStatement)ps).sendBatch();

conn.commit();
ps.close();

 

4. Standard Fetch Size and Oracle Row Prefetching

① Standard Fetch Size

Statement stmt = conn.createStatement();
stmt.setFetchSize(100);

② Oracle Row Prefetching

((OracleConnection)conn).setDefaultRowPrefetch(7);
((OracleStatement)stmt).setRowPrefetch (2);

 

 

<출처>
2007 Spring Oracle Korea Magazine
Oracle JDBC를 이용한 성능향상 방법
저자_김정식 Oracle ACE(oramaster@empal.com)