Improve Oracle performance and stability

[hmiguim]

Problem:

We create a database in the Oracle DBMS with a single table with 5 columns one of them was a CLOB. We added 1 million rows to this table and export the database to SIARD. After fetching around 800.000 rows the JDBC driver started to utilize all the cores in the CPU and eventually the execution stopped with a deadlock.

Solution:

The Oracle JDBC driver have a property that can be set at the connection creation level or statement level called oracle.jdbc.defaultLobPrefetchSize. This property allows to configure how much of the LOB data is fetched the first time is requested. By default the value is 4K for BLOB and 4K to CLOB.

A performance benchmark can be found here.

The number of roundtrips that are made between the client and the server can decrease significantly if overriding this property value. By decreasing the number of roundtrips we also increase the performance.

A java property option was added dbptk.jdbc.oracle.lobPrefetchSize to allow the user to fine tune the DBPTK Developer to increase overall performance. Combine this property with dbptk.jdbc.fetchsize.default and tune the DBPTK Developer accordingly your system specifications and database specifications.

[hmiguim]

Close at fd783b4