6 Statement Caching

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.

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 6-1 compares the different methods employed in implicit and explicit statement caching.

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.

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

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

((OracleConnection)conn).setExplicitCachingEnabled(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:

1. Enable implicit statement caching as described in "Enabling and Disabling Implicit Statement Caching".

2. Allocate a statement using one of the standard methods.

3. (Optional) Disable implicit statement caching for any particular statement you do not want to cache.

4. Cache the statement using the close() method.

5. Retrieve the implicitly cached statement by calling the appropriate standard "prepare" method.

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

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

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

((OraclePreparedStatement)pstmt).close ();

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

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:

1. Enable explicit statement caching as described in "Enabling and Disabling Explicit Statement Caching".

2. Allocate a statement using one of the standard methods.

3. Explicitly cache the statement by closing it with a key, using the closeWithKey() method.

4. Retrieve the explicitly cached statement by calling the appropriate Oracle "WithKey" method, specifying the appropriate key.

5. 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.

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

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

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