14
Statement Caching

This chapter describes the benefits and use of statement caching, an Oracle JDBC extension.

This following topics are discussed:

About Statement Caching
Using Statement Caching

Note:
Release 2 (9.2) of JDBC provides a new statement cache interface and implementation, replacing the API supported at Release 9.1 0. The previous API is now deprecated.

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. JDBC 3.0 defines a statement-caching interface.

Statement caching can:

Prevent the overhead of repeated cursor creation
Prevent repeated statement parsing and creation

Basics of Statement Caching

Use a statement cache to cache statements associated with a particular physical connection. For a simple connection, the cache is associated with an OracleConnection object. For a pooled connection, the cache is associated with an OraclePooledConnection or PooledConnection object. The OracleConnection and OraclePooledConnection objects include methods to enable statement caching. When you enable statement caching, 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 you enable statement caching 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.

There are two types of statement caching: implicit and explicit. Each type of statement cache can be enabled or disabled independent of the other: you can have either, neither, or both in effect. Both types of statement caching share a cache.

Implicit Statement Caching

When you enable implicit statement caching, JDBC 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.)

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.

Note:

The JDBC driver does not clear metadata. However, although metadata is saved for performance reasons, it has no semantic impact. A statement that comes from the implicit cache appears as if it were newly created.

You can prevent a particular statement from being implicitly cached; see "Disabling Implicit Statement Caching for a Particular Statement".

Explicit Statement Caching

Explicit statement caching enables you to cache and retrieve selected prepared, callable, and plain statements. Explicit statement caching relies on a key, an arbitrary Java string that you provide.

Because explicit statement caching retains statement data and state as well as metadata, it has a performance edge over implicit statement caching, which retains only 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.

With implicit statement caching, you take no special action to retrieve statements from a cache. Instead, whenever you call prepareStatement() or prepareCall(), JDBC automatically checks the cache for a matching statement and returns it if found.

With explicit statement caching, you use 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	Insert Into Cache	Retrieve From Cache
Implicit	`prepareStatement() prepareCall()`	`close()`	`prepareStatement() prepareCall()`
Explicit	`createStatement() prepareStatement() prepareCall()`	`closeWithKey()`	`getStatementWithKey() getCallWithKey()`

Using Statement Caching

This section discusses the following topics:

Enabling and Disabling Statement Caching

Implicit and explicit statement caching can be enabled or disabled independent of one other: you can have either, neither, or both in effect.

Enabling and Disabling Implicit Statement Caching

Enable implicit statement caching in one of two ways:

Invoking setImplicitStatementCaching(true) on the connection
Invoking OracleDataSource.getConnection () with the ImplicitStatementCachingEnabled property set to true; you set ImplicitStatementCachingEnabled by calling OracleDataSource.setImplicitStatementCachingEnabled(true)

Disable implicit statement caching by invoking setImplicitStatementCaching(false) on the connection or by setting the ImplicitStatementCachingEnabled property to false.

To determine whether implicit caching is enabled, call getImplicitStatementCachingEnabled(), which returns true if implicit caching is enabled, false otherwise.

Enabling and Disabling Explicit Statement Caching

To enable explicit statement caching you must first set the application cache size. You set the cache size in one of two ways:

invoking OracleConnection.setStatementCacheSize() on the physical connection
invoking OracleDatasource.setMaxStatements()

In either case, the argument you supply is the maximum number of statements in the cache; an argument of 0 specifies no caching. To check the cache size, use the getStatementCacheSize() method.

System.out.println("Stmt Cache size is " +
   ((OracleConnection)conn).getStatementCacheSize());

Enable explicit statement caching by invoking setExplicitStatementCaching(true) on the connection.

To determine whether explicit caching is enabled, call getExplicitStatementCachingEnabled(), which returns true if implicit caching is enabled, false otherwise.

Notes:

You enable implicit and explicit caching for a particular physical connection independently. Therefore, it is possible to do statement caching both implicitly and explicitly during the same session.
Implicit and explicit statement caching share the same cache. Remember this when you set the statement cache size.

The following code specifies a cache size of ten statements:

((OracleConnection)conn).setStatementCacheSize(10);

Disable explicit statement caching by calling setExplicitStatementCaching(false). Disabling caching or closing the cache purges the cache.

The following code disables explicit statement caching.

((OracleConnection)conn).setExplicitStatementCaching(false);

Checking for Statement Creation Status

By calling the creationState() method of a statement object, you can determine if a statement was newly created or if it was retrieved from cache on an implicit or explicit lookup. The creationState() method returns the following integer values for plain, prepared, and callable statements:

NEW - The statement was newly created.
IMPLICIT - The statement was retrieved on an implicit statement lookup.
EXPLICIT - The statement was retrieved on an explicit statement lookup.

For example, the JDBC driver returns OracleStatement.EXPLICIT for an explicitly cached statement. The following code checks the statement creation status for stmt:

int state = ((OracleStatement)stmt).creationState()
   ...(process state)

Physically Closing a Cached Statement

With implicit statement caching enabled, you cannot truly physically close statements manually. The close() method of a statement object caches the statement instead of closing it. The statement is physically closed automatically under one of three conditions: (1) when the associated connection is closed, (2) when the cache reaches its size limit and the least recently used statement object is preempted from cache by the LRU scheme, or (3) if you call the close() method on a statement for which statement caching is disabled. (See "Disabling Implicit Statement Caching for a Particular Statement" for more details.)

Using Implicit Statement Caching

Once you enable implicit statement caching, by default all prepared and callable statements are automatically cached. Implicit statement caching includes the following steps:

