I've downloaded the latest version of Oracle
JDBC Driver v9.2 and tried the new statement caching feature and see how it works within connection pooling. I wrote this class that basically would get a connection from pool and loop throught a query 10 times (without statement caching) and return the elapsed time, then the do the same with statement caching.
How I have been keep getting ;
C:\java\projects\ora_pool_stmt_cache>
java stmt_cache_conn_pool
Elapsed Time without Statement Caching :150
Error connecting to database :java.sql.SQLException: Statement Caching cannot be enabled for this logical connection.
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.OracleConnection;
public class stmt_cache_conn_pool {
public static void main(
String[] args) {
OracleConnectionCacheImpl myConnectionPool=null;
OracleConnectionPoolDataSource myDataSource=null;
Connection ora_conn = null;
Connection sc_imp_ora_conn = null;
PreparedStatement ora_stmt = null;
ResultSet ora_rs = null;
String SQL_stmt = "select * from table1";
long start = 0, end = 0;
int x = 0;
try {
//Create oracle datasource instance
myDataSource = new OracleConnectionPoolDataSource();
// Set connection parameters
myDataSource.setDriverType("thin");
myDataSource.setNetworkProtocol("tcp");
myDataSource.setServerName("myServer");
myDataSource.setDatabaseName("myDB");
myDataSource.setPortNumber(1521);
myDataSource.setUser("user");
myDataSource.setPassword("password");
//Create & configure pool
myConnectionPool = new OracleConnectionCacheImpl(myDataSource);
myConnectionPool.setMaxLimit(10);
myConnectionPool.setMinLimit(3);
myConnectionPool.setCacheScheme(OracleConnectionCacheImpl.FIXED_RETURN_NULL_SCHEME);
ora_conn = myConnectionPool.getConnection();
// Prepare, Execute Query without Statement Caching
// Set Start Time
start = System.currentTimeMillis();
// Loop, prepare and execute the query 10 times
for (int i = 0;i < 10;i++) {
ora_stmt = ora_conn.prepareStatement(SQL_stmt);
ora_rs = ora_stmt.executeQuery();
ora_rs.close();
ora_stmt.close();
}
// Set End Time
end = System.currentTimeMillis();
// Display the duration
System.out.println("Elapsed Time without Statement Caching :"+((int) (end-start)));
// close connection for non-statement caching
ora_conn.close();
sc_imp_ora_conn = (OracleConnection)myConnectionPool.getConnection();
// Prepare, Execute Query with Implicit Statement Caching
// Set Start Time
// Set the Statement cache size to 5
((OracleConnection) sc_imp_ora_conn).setStatementCacheSize(5);
// Enable Implicit caching
((OracleConnection) sc_imp_ora_conn).setImplicitCachingEnabled(true);
start = System.currentTimeMillis();
// Loop, prepare and execute the query 10 times
for (int i = 0;i < 10;i++) {
ora_stmt = (OraclePreparedStatement) sc_imp_ora_conn.prepareStatement(SQL_stmt);
ora_rs = ora_stmt.executeQuery();
ora_rs.close();
ora_stmt.close();
}
// Set End Time
end = System.currentTimeMillis();
// Display the duration
System.out.println("Elapsed Time with Implicit Statement Caching:"+((int) (end-start)));
sc_imp_ora_conn.close();
}
catch (Exception ex) {
System.out.println("Error connecting to database :"+ex);
}
// close physical database (pooled) connections
try {
if (myConnectionPool != null) myConnectionPool.close();
} catch (SQLException SQLEx) {
System.out.println("Error disconnecting to database :"+SQLEx);
}
}
}