About Statement Caching

Statement caching improves performance by caching executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly.

Statement caching can have two benefits:

• prevent the overhead of repeated cursor creation

• prevent repeated statement parsing and creation

Basics of Statement Caching

A statement cache is used to cache statements associated with a particular physical connection. Enabling statement caching automatically enables implicit and explicit statement caching. Both of these types of statement caching share the same cache.

For a simple connection, the cache is associated with an OracleConnection object. For a pooled connection, the cache is associated with an OraclePooledConnection object. The OracleConnection and OraclePooledConnection objects include methods to enable statement caching. When statement caching is enabled, a statement object is cached when you call the "close" methods.

Because each physical connection has its own cache, multiple caches can exist if you enable statement caching for multiple physical connections. When statement caching is enabled on a pooled connection, all the logical connections will use the same cache. If you try to enable statement caching on a logical connection of a pooled connection, this will throw an exception.

As mentioned above, there are two forms of statement caching:

• implicit

• explicit

Implicit Statement Caching

When you enable statement caching, implicit statement caching automatically caches the prepared or callable statement when you call the close() method of this statement object. The prepared and callable statements are cached and retrieved using standard connection object and statement object methods.

Plain statements are not implicitly cached because implicit statement caching uses a SQL string as a key, and plain statements are created without a SQL string. Therefore, implicit statement caching applies only to the OraclePreparedStatement and OracleCallableStatement objects, which are created with a SQL string. When one of these statements is created, the JDBC driver automatically searches the cache for a matching statement. The match criteria are the following:

• The SQL string in the statement must be identical (case-sensitive) to one in the cache.

• The statement type must be the same (prepared or callable).

• The scrollable type of result sets produced by the statement must be the same (forward-only or scrollable). You can determine the scrollability when you create the statement. (See "Specifying Result Set Scrollability and Updatability" for complete details.)

Figure 14-1 illustrates the implicit statement caching concept.

Figure 14-1 Implicit Statement Caching Process

If a match is found during the cache search, the cached statement is returned. If a match is not found, then a new statement is created and returned. The new statement, along with its cursor and state, are cached when you call the close() method of the statement object.

When a cached OraclePreparedStatement or OracleCallableStatement object is retrieved, the state and data information are automatically re-initialized and reset to default values, while metadata is saved. The Least Recently Used (LRU) scheme performs the statement cache operation.

You can prevent a particular statement from being implicitly cached by calling a special method. This method is discussed in "Disabling Implicit Statement Caching for a Particular Statement".

Explicit Statement Caching

When you enable statement caching, explicit statement caching, which is based on a user-defined key, enables you to cache and retrieve selected prepared, callable, and plain statements. The key is an arbitrary Java string that you provide.

To explicitly cache a statement and to retrieve an explicitly cached statement, use the Oracle "WithKey" methods. Employing these methods, specify the key as an input parameter. As the name implies, explicit statement caching is performed on each statement object you want to cache.

If the JDBC driver finds the matching statement with the specified key, then the statement is returned. If the JDBC driver cannot find a matching statement in the cache, it will return a null value.

Comparing Implicit and Explicit Statement Caching

Because explicit statement caching retains the statement data and state, in addition to the metadata, it has a performance edge over implicit statement caching, which retains only the metadata. However, because explicit statement caching saves all three types of information for re-use, you must be cautious when using this type of caching--you may not be aware of what was retained for data and state in the previous statement.

Implicit statement caching uses standard methods to allocate and retrieve statement objects. Explicit statement caching uses standard methods to allocate statements and specialized Oracle "WithKey" methods to cache and retrieve statement objects.

Implicit statement caching uses the SQL string of a prepared or callable statement as the key, requiring no action on your part. Explicit statement caching requires you to provide a Java string, which it uses as the key.

During implicit statement caching, if the JDBC driver cannot find a statement in cache, it will automatically create one. During explicit statement caching, if the JDBC driver cannot find a matching statement in cache, it will return a null value.

Table 14-1 compares the different methods employed in implicit and explicit statement caching.

Table 14-1 Comparing Methods Used in Statement Caching

	Allocate	Cache	Retrieve
Implicit	prepareStatement() prepareCall()	close()	prepareStatement() prepareCall()
Explicit	createStatement() prepareStatement() prepareCall()	closeWithKey()	createStatementWithKey() prepareCallWithKey() prepareStatementWithKey()

---