Pass parameters in Oracle JDBC URL to improve performance

 In the latest Oracle JDBC driver version 19.3 and above, it is now possible to include some connection properties as part of the JDBC URL syntax. This is a great advantage as it enables us to connect to any application where JDBC URL is configurable, and pass properties parameters to improve performance, pass security parameters, and pass Transparent Application Continuity parameters.

One of the most common changes we make to the JDBC default connection is the Fetchsize, Buffer size, or ArraySize to get the query result set. With 19c, we can now pass these properties in the JDBC URL. The default fetch size is 10, which is tuned for OLTP transactions. However, for data warehouse applications and ETLs, retrieving large amounts of data across the network using the default size can be slow. To change the default fetch size, we can set the connection property "defaultRowPrefetch" in the JDBC URL.

For example, the following URL syntax with properties can be used to set the fetch size to 1000: jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1521)(host=example1.com)) (connect_data=(service_name=example2.com)))?oracle.jdbc.defaultRowPrefetch=1000

In Oracle19c, easy connect syntax also supports adding properties: jdbc:oracle:thin:@hostname:1521/pdb1?oracle.jdbc.defaultRowPrefetch=1000&oracle.jdbc.defaultExecuteBatch=1000

If you have more than one property to set, the syntax is to start with a "?" and any additional parameters with a "&" sign. For more detailed information, check out this document.

This is very useful when we migrate our databases to the cloud. The network latency could impact performance unless we change these settings. However, in many cases, we cannot change the code or recompile the application. To tune the application for network latency without changing the code is a big advantage. Most often, the JDBC URL is the only configurable parameter. Hence this is a great tuning tip.

Prior to 12.2, the only way to set this property used to be from within the Java application. Within a Java program, you can use setFetchSize as a connection method or at a statement level. From within the Java application, the usual ways to set the row fetch size are:

• Via java.sql.Connection vendor implementation class custom method (e.g., OracleConnection.setDefaultRowPrefetch)
• Via java.sql.Statement.setFetchSize(int): gives a hint to the driver as to the row fetch size for all ResultSets obtained from this Statement. This method is inherited by PreparedStatement and CallableStatement. Most JDBC drivers support it.
• Via java.sql.ResultSet.setFetchSize(int): gives a hint to the driver as to the row fetch size for all this ResultSet.

In conclusion, this feature is going to make the JDBC URL very flexible to connect to Oracle databases. It can now include tuning parameters, TNS_ADMIN, wallet information, and many other functionalities like HA and Transparent Application Continuity.