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

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.

Data Source Configuration Options

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

Topics:

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.

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.

Database Resident Connection Pooling Support

Connection pooling enables the ODBC driver to reuse existing database connections on the client-side, improving application performance by reducing the time required to establish a new connection. ODBC Driver Manager’s default connection pooling maintains connections based on attributes such as the username, DSN, and driver name.

With the support for Oracle Database Resident Connection Pooling (DRCP), which provides similar pooling functionality on the database server side, Oracle ODBC applications can now acquire and reuse connections from the connection pool maintained by the Oracle AI Database server. By allowing an ODBC application to set the DRCP connection class (OCI_ATTR_CONNECTION_CLASS), the Oracle ODBC driver can request connections from DRCP and return them for future reuse.

Prerequisites

Before using DRCP in ODBC applications:

DRCP must be configured on the database server.
The application must set SQL_ATTR_ODBC_VERSION to SQL_OV_ODBC3_80 using SQLSetEnvAttr() to enable connection reuse from the Driver Manager connection pool.
ODBC Driver Manager connection pooling must be enabled to allow the application to reuse connections from the DRCP pool.
(Optional) The DRCP Connection class can be set in the ODBC driver or in the TNS connection string.

Configure DRCP on the Database Server

DRCP creates a connection pool in the Oracle AI Database server that can be shared by multiple client applications, reducing database resource usage and improving scalability. A database administrator must start and configure the pool using the DBMS_CONNECTION_POOL package. DRCP can be configured at the CDB or PDB level according to the deployment requirements. For details, see Oracle’s Enabling DRCP on the Server Side and Performance and Scalability documentation.

Setting Up Connection Class in Oracle ODBC Driver

The DRCP connection class can be specified through the .odbc.ini configuration file, an ODBC driver connect string, or the Oracle ODBC DSN configurator on Windows. When a connection class is provided, the Oracle ODBC driver sends connection requests from SQLConnect or SQLDriverConnect to DRCP running in the Oracle AI Database and retrieves a connection from its pool.

Using .odbc.ini

Set the DRCP Connection Class attribute under the required DSN in the .odbc.ini file. The attribute has no default value.

[MyDSN]
 AggregateSQLType = FLOAT
 Application Attributes = T

 Driver = /ade/b/1337024197/oracle/odbc/lib/libsqora.so.23.1
 DRCP Connection Class = myODBCConnectClass
 DSN = MyDSN

When MyDSN is passed to SQLConnect or SQLDriverConnect, the Oracle ODBC driver uses myODBCConnectClass as the DRCP connection class and sends the connection request to DRCP.

Using an ODBC Driver Connect String

The connection class can be set in an ODBC driver connect string using the DCC attribute. Set DCC to the DRCP connection class name when calling SQLDriverConnect.

Driver=myOrclDrv;UID=<username>;PWD=<password>;DBQ=;DCC=myODBCConnectClass

With this setting, the SQLDriverConnect request is sent to DRCP so that the driver can retrieve a connection from the database server’s connection pool.

Using the Oracle ODBC DSN Configurator on Windows

On Windows, set the connection class in the DRCP Connection Class field on the Oracle tab of the Oracle ODBC DSN configuration wizard. The field is empty by default and is optional.

Figure 1: Oracle ODBC Driver Configuration - DRCP Connection Class

Oracle ODBC Driver Configuration - DRCP Connection Class

Enabling ODBC Driver Manager Connection Pooling

The following sections explain how to enable connection pooling for the ODBC driver on UNIX and Windows platforms.

Enabling ODBC DM Connection Pooling on Linux and UNIX Platforms

To enable connection pooling in unixODBC DM, set attribute Pooling to Yes in the odbcinst.ini file under section [ODBC]. Here is a sample of odbcinst.ini:

[ODBC]
Pooling = Yes
PoolMaxSize = 20
PoolWaitTimeout = 10

[MyOrclODBCDriverName]
CPTimeout = 120
Driver = /mydisk/odbc/lib/libsqora.so.23.1
Setup = /mydisk/odbc/lib/libsqora.so.23.1

Finding the Location of odbcinst.ini and odbc.ini files

To locate odbcinst.ini and .odbc.ini files, you can use ODBC installer/deinstaller utility odbcinst. You can use ODBC installer’s -j option to find the location of odbcinst.ini and .odbcini files.

$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/userid/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Enabling ODBC DM Connection Pooling on Windows

To enable connection pooling on a Windows platform:

Open the ODBC Data Source Administrator (odbcad32) utility.

Figure 2: ODBC Data Source Administrator (odbcad32)
Select the Connection Pooling tab.

Figure 3: ODBC Data Source Administrator - Connection Pooling Tab
From the drivers list, double-click the Oracle ODBC driver on which the connection pooling needs to be enabled. A Set Connection Pooling Attributes dialog is displayed.

Figure 4: ODBC Data Source Administrator - Set Connection Pooling Attributes
Select the Pool Connections to this driver option and set the connection timeout value as per requirement.

Figure 5: ODBC Data Source Administrator - Set Pool Connection and Timeout
Click OK to confirm the changes.

Figure 6: ODBC Data Source Administrator - Save Changes