This chapter describes the benefits and use of statement caching, an Oracle Java Database Connectivity (JDBC) extension.
This chapter contains the following sections:
Note:
In Oracle9i Database 9.2.0 and later releases, Oracle JDBC provides a new statement cache interface and implementation, replacing the application programming interface (API) supported in Oracle9i Database release 1 (9.0.1). The previous API is now deprecated.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
Reuse data structures in the client
This section covers the following topics:
Applications use the statement cache to cache statements associated with a particular physical connection. The cache is associated with an OracleConnection
object. OracleConnection
includes methods to enable statement caching. When you enable statement caching, a statement object is cached when you call the close
method.
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 connection cache, the logical connections benefit from the statement caching that is enabled on the underlying physical connection. If you try to enable statement caching on a logical connection held by a connection cache, then 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 single cache per connection.
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. You cannot use implicit statement caching with OracleStatement
. When you create an OraclePreparedStatement
or OracleCallableStatement
, 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 to one in the cache.
The statement type must be the same, that is, prepared or callable.
The scrollable type of result sets produced by the statement must be the same, that is, forward-only or scrollable.
If a match is found during the cache search, then the cached statement is returned. If a match is not found, then a new statement is created and returned. In either case, the 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. Statements are removed from the cache to conform to the maximum size using an Least Recently Used (LRU) algorithm.
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.
Explicit statement caching enables you to cache and retrieve selected prepared and callable statements. Explicit statement caching relies on a key, an arbitrary Java String
that you provide.
Note:
Plain statements cannot be cached.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, you must be cautious when using this type of caching, because explicit statement caching saves all three types of information for reuse and you may not be aware of what data and state are retained from prior use of the statements.
Implicit and explicit statement caching can be differentiated on the following points:
Retrieving statements
In the case of 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. However, in the case of explicit statement caching, you use specialized Oracle WithKey methods to cache and retrieve statement objects.
Providing key
Implicit statement caching uses the SQL string of a prepared or callable statement as the key, requiring no action on your part. In contrast, explicit statement caching requires you to provide a Java String
, which it uses as the key.
Returning statements
During implicit statement caching, if the JDBC driver cannot find a statement in cache, then it will automatically create one. However, during explicit statement caching, if the JDBC driver cannot find a matching statement in cache, then it will return a null
value.
Table 22-1 compares the different methods employed in implicit and explicit statement caching.
This section discusses the following topics:
When using the OracleConnection
API, implicit and explicit statement caching can be enabled or disabled independent of one other. You can have either, neither, or both in effect.
When using the connection cache, you enable statement caching by setting the MaxStatementsLimit
property on the connection cache. The MaxStatementsLimit
applies to every connection in the cache. Each connection has its own statement cache with the same maximum size. This provides a global way to throttle the statement cache size for all connections in the cache. Enabling statement caching enables both implicit and explicit statement caching.
Enabling and Disabling Implicit Statement Caching
Enable implicit statement caching in one of two ways:
Call setImplicitCachingEnabled(true)
on the connection
Call OracleDataSource.getConnection
with the ImplicitCachingEnabled
property set to true
. You set ImplicitCachingEnabled
by calling OracleDataSource.setImplicitCachingEnabled(true)
In addition to calling one of these methods, you also need to call OracleConnection.setStatementCacheSize
on the physical connection. The argument you supply is the maximum number of statements in the cache. An argument of 0
specifies no caching.
Disable implicit statement caching by calling setImplicitCachingEnabled(false)
on the connection or by setting the ImplicitCachingEnabled
property to false
.
To determine whether implicit caching is enabled, call getImplicitCachingEnabled
, 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 statement cache size. You set the cache size in one of two ways:
Call OracleConnection.setStatementCacheSize
on the physical connection
Call 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());
The following code specifies a cache size of ten statements:
((OracleConnection)conn).setStatementCacheSize(10);
Enable explicit statement caching by calling setExplicitCachingEnabled(true)
on the connection.
To determine whether explicit caching is enabled, call getExplicitCachingEnabled
, which returns true
if explicit 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.
Disable explicit statement caching by calling setExplicitCachingEnabled(false)
. Disabling caching or closing the cache purges the cache. The following example disables explicit statement caching:
((OracleConnection)conn).setExplicitCachingEnabled(false);
With implicit statement caching enabled, you cannot 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 following three conditions:
When the associated connection is closed
When the cache reaches its size limit and the least recently used statement object is preempted from cache by the LRU scheme
If you call the close
method on a statement for which statement caching is disabled
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.
Allocate a statement using one of the standard methods.
Disable implicit statement caching for any particular statement you do not want to cache. This is an optional step.
Cache the statement using the close
method.
Retrieve the implicitly cached statement by calling the appropriate standard prepare method.
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.
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. You can manage cache space by calling 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).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 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 = ?");
Table 22-2 describes the methods used to allocate statements and retrieve implicitly cached statements.
Table 22-2 Methods Used in Statement Allocation and Implicit Statement Caching
Method | Functionality for Implicit Statement Caching |
---|---|
Performs a cache search that either finds and returns the desired cached |
|
Performs a cache search that either finds and returns the desired cached |
A 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.
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.
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.
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, then the JDBC driver searches the cache for the statement, based on the specified key. If a match is found, then the matching statement is returned along with its state, data, and metadata. This information is as it was when the statement was last closed. If a match is not found, then the JDBC driver returns null
.
The following code recalls pstmt
from cache using the "mykey"
key with the getStatementWithKey
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, then the method returns EXPLICIT
.
Important:
When you retrieve an explicitly cached statement, ensure that you use the method that is appropriate for your statement type when specifying the key. For example, if you used theprepareStatement
method to allocate a statement, then use the getStatementWithKey
method to retrieve that statement from cache. The JDBC driver does not verify the type of statement it is returning.Table 22-3 describes the methods used to retrieve explicitly cached statements.