8 Performance and Tuning

This chapter provides necessary information for programmers to manage the performance and tuning of the Oracle Database ODBC driver.

Topics:

• General ODBC Programming Tips
  
• Data Source Configuration Options
  
• DATE and TIMESTAMP Data Types
  

8.1 General ODBC Programming Tips

This section describes some general programming tips to improve the performance of an ODBC application.

• Enable connection pooling if the application frequently connects to and disconnects from a data source. Reusing pooled connections is extremely efficient compared to reestablishing a connection.

• Minimize the number of times a statement must be prepared. Where possible, use bind parameters to make a statement reusable for different parameter values. Preparing a statement once and executing it several times is much more efficient than preparing a statement for every SQLExecute.

• Do not include columns in a SELECT statement if you know the application will not retrieve them; especially LONG columns. Due to the nature of the database server protocols, the ODBC driver must fetch the entire contents of a LONG column if it is included in the SELECT statement, regardless of if the application binds the column or does a SQLGetData.

• If you are performing transactions that do not update the data source, set the SQL_ATTR_ACCESS_MODE attribute of the ODBC SQLSetConnectAttr function to SQL_MODE_READ_ONLY.

• If you are not using ODBC escape clauses, set the SQL_ATTR_NOSCAN attribute of the ODBC SQLSetConnectAttr function or the ODBC SQLSetStmtAttr function to true.

• Use the ODBC SQLFetchScroll function instead of the ODBC SQLFetch function for retrieving data from tables that have a large number of rows.

• Enable OCI statement caching when the same SQL statements are used multiple times (StatementCache=T).

• Binding NUMBER columns as FLOAT speeds up query execution (BindAsFLOAT=T).

• While fetching LONG or LONG RAW set MaxLargeData=<value> for optimum performance.

• Setting UseOCIDescribeAny=T for applications that make heavy calls to small packaged procedures with return Ref Cursor improves performance.

8.2 Data Source Configuration Options

This topic discusses performance implications of the following ODBC data source configuration options:

Topics:

• Enable Result Sets

• Enable LOBs

• Bind TIMESTAMP as DATE

• Enable Closing Cursors

• Enable Thread Safety

• Fetch Buffer Size

Enable Result Sets

This option enables the support of returning result sets (for example, RefCursor) from procedure calls. The default is enabling the returning of result sets.

The ODBC driver must query the database server to determine the set of parameters for a procedure and their data types to determine if there are any RefCursor parameters. This query incurs an additional network round trip the first time any procedure is prepared and executed.

Enable LOBs

This option enables the support of inserting and updating LOBs. By default, it is enabled.

The ODBC driver must query the database server to determine the data types of each parameter in an INSERT or UPDATE statement to determine if there are any LOB parameters. This query incurs an additional network round trip the first time any INSERT or UPDATE is prepared and executed.

Bind TIMESTAMP as DATE

Binds SQL_TIMESTAMP parameters as the appropriate Oracle data type. If this option is TRUE, SQL_TIMESTAMP binds as the Oracle DATE data type. If this option is FALSE, SQL_TIMESTAMP binds as the Oracle TIMESTAMP data type (which is the default).

Enable Closing Cursors

The SQL_CLOSE option of the ODBC function, SQLFreeStmt, is supposed to close associated cursors with a statement and discard all pending results. The application can reopen the cursor by executing the statement again without doing a SQLPrepare again. A typical scenario for this is an application that is idle for a while but reuses the same SQL statement. While the application is idle, it might free up associated server resources.

The Oracle Call Interface (OCI), on which the Oracle Database ODBC driver is layered, does not support the functionality of closing cursors. So, by default, the SQL_CLOSE option has no effect in the Oracle Database ODBC driver. The cursor and associated resources remain open on the database server.

Enabling this option causes the associated cursor to be closed on the database server. However, this results in the parse context of the SQL statement being lost. The ODBC application can execute the statement again without calling SQLPrepare. However, internally the ODBC driver must prepare and execute the statement all over. Enabling this option severely impacts performance of applications that prepare a statement once and execute it repeatedly.

Enable this option only if freeing the resources on the server is absolutely necessary.

Enable Thread Safety

If an application is single-threaded, this option can be disabled. By default, the ODBC driver ensures that access to all internal structures (environment, connection, statement) are thread-safe. Single-threaded applications can eliminate some of the thread safety overhead by disabling this option. Disabling this option typically shows a minor performance improvement.

Fetch Buffer Size

Set the Fetch Buffer Size in the Oracle Options tab of the Oracle ODBC Driver Configuration Dialog Box to a value specified in bytes. This value determines how many rows of data at a time the ODBC driver prefetches from an Oracle Database to the client's cache, regardless of the number of rows the application program requests in a single query, thus improving performance.

Applications that typically fetch fewer than 20 rows of data at a time improve their response time, particularly over slow network connections or on heavily loaded servers. Setting this too high can worsen response time or consume large amounts of memory. The default is 64,000 bytes. Choose a value that works best for your application.

Note:

When LONG and LOB data types are present, the number of rows prefetched by the ODBC driver is not determined by the Fetch Buffer Size. The inclusion of the LONG and LOB data types minimizes the performance improvement and could result in excessive memory use. The ODBC driver disregards the Fetch Buffer Size and prefetches a set number of rows in the presence of the LONG and LOB data types.

8.3 DATE and TIMESTAMP Data Types

If a DATE column in the database is used in a WHERE clause and the column has an index, there can be an impact on performance. For example:

SELECT * FROM EMP WHERE HIREDATE = ?

In this example, an index on the HIREDATE column could be used to make the query execute quickly. But, because HIREDATE is actually a DATE value and the ODBC driver supplies the parameter value as TIMESTAMP, the Oracle server's query optimizer must apply a conversion function. To prevent incorrect results (as might happen if the parameter value had non-zero fractional seconds), the optimizer applies the conversion to the HIREDATE column resulting in the following statement:

SELECT * FROM EMP WHERE TO_TIMESTAMP(HIREDATE) = ?

Unfortunately, this has the effect of disabling the use of the index on the HIREDATE column and, instead, the server performs a sequential scan of the table. If the table has many rows, this can take a long time. As a workaround for this situation, the ODBC driver has the connection option to Bind TIMESTAMP as DATE. When this option is enabled, the ODBC driver binds SQL_TIMESTAMP parameters as the Oracle DATE data type instead of the Oracle TIMESTAMP data type. This allows the query optimizer to use any index on the DATE columns.

Note:

This option is intended for use only with Microsoft Access or other similar programs that bind DATE columns as TIMESTAMP columns. Do not use this option when there are actual TIMESTAMP columns present or when data loss may occur. Microsoft Access executes such queries using whatever columns are selected as the primary key.

See Also:

Implementation of Data Types (Advanced)