You can optimize your application’s performance if you set the Oracle driver connection properties as described in this section:
BatchMechanism: The driver can use a JDBC 3.0-compliant batch mechanism or the native Oracle batch mechanism to execute batch operations. If your application does not use update count information, performance can be improved by using the native Oracle batch environment. The JDBC 3.0-compliant mechanism returns individual update counts for each statement or parameter set in the batch as required by the JDBC 3.0 specification. The native Oracle batch mechanism does not return individual update counts for each statement or parameter set in the batch. For this reason, when the native Oracle batch mechanism is used, the driver returns a value of SUCCESS_NO_INFO (-2) in the returned update count array.
CatalogOptions: It is expensive to retrieve information about synonyms, remarks, and Oracle collection data types. If your application does not require this information, the driver can improve the performance of queries that call DatabaseMetaData methods. Default driver behavior includes synonyms in the result set of calls to the following DatabaseMetaData methods: getColumns(), getExportedKeys(), getFunctionColumns(), getFunctions(), getImportedKeys(), getIndexInfo(), getPrimaryKeys(), getProcedureColumns(), and getProcedures(). In addition, the driver can include Remarks information in the result sets of calls to the following DatabaseMetaData methods: getColumns() and getTables(). If your application needs to return synonyms for getColumns() calls, but not remarks, the driver can emulate getColumns() calls using the ResultSetMetaData object instead of querying database catalogs for the column information. Using emulation can improve performance because the SQL statement formulated by the emulation is less complex than the SQL statement formulated using getColumns().
If your application uses Oracle collection data types, you may require more accurate information from the following DatabaseMetaData methods: getColumns(), getProcedureColumns(), and getFunctionColumns(). The driver can return this information, but it negatively affects performance. By default, the driver does not return information about Oracle collection data types.
CommitBehavior: Typically, redo changes generated by update transactions are written to disk immediately when the transaction is committed, and the session waits for the disk write to complete before returning control to the application. Oracle 10g R2 or higher can let the log writer write the redo changes to disk in its own time instead of immediately and return control to the application before the disk write is complete instead of waiting. Not waiting for the disk write improves performance for applications that perform update operations and where data integrity is not critical. For example, most banking applications cannot tolerate data loss in the event that the server has a problem writing the redo changes to disk or fails during the process, but many logging applications for diagnostic purposes can.
EnableBulkLoad: For batch inserts, the driver can use native bulk load protocols instead of the batch mechanism. Bulk load bypasses the data parsing usually done by the database, providing an additional performance gain over batch operations. Set this property to true to allow existing applications with batch inserts to take advantage of bulk load without requiring changes to the code.
EnableServerResultCache: If your application connects to Oracle 11g or higher and executes the same query multiple times, you can improve performance by using the Oracle feature server-side resultset caching. When enabled, Oracle stores the result set in database memory. On subsequent executions of the same query, the result set is returned from database memory if the underlying tables have not been modified. Without result set caching, the server would process the query and formulate a new result set.
DataIntegrityLevel and DataIntegrityTypes: Checking data integrity can reduce performance on both the client and the server because of the additional overhead (mainly CPU usage) that is required to perform the check.
EncryptionMethod, EncryptionLevel, and EncryptionTypes: Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data. Using data encryption can degrade performance more than performing data integrity checks.
InsensitiveResultSetBufferSize: To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.
LOBPrefetchSize: You can improve performance when fetching LOBs by enabling the LOBPrefetchSize property (set to a value equal to or greater than 0). With LOBPrefetchSize property enabled, the driver can return LOB meta-data and the beginning of LOB data along with the LOB locator during a fetch operation, thereby reducing the number of round trips and improving performance. For significant gains, specify a value that is large enough to entirely prefetch LOB values. This allows data to be available without having to go through LOB protocol, which can be expensive.
MaxPooledStatements: To improve performance, the driver's own internal prepared statement pooling should be enabled when the driver does not run from within an application server or from within another application that does not provide its own prepared statement pooling. When the driver's internal prepared statement pooling is enabled, the driver caches a certain number of prepared statements created by an application. For example, if the MaxPooledStatements property is set to 20, the driver caches the last 20 prepared statements created by the application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements are cached.
Refer to "Designing JDBC Applications for Performance Optimization" for more information about using prepared statement pooling to optimize performance.
RandomGenerator: By default, RandomGenerator is set to secureRandom. While secureRandom offers more secure seeding of random numbers, operations generally require additional processing. Therefore, if your environment does not require more secure seeding, you should consider setting RandomGenerator to random to increase response times for your applications.
ResultSetMetaDataOptions: The driver’s performance may be adversely affected if you set this option to 1. If set to 1 and the ResultSetMetaData.getTableName method is called, the driver performs emulations which take additional processing.
SDUSize: Set this property based on the size of result sets returned by your application. If your application returns large result sets, set this property to the maximum SDU size configured on the database server. This reduces the total number of round trips required to return data to the client, thus improving performance. If your application returns small result sets, set this property to a size smaller than the maximum to avoid burdening your network with unnecessarily large packets.
ServerType: When using a dedicated server connection, a server process on UNIX (a thread on Windows) is created to serve only your application connection. When you disconnect, the process goes away. The socket connection is made directly between your application and this dedicated server process. This can provide considerable performance improvements, but will use significantly more resources on UNIX servers. Because this is a thread on Oracle servers running on Windows platforms, the additional resource usage on the server is significantly less. The ServerType property should be set to dedicated when you have a batch environment with lower numbers of connections, your Oracle server has excess processing capacity and memory available when at maximum load, or if you have a performance-sensitive application that would be degraded by sharing Oracle resources with other applications.
StringParamsMustMatchCharColumns: Using ORA_VARCHAR bindings for string parameters in a Where clause can improve performance, but may cause matching problems for CHAR columns. If your application does not match string parameters against CHAR columns, set this property to false for the driver to use ORA_VARCHAR bindings.
SupportBinaryXML: The SupportBinaryXML connection property applies only to environments running servers with Oracle 12c and higher. When SupportBinaryXML=true, executing a Select that includes XMLType columns can degrade performance because the driver must pull all in-line data to execute the query. Queries involving XMLType with binary storage and XMLType with CLOB storage are equally affected in this scenario.
