8 Performance and Tuning
This chapter provides necessary information for programmers to manage the performance and tuning of the Oracle Database ODBC driver.
Topics:
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; especiallyLONG
columns. Due to the nature of the database server protocols, the ODBC driver must fetch the entire contents of aLONG
column if it is included in theSELECT
statement, regardless of if the application binds the column or does aSQLGetData
. -
If you are performing transactions that do not update the data source, set the
SQL_ATTR_ACCESS_MODE
attribute of the ODBCSQLSetConnectAttr
function toSQL_MODE_READ_ONLY
. -
If you are not using ODBC escape clauses, set the
SQL_ATTR_NOSCAN
attribute of the ODBCSQLSetConnectAttr
function or the ODBCSQLSetStmtAttr
function to true. -
Use the ODBC
SQLFetchScroll
function instead of the ODBCSQLFetch
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 asFLOAT
speeds up query execution (BindAsFLOAT=T
). -
While fetching
LONG
orLONG RAW
setMaxLargeData=<value>
for optimum performance. -
Setting
UseOCIDescribeAny=T
for applications that make heavy calls to small packaged procedures with returnRef Cursor
improves performance.
Parent topic: Performance and Tuning
8.2 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.
Parent topic: Performance and Tuning
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:
Parent topic: Performance and Tuning