Large Waiting Times on Server Cleanup On Connection Close After Batch CLOB Insert with 19.3 JDBC (Doc ID 2877706.1)

Last updated on MARCH 12, 2024

Applies to:

Symptoms

Oracle Data Integrator (ODI) application using WebLogic 12.2.1.4.0 and JDBC 19.3 transfers CLOB data from one schema to another in a 12c Oracle database.
The application runs a SELECT statement on the table in source schema and then runs a batch INSERT statement on the table in target schema.
When ODI calls Commit Transaction, the thread takes a longer amount of time when closing the connection than it did using earlier versions of ODI and JDBC (this performance issue did not occur when using ODI 12.2.1.3 and JDBC 12.2.0.1).
The performance degradation appears to increase dramatically as the batch size increases.


OBSERVATIONS
=============
Testing results:
1. Using the same parameters and data in 12.2.1.4 and 12.2.1.3 with row limits set to 50,000: 12.2.1.3, the execution time is 2.3 times faster than 12.2.1.4.
2. If the row limit is increased from 50,000 to 100,000, 12.2.13 execution time is 4.3 times faster than 12.2.1.4.
3. Testing with the following Java directive results in no performance improvement.
-Doracle.jdbc.piggybackLOB=false
This setting reverts the driver to behave as it did with the 12.2.0.1 JDBC driver by disabling LOB free from being piggybacked.
Setting this parameter to true results in a server round-trip for every free() call.

Changes

 Upgrade from ODI 12.2.1.3 to 12.2.1.4.0.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.