Using OCI Statement Caching

In This Topic

In order to improve performance of SQL statement execution, dotConnect for Oracle supports OCI statements caching. Statement caching eliminates the need for reparsing SQL and PL/SQL statements when executing the same statement that was already executed by reusing the parsing information from the cursor, cached from the previous execution.

In order to achieve the best performance gain, Oracle recommends to use parameters instead of literals, since statement cache information can be reused for other statements that differ from the cached one by parameter values only. It is also recommended to cache only the statements that are executed multiple times. You can disable caching for a specific OracleCommand object by setting its Cached property to false.

You can manage statement cache using Statement Cache Size and Statement Cache Purge connection string parameters.

The Statement Cache Size parameter specifies the maximal number of statements that can be cached. By default it is set to 0, which means that the statement cache is disabled for the connection. Since the cached statement is in fact a cursor for this statement, opened in an Oracle database, this parameter should not be more than MAX_OPEN_CURSORS parameter in the Oracle database.

The Statement Cache Purge parameter determines whether to clear the OCI statement cache (close cache cursors) when closing the connection or putting it to the pool. By default this parameter is set to false, which means that cursors are left open when placing the connection to the pool thus providing best performance when using both connection pooling together with statement caching.

Additionally OracleConnection class has the PurgeStatementCache method, allowing you to clear OCI statement cache at any time.

Note that there is a separate cache for each connection. If you execute a statement, that was executed previously, via another connection, it still needs to be parsed unless the new connection was taken from the pool and is actually the same connection.

See Also