Tuning for Performance

The connection options 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.

Application Using Threads

Determines whether the driver works with applications using multiple ODBC threads.

The driver coordinates concurrent database operations (operations from different threads) by acquiring locks. Although locking prevents errors in the driver, it also decreases performance. If your application does not make ODBC calls from different threads, the driver has no reason to coordinate operations.

Recommended Settings:

If your application is multi-threaded, enable this option.

If your application is not multi-threaded, disable this option for improved performance.

Array Size

Determines the number of bytes the driver retrieves from a server for a fetch.

To improve throughput, increase the value of Array Size. By increasing the value of Array Size, you increase the number of bytes the driver will retrieve from the server for a fetch. In turn, increasing the number of bytes that the driver can retrieve reduces the number, and expense, of network round trips. Note that improved throughput does come at the expense of increased demands on memory and slower response time. Furthermore, if the fetch size exceeds the available buffer memory of the server, an out of memory error is returned when attempting to execute a fetch. If you receive this error, decrease the value specified until fetches are successfully executed.

Recommended Settings:

Tune this setting to reflect the typical fetch size of your application. Smaller fetch sizes can improve the initial response time of the query. Larger fetch sizes improve overall fetch times at the cost of additional memory.

Cached Cursor Limit

Specifies the number of Oracle Cursor Identifiers that the driver stores in cache. A Cursor Identifier is needed for each concurrent open Select statement. When a Select statement is closed, the driver stores the identifier in its cache, up to the limit specified, rather than closing the Cursor Identifier. When a new Cursor Identifier is needed, the driver takes one from its cache, if one is available. Cached Cursor Identifiers are closed when the connection is closed.

To improve performance when your application executes concurrent Select statements, Cursor Identifiers can be cached. In this case, the Cursor Identifier is retrieved from a cache rather than being created for each connection. When an Identifier is needed, the driver takes one from its cache, if one is available, rather than creating a new one. Cached Cursor Identifiers are closed when the connection is closed. To cache Cursor Identifiers, the Cached Cursor Limit option must be set to the appropriate number of concurrent open Select statements.

Recommended Settings:

If the number of concurrent Select statements your application typically executes is:

Cached Description Limit

Specifies the number of descriptions that the driver saves for Select statements. These descriptions include the number of columns, data type, length, and scale for each column.

The driver can cache descriptions of Select statements and improve the performance of your ODBC application; therefore, if your application issues a fixed set of SQL queries throughout the life of the application, the description of the query should be cached. If a description is not cached, the description must be retrieved from the server, which reduces performance. The descriptions include the number of columns and the data type, length, and scale for each column. The matching is done by an exact-text match through the From clause. If the statement contains a Union or a subquery, the driver cannot cache the description.

Recommended Settings:

If your application issues a fixed set of SQL queries throughout the life of the application, specify a value that represents the number of descriptions that the driver saves for Select statements for improved performance.

If your application does not issue Select statements that returns a few rows repeatedly, set this option to 0.

Catalog Functions Include Synonyms

Determines whether synonyms are included in calls to SQLProcedures, SQLStatistics, and SQLProcedureColumns.

Standard ODBC behavior is to include synonyms in the result set of calls to the following catalog functions: SQLProcedures, SQLStatistics, and SQLProcedureColumns. Retrieving this synonym information degrades performance.

Recommended Settings:

If your application does not need to return synonyms when using these catalog functions, the driver can improve performance if the Catalog Functions Include Synonyms option is disabled.

Catalog Options

Determines whether SQL_NULL_DATA is returned for the result columns REMARKS and COLUMN_DEF.

Retrieving actual values for the result column REMARKS reduces the performance of your catalog queries.

Recommended Settings:

If your application does not need to access the comments/remarks for database tables, the Catalog Options option should be disabled because retrieving comments/remarks degrades performance.

Client Information Options

The client information feature automatically adjusts server resources, such as CPU and memory, based on the service class associated with a workload. Therefore, an application’s performance is tied to the workload to which it is assigned and, ultimately, to the service class associated with that workload. The Oracle Wire Protocol driver allows your application to set client information in the Oracle database that can be used by the client information feature to classify work.

Recommended Settings:

If you know that your database environment can use client information, coordinate with your database administrator to determine how setting the following Client Information options affects performance:

Connection Pooling

Specifies whether to use the driver’s connection pooling.

Connection pooling allows you to reuse connections rather than creating a new one every time the driver needs to establish a connection to the underlying database. Using connection pooling can improve performance by avoiding the penalty incurred when establishing a new connection, especially for high-throughput applications.

You can further affect the performance by tuning the following connection options that are related to connection pooling: Connection Reset, Max Pool Size, Min Pool Size, and LoadBalance Timeout.

Recommended Settings:

Enable this option to improve performance.

Connection Reset

Determines whether the state of connections that are removed from the connection pool for reuse by the application is reset to the initial configuration of the connection.

Resetting a re-used connection to the initial configuration settings impacts performance negatively because the connection must issue additional commands to the server.

Recommended Settings:

Disable this option for improved performance.

Data Integrity Level

Specifies a preference for the data integrity to be used on data sent between the driver and the database server.

Checking data integrity may adversely reduce performance because of the additional overhead (mainly CPU usage) that is required to perform the check.

Recommended Settings:

If your application does not require data integrity checking, specify a value of 0 - Rejected for improved performance.

Default Buffer Size for Long/LOB Columns (in Kb)

The maximum length of data (in KB) the driver can fetch from long columns in a single round trip and the maximum length of data that the driver can send using the SQL_DATA_AT_EXEC parameter.

