The connection properties described in this section directly affect the performance of your driver. To tune for performance, configure your driver according to the recommended settings and your environment.
BatchMechanism
Purpose:
Determines the mechanism that is used to execute batch operations.
Performance Impact:
The driver can use a JDBC 3.0-compliant batch mechanism or the native Oracle batch mechanism to execute batch operations. 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.
Recommended Settings:
If your application does not use update count information, performance can be improved by using the native Oracle batch environment (BatchMechanism=nativeBatch).
CatalogOptions
Purpose:
Determines which type of metadata information is included in result sets when an application calls DatabaseMetaData methods.
Performance Impact:
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.
Recommended Settings:
Set a value for this property based on the type of metadata information your application requires. To know the complete behavior of each of the valid values, see "CatalogOptions" in Progress DataDirect for JDBC for Oracle Driver User's Guide.
CommitBehavior
Purpose:
Determines the redo log behavior. Typically, redo changes that are generated by update transactions are written to disk immediately when a transaction is committed, and the session waits for the disk write to complete before returning control to the application.
Performance Impact:
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.
Recommended Settings:
If your application processes multiple update transactions simultaneously and data integrity is not critical, set to noWaitBatch for improved performance.
DataIntegrityLevel
Purpose:
Determines the level of Oracle Advanced Security data integrity used for data sent between the driver and database server. The connection fails if the database server does not have a compatible integrity algorithm.
Performance Impact:
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.
Recommended Settings:
If your application does not require a high level of data integrity, set to rejected for better performance.
EnableBulkLoad
Purpose:
Specifies whether the driver uses the native bulk load protocols for batch inserts..
Performance Impact:
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.
Recommended Settings:
If your application does not require a high level of data integrity, enable this option (EnableBulkLoad=true) for improved performance.
EnableServerResultCache
Purpose:
Determines whether the driver enables Oracle’s server-side resultset caching feature, which stores the result set in database memory so that it can be reused.
Performance Impact:
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.
Recommended Settings:
If your application requires the same query to be executed multiple times, enable server-side resultset caching (EnableServerResultCache=true) for improved performance.
EncryptionMethod
Purpose:
Determines whether data is encrypted and decrypted when transmitted over the network between the driver and database server.
Performance Impact:
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.
Recommended Settings:
If your application does not require data encryption, set to noEncryption for better performance.
InsensitiveResultSetBufferSize
Purpose:
Determines the amount of memory that is used by the driver to cache insensitive result set data.
Performance Impact:
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.
Recommended Settings:
Specify a value in KB that is a power of 2 for improved performance. This value should not exceed the amount of available memory. The maximum value for this property is 2 GB.
LOBPrefetchSize
Purpose:
Specifies the size of prefetch data the driver returns for BLOBs and CLOBs. LOBPrefetchSize is supported for Oracle database versions 12.1.0.1 and higher.
Performance Impact:
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.
Recommended Settings:
For better performance, 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
Purpose:
Specifies the maximum number of prepared statements to be pooled for each connection and enables the driver’s internal prepared statement pooling when set to an integer greater than zero (0).
Performance Impact:
The driver’s internal prepared statement pooling provides performance benefits when the driver is not running from within an application server or another application that provides its own statement pooling.
Recommended Settings:
For better performance, specify a value that is greater than the number of prepared statements used by the application. Note that this performance benefit comes at the expense of greater memory consumption.
RandomGenerator
Purpose:
Specifies the type of random number generator the database uses for secure seeding.
Performance Impact:
By default, RandomGenerator is set to secureRandom. While secureRandom offers more secure seeding of random numbers, it generally increases the processing time of operations, which affects the performance adversely.
Recommended Settings:
If your environment does not require more secure seeding, set RandomGenerator to random to improve response times for your application.
ResultSetMetaDataOptions
Purpose:
Determines whether the driver returns table name information in the ResultSet metadata for Select statements.
Performance Impact:
Returning table name information using the ResultSetMetaData.getTableName() method requires the driver to perform emulations to determine the correct table name for each column in the result set. This additional processing can adversely affect performance.
If set to 1 and the ResultSetMetaData.getTableName() method is called, the driver performs additional processing to determine the correct table name for each column in the result set. The driver returns schema name and catalog name information when the ResultSetMetaData.getSchemaName() and ResultSetMetaData.getCatalogName() methods are called if the driver can determine that information.
Recommended Settings:
If your application does not require returning table name information in the ResultSet metadata , you can improve performance by setting this option to 0.
SDUSize
Purpose:
Specifies the size in bytes of the Session Data Unit (SDU) that the driver requests when connecting to the server. The SDU is equivalent to the maximum size of database protocol packets sent across the network. This property serves only as a suggestion to the database server. The actual SDU is negotiated with the database server.
Performance Impact:
To optimize performance, set this property based on the size of result sets returned by your application.
Recommended Settings:
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
Purpose:
Specifies whether the connection is established using a shared or dedicated server process (UNIX) or thread (Windows).
Performance Impact:
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.
Recommended Settings:
If you have a batch environment or a performance-sensitive application that would be degraded by sharing Oracle resources with other applications, set this property to dedicated. With lower numbers of connections, your Oracle server has excess processing capacity and memory available when at maximum load.
StringParamsMustMatchCharColumns
Purpose:
Determines whether the driver uses ORA_CHAR or ORA_VARCHAR bindings for string parameters in a Where clause.
Performance Impact:
Using ORA_VARCHAR bindings can improve performance, but may cause matching problems for CHAR columns.
Recommended Settings:
If your application does not match string parameters against CHAR columns, set this property to false for the driver to use ORA_VARCHAR bindings.
