13
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

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:

• 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. Enabling statement caching automatically enables implicit and explicit statement caching. Both 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 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.

As mentioned previously, 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 13-1 illustrates the implicit statement caching concept.

Figure 13-1 Implicit Statement Caching Process

Text description of the illustration impsc.gif

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

Table 13-1 Comparing Methods Used in Statement Caching 

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

Using Statement Caching

This section discusses the following topics:

• Enabling and Disabling Statement Caching

• Checking for Statement Creation Status

• Physically Closing a Cached Statement

• Using Implicit Statement Caching

• Using Explicit Statement Caching

Enabling and Disabling Statement Caching

You can dynamically enable and disable statement caching. Enable statement caching by calling the setStmtCacheSize() method of your connection object, setting the cache to a value greater than zero. The cache size you specify is the maximum number of statements in the cache.

The setStmtCacheSize() method has the following signatures:

• setStmtCacheSize (int size)

• setStmtCacheSize (int size, boolean clearMetaData)

The size parameter sets the maximum number of statements in the cache. The optional clearMetaData parameter allows you to specify if metadata is to be cleared when the cached statement is retrieved.

Notes: When you enable or disable statement caching for a particular physical connection, you do this for both implicit and explicit statement caching. 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. Therefore, consider this when you set the statement cache size

The following code specifies a cache size of ten statements:

((OracleConnection)conn).setStmtCacheSize(10);

Disable statement caching by calling the setStmtCacheSize() method and setting the cache to zero. To check the cache size, use the getStmtCacheSize() method.

The following code disables statement caching by setting the cache size to zero. Additionally, it verifies the new cache setting in the last line of code.

((OracleConnection)conn).setStmtCacheSize(0);
System.out.println("Stmt Cache size is " +
   ((OracleConnection)conn).getStmtCacheSize());

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 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 statement caching, by default all prepared and callable statements are automatically cached. Implicit statement caching includes the following steps:

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

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

3. Optionally 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.

For a complete code example of implicit statement caching, see "Implicit Statement Caching--StmtCache1.java". 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 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 setDisableStmtCaching() method of the statement object. To help you manage cache space, you can call the setDisableStmtCaching() 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).setDisableStmtCaching (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 recall an implicitly cached statement, call either the prepareStatement() or prepareCall()method, depending on the statement type.

The following code recalls 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 13-2 describes the methods used to allocate statements and retrieve implicitly cached statements.

Table 13-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 statement caching. Explicit statement caching includes the following steps:

1. Enable statement caching as described in "Enabling and Disabling 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.

For a complete code example of explicit statement caching, see "Explicit Statement Caching--StmtCache2.java". 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 createStatementWithKey(), prepareStatementWithKey(), or prepareCallWithKey() method, 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 prepareStatementWithKey() method. Recall that the pstmt statement object was cached with the "mykey" key.

pstmt = ((OracleConnection)conn).prepareStatementWithKey ("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 prepareStatementWithKey() method to retrieve that statement from cache. The JDBC driver cannot verify the type of statement it is returning.

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

Table 13-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
prepareStatementWithKey()	specifies the key needed to retrieve a prepared statement from cache
prepareCallWithKey()	specifies the key needed to retrieve a callable statement from cache