使用jdbc时缓存PreparedStatement的简单测试

一、测试方案简单说明

1、无连接池

Class.forName(driverClass); conn = DriverManager.getConnection(jdbcUrl, user, password);

2、无连接池,使用oracle专有的缓存机制

((oracle.jdbc.OracleConnection) conn).setImplicitCachingEnabled(true);
((oracle.jdbc.OracleConnection) conn).setStatementCacheSize(10);

3、使用DBCP连接池,无缓存

4、使用DBCP连接池,带缓存

poolPreparedStatements=false

maxOpenPreparedStatements=10

5:使用C3P0连接池,无缓存

6、使用C3P0连接池,带缓存

maxStatementsPerConnection=10

二、oracle中检查解析次数方法(测试期间数据库没有其他访问用户):

select name, value from v$sysstat
where name like ‘%parse%’;

三、测试结果:

parse count (total) delta parse count test scenario excute time
63738
64043 296 DBCP without cached prepared statement 2110
64305 253 DBCP cached prepared statement 1547
64405 91 DBCP cached prepared statement 1547
64505 91 DBCP cached prepared statement 4469
64805 291 DBCP without cached prepared statement 2093
65104 290 DBCP without cached prepared statement 2078
65194 81 C3P0 cached prepared statement 1391
65284 81 C3P0 cached prepared statement 1375
65384 91 C3P0 cached prepared statement 1406
65685 292 C3P0 without cached prepared statement 1969
65982 288 C3P0 without cached prepared statement 1969
66271 280 C3P0 without cached prepared statement 1938
66489 209 direct connection without cached prepared statement 1640
66711 213 direct connection without cached prepared statement 1828
66929 209 direct connection without cached prepared statement 1703
66947 9 direct connection with oracle specified cache 1094
66965 9 direct connection with oracle specified cache 1078
66983 9 direct connection with oracle specified cache 1078

四、参考资料

DBCP连接池配置

C3P0连接池配置

Oracle官方文档中的Statement Caching

Expert Oracle JDBC书中的Statement Caching

五、部分测试文件

dbcp.properties

DbUtil.java

Comments are closed.