Doc: ParameterMetaData.getParameterType performance on Oracle 12c [SPR-16139]

[spring-projects-issues]

Adam Sojka opened SPR-16139 and commented

After upgrading from Spring 4.3 to 5.0.1 the NamedParameterJdbcTemplate.batchUpdate call results in an exception ORA-01000: maximum open cursors exceeded when using with Oracle 12c.

The batchUpdate is updating batches of 250 records.

The applications code hasn't changed in method where exception is being thrown.

@SuppressWarnings("unchecked")
    public int[] saveBatchOutputs(final String updateSql, final List<Map<String, Object>> batchValuesList) {
        Map<String, Object>[] batchValues = new Map[batchValuesList.size()];
        batchValues = batchValuesList.toArray(batchValues);
        return namedParameterJdbcTemplate.batchUpdate(updateSql, batchValues);
    }

In the method the updateSql is predefined and the batchValuesList is up to 250 elements in size.

---

Affects: 5.0.1

Issue Links:

• "Parameter metadata not available for the given statement" SQL Exception after upgrading to Spring 5.0 RC4 [SPR-15977] #20528 "Parameter metadata not available for the given statement" SQL Exception after upgrading to Spring 5.0 RC4
• Raise JDBC driver feature baseline to JDBC 4.0+ [SPR-13826] #18399 Raise JDBC driver feature baseline to JDBC 4.0+
• Protect against getParameterType cursor leak with Oracle 12c JDBC driver [SPR-14629] #19196 Protect against getParameterType cursor leak with Oracle 12c JDBC driver
• We are facing the maximum open cursor issue [SPR-17001] #21539 We are facing the maximum open cursor issue
• Spring-jdbc 5.0.5 NamedParameterJdbcTemplate.batchUpdate ORA-01000 [SPR-16814] #21354 Spring-jdbc 5.0.5 NamedParameterJdbcTemplate.batchUpdate ORA-01000
• SqlParameterSourceUtils.createBatch with Collection support [SPR-16215] #20763 SqlParameterSourceUtils.createBatch with Collection support

Referenced from: commits daee6ee

[spring-projects-issues]

Juergen Hoeller commented

There has been quite a bit of JDBC baseline upgrading in Spring Framework 5.0, so maybe this is a side effect... Any insight (or even guess) into where the different behavior might be caused?

[spring-projects-issues]

Adam Sojka commented

I have just verified that rolling back spring-jdbc to 4.3.10 resolved the issue.

[spring-projects-issues]

Adam Sojka commented

I couldn't find anything else causing the issue, rollback did the job. I rolled back spring-jdbc alone leaving other spring libraries at version 5.0.1.
I am using ojdbc7.jar driver for Oracle 12c version 12.1.0.2

[spring-projects-issues]

Juergen Hoeller commented

I have a suspicion that this is the same effect as with #19196... a workaround which we removed in 5.0 as part of our JDBC baseline upgrade.

You could try setting spring.jdbc.getParameterType.ignore=true, either as a system property or in a spring.properties file in the root of the classpath. If this is known to help, we may consider reintroducing the workaround to 5.0's streamlined variant of StatementCreatorUtils.setNull.

That said, see my comment in #19196: Recent Oracle 12c drivers should not expose that cursor leak anymore. If an Oracle driver upgrade helps, that would also be great to know!

[spring-projects-issues]

Adam Sojka commented

I have replaced the existing driver with ojdbc8.jar version 12.2.0.1 which fixed the problem. I was no longer getting maximum open cursors exception and the process completed successfully.

The only problem is the performance. The batch update operation used to take 0.5 seconds for 250 records but now it takes 6 seconds instead. After rolling back to spring-jdbc 4.3.10.RELEASE the timings where back to normal - 0.5 second (using the new Oracle driver).

Overall the new JDBC driver does address the issue of the exception but there is a performance problem now.

[spring-projects-issues]

Juergen Hoeller commented

Thanks for the feedback, that's good to know!

For the performance part of the problem, does it help to set spring.jdbc.getParameterType.ignore=true as suggested above?

[spring-projects-issues]

Adam Sojka commented

Yes, after adding spring.properties with spring.jdbc.getParameterType.ignore=true the performance issue was resolved.

In Summary to resolve the issue one need to:

1. Upgraded Oracle JDBC driver to ojdbc8.jar version 12.2.0.1
2. Create spring.properties with spring.jdbc.getParameterType.ignore=true

[spring-projects-issues]

Juergen Hoeller commented

Alright, let's turn this into a documentation task then, putting specific notes for Oracle 12c into our reference documentation.

[spring-projects-issues]

Juergen Hoeller commented

BTW, I've created #20763 inspired by your source code example (and a similar case in our reference documentation). Such manual collection adaptation shouldn't be necessary. I've added overloaded methods to SqlParameterSourceUtils, allowing for the following concise code:

public int[] saveBatchOutputs(final String updateSql, final List<Map<String, Object>> batchValuesList) {
    return namedParameterJdbcTemplate.batchUpdate(updateSql, SqlParameterSourceUtils.createBatch(batchValuesList));
}

[spring-projects-issues]

Adam Sojka commented

Juergen, Thank you very much for resolving the issue.