Recently we change our connection pool to migrate for Oracle UCP. Before the migration, we used the pool embeded with the oracle jdbc driver (ojdbc6.jar).
Our problem is the elapsed time during the phase of the bind variables. With UCP, the time to bind a variable is greater than the old pool because it use introspection.
In a normal case (select or update), the time to bind the variables is very small compare to the time of the execution of the sql query. But, when we used a PreparedStatement for a batch execution, we do a lot of binding variables and sometimes we run the query by calling pst.excuteBatch().
As an example, this is a small program to illustrate the elasped time with the two pools.
PreparedStatement ppst = connection.prepareStatement(INSERT_SQL);
...
private long setParam(PreparedStatement prepStmt) throws SQLException {
long d = 0;
for (long i = 1; i <= 750 000; i++) {
int index = 1;
prepStmt.setString(index++, "1470");
prepStmt.setTimestamp(index++, new Timestamp(System.currentTimeMillis()));
prepStmt.setInt(index++, 1);
prepStmt.setObject(index++, String.valueOf(i));
prepStmt.addBatch();
}
prepStmt.clearBatch();
return d;
}
With the old pool embeded with the ojdbc6 driver, elapsed time is : 7.653 sec.
With the UCP pool, elapsed time is : 10.92 sec.
In this example we have 750 000 iterations with 4 bind variables. In our production batch, we have 50 000 000 iterations. So the elapsed time to bind variables is long and our batch time has grow up.
Technical Informations :
Old pool : ojdbc6.jar (11.2.0.3.0)
New Pool : ojdbc6.jar (11.2.0.3.0) + ucp.jar (11.2.0.4.0)
We have profiled the binding variable phase :
- With the new pool UCP, each variable is binded with java relection api which is slower.
- With the old pool, each variable is binded directly with the corresponding method of the variable type.
How can we improve the performance of the binding variable for the UCP pool ? Do you know a way to disable the usage of java reflection api ?