To improve performance when your application fetches images, pictures, or long text or binary data, a buffer size can be set to accommodate the maximum size of the data. The buffer size should only be large enough to accommodate the maximum amount of data retrieved; otherwise, performance is reduced by transferring large amounts of data into an oversized buffer. If your application retrieves more than 1 MB of data, the buffer size should be increased accordingly.

Recommended Settings:

Set DefaultLongDataBuffLen to a size no larger than the maximum size of data retrieved.

Describe At Prepare

Determines whether the driver describes the SQL statement at prepare time.

When enabled, the driver describes the SQL statement at prepare time, which requires extra network traffic.

Recommended Settings:

If your application does not require result set information at prepare time (for instance, you request information about the result set using SQLColAttribute(s), SQLDescribeCol, SQLNumResultCols, and so forth, before calling SQLExecute on a prepared statement), you can increase performance by disabling this option.

Enable Bulk Load

Specifies whether the driver is permitted to use the bulk load protocol when executing a parameter batch for an INSERT statement.

For batch inserts, the driver might be able to use the native bulk load functionality instead of the database’s parameter batch mechanism. Using the bulk load functionality typically improves performance, but at the expense of potentially bypassing data integrity constraints.

Recommended Settings:

If your application does not require a high level of data integrity, enable this option for improved performance.

Enable Server Result Cache

If your application connects to Oracle 11g and executes the same query multiple times, you can improve performance by using the Oracle feature server-side result set 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 executes the same query multiple times, enable this option to improve performance.

Encryption Method

Determines the method the driver uses to encrypt data sent between the driver and the database server.

Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) that is required to encrypt and decrypt data.

Recommended Settings:

If data encryption is not required, set to 0 - No Encryption for improved performance.

Failover Mode

Specifies the type of failover method the driver uses.

Although high availability that replays queries after a failure provides increased levels of protection, it can adversely affect performance because of increased overhead.

Recommended Settings:

If your application does not require the ability to recover or restart transactions, set this option to 0 - Connection for improved performance.

LoadBalance Timeout

Specifies the number of seconds to keep inactive connections open in a connection pool.

By configuring LoadBalance Timeout to keep inactive connections open, you improve the likelihood that the driver can reuse an existing connection instead of establishing new one when a connection is needed. This improves performance by avoiding the overhead required to establish a new connection.

The Min Pool Size option can cause some connections to ignore this value.

Recommended Settings:

Set to 0 for improved performance. By specifying a value of 0 (the default), inactive connections are kept open.

LOB Prefetch Size

Specifies the size of prefetch data the server returns for BLOBs and CLOBs.

With LOB prefetching enabled (set to value equal to or greater than 0), the driver can return LOB meta-data and the beginning of LOB data along with the LOB locator during a fetch operation, therefore 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.

Recommended Settings:

If your application regularly processes LOB data, enable this option for better performance. Significant gains can be achieved by specifying a value reflective of the size of your LOB data.

If your application rarely processes LOB data, disable (-1) this option.

Lock Timeout

Specifies the amount of time, in seconds, the Oracle server waits for a lock to be released before generating an error when processing a Select...For Update statement .

Sometimes users attempt to select data that is locked by another user. Oracle provides three options when accessing locked data with SELECT … FOR UPDATE statements:

Some applications may benefit by not waiting indefinitely and continuing execution; this keeps the application from hanging. The application, however, needs to handle lock timeouts properly with an appropriate timeout value; otherwise, processing time could be wasted handling lock timeouts, and deadlocks could go undetected.

Recommended Settings:

To improve performance, either enter a number of seconds or 0 as the value for this option.

Max Pool Size

The maximum number of connections allowed within a single connection pool. When the maximum number of connections is reached, no additional connections can be created in the connection pool.

Setting the maximum number of connections that the pool can contain too low might cause delays while waiting for a connection to become available. Setting the number too high wastes memory resources.

Recommended Settings:

Tune this setting for your environment. Specify the maximum number of connections your environment requires to have open at a time.

Min Pool Size

Specifies the minimum number of connections that are opened and placed in a connection pool, in addition to the active connection, when the pool is created. The connection pool retains this number of connections, even when some connections exceed their LoadBalance Timeout value.

A connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size, if one has been specified. The connection pool retains this number of connections, even when some connections exceed their LoadBalanceTimeout value. By increasing this number, you improve the likelihood that the driver will be able to reuse a connection; thereby, improving performance.

Recommended Settings:

Tune this setting for your environment. Specify the average number of connections your environment typically uses at a time.

Procedure Returns Results

Determines whether the driver returns result sets from stored procedures/functions.

When this option is enabled, executing stored procedures that do not return a result set incur a small performance penalty.

Recommended Settings:

If your application's stored procedures do not return results, disable this option to improve performance.

SDU Size

Specifies the size in bytes of the Session Data Unit (SDU) that the driver requests when connecting to the server.

Larger SDU sizes reduce the number of packets required to return data to the client, thus improving performance. However, excessively large packets may result in unnecessary burden on your network.

Recommended Settings:

If your application returns large result sets, set this option to the maximum SDU size configured on the database server.

If your application returns small result sets, set this option to a size smaller than the maximum.

Server Process Type

Determines whether the connection is established using a shared or dedicated server process (dedicated thread on Windows).

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 tremendous 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:

This option should be set to 2 - 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.

Use Current Schema for SQLProcedures

If your application needs to access database objects owned only by the current user, then performance can be improved. When UseCurrentSchema is enabled, the driver returns only database objects owned by the current user when executing catalog functions. Calls to catalog functions are optimized by grouping queries. Enabling this option is equivalent to passing the Logon ID used on the connection as the SchemaName argument to the catalog functions.