Enable implicit statement caching as described in "Enabling and Disabling Implicit Statement Caching".
Allocate a statement using one of the standard methods.
(Optional) Disable implicit statement caching for any particular statement you do not want to cache.
Cache the statement using the close() method.
Retrieve the implicitly cached statement by calling the appropriate standard "prepare" method.

The following sections explain the implicit statement caching steps in more detail.

Allocating a Statement for Implicit Caching

To allocate a statement for implicit statement caching, use either the prepareStatement() or prepareCall()method as you would normally. (These are methods of the connection object.)

The following code allocates a new statement object called pstmt:

PreparedStatement pstmt = conn.prepareStatement

   ("UPDATE emp SET ename = ? WHERE rowid = ?");

Disabling Implicit Statement Caching for a Particular Statement

With implicit statement caching enabled for a connection, by default all callable and prepared statements of that connection are automatically cached. To prevent a particular callable or prepared statement from being implicitly cached, use the setDisableStatementCaching() method of the statement object. To help you manage cache space, you can call the setDisableStatementCaching() method on any infrequently used statement.

The following code disables implicit statement caching for pstmt:

PreparedStatement pstmt = conn.prepareStatement ("SELECT 1 from DUAL");
((OraclePreparedStatement)pstmt).setDisableStatementCaching (true);
pstmt.close ();

Implicitly Caching a Statement

To cache an allocated statement, call the close() method of the statement object. When you call the close() method on an OraclePreparedStatement or OracleCallableStatement object, the JDBC driver automatically puts this statement in cache, unless you have disabled caching for this statement.

The following code caches the pstmt statement:

((OraclePreparedStatement)pstmt).close ();

Retrieving an Implicitly Cached Statement

To retrieve an implicitly cached statement, call either the prepareStatement() or prepareCall()method, depending on the statement type.

The following code retrieves pstmt from cache using the prepareStatement() method:

pstmt = conn.prepareStatement ("UPDATE emp SET ename = ? WHERE rowid = ?");

If you call the creationState() method on the pstmt statement object, the method returns IMPLICIT. If the pstmt statement object was not in cache, then the creationState() method returns NEW to indicate a new statement was recently created by the JDBC driver.

Table 14-2 describes the methods used to allocate statements and retrieve implicitly cached statements.

Table 14-2 Methods Used in Statement Allocation and Implicit Statement Caching

Method	Functionality for Implicit Statement Caching
`prepareStatement()`	Triggers a cache search that either finds and returns the desired cached `OraclePreparedStatement` object or allocates a new `OraclePreparedStatement` object if a match is not found
`prepareCall()`	Triggers a cache search that either finds and returns the desired cached `OracleCallableStatement` object or allocates a new `OracleCallableStatement` object if a match is not found

Using Explicit Statement Caching

A plain, prepared, or callable statement can be explicitly cached when you enable explicit statement caching. Explicit statement caching includes the following steps:

Enable explicit statement caching as described in "Enabling and Disabling Explicit Statement Caching".
Allocate a statement using one of the standard methods.
Explicitly cache the statement by closing it with a key, using the closeWithKey() method.
Retrieve the explicitly cached statement by calling the appropriate Oracle "WithKey" method, specifying the appropriate key.
Re-cache an open, explicitly cached statement by closing it again with the closeWithKey() method. Each time a cached statement is closed, it is re-cached with its key.

The following sections explain the explicit statement caching steps in more detail.

Allocating a Statement for Explicit Caching

To allocate a statement for explicit statement caching, use either the createStatement(), prepareStatement(), or prepareCall() method as you would normally. (These are methods of the connection object.)

The following code allocates a new statement object called pstmt:

PreparedStatement pstmt =

   conn.prepareStatement ("UPDATE emp SET ename = ? WHERE rowid = ?");

Explicitly Caching a Statement

To explicitly cache an allocated statement, call the closeWithKey() method of the statement object, specifying a key. The key is an arbitrary Java string that you provide. The closeWithKey() method caches a statement as is. This means the data, state, and metadata are retained and not cleared.

The following code caches the pstmt statement with the key "mykey":

((OraclePreparedStatement)pstmt).closeWithKey ("mykey");

Retrieving an Explicitly Cached Statement

To recall an explicitly cached statement, call either the getStatementWithKey() or getCallWithKey() methods depending on the statement type.

If you retrieve a statement with a specified key, the JDBC driver searches the cache for the statement, based on the specified key. If a match is found, the matching statement is returned, along with its state, data, and metadata. This information is returned as it was when last closed. If a match is not found, the JDBC driver returns null.

The following code recalls pstmt from cache using the "mykey" key with the gettatementWithKey() method. Recall that the pstmt statement object was cached with the "mykey" key.

pstmt = ((OracleConnection)conn).getStatementWithKey ("mykey");

If you call the creationState() method on the pstmt statement object, the method returns EXPLICIT.

Important:

When you retrieve an explicitly cached statement, be sure to use the method that is appropriate for your statement type when specifying the key. For example, if you used the prepareStatement() method to allocate a statement, then use the getStatementWithKey() method to retrieve that statement from cache. The JDBC driver cannot verify the type of statement it is returning.

Table 14-3 describes the methods used to retrieve explicitly cached statements.

Table 14-3 Methods Used to Retrieve Explicitly Cached Statements

Method	Functionality for Explicit Statement Caching
`createStatementWithKey()`	specifies the key needed to retrieve a plain statement from cache
`getStatementWithKey()`	specifies the key needed to retrieve a prepared statement from cache
`getCallWithKey()`	specifies the key needed to retrieve a callable statement from cache

14 Statement Caching