|Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)
Part Number A83724-01
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:
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
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:
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
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:
Figure 14-1 illustrates the implicit statement caching concept.
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
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.
By default, the JDBC driver does not clear metadata. However, if you call the
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".
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
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
Table 14-1 compares the different methods employed in implicit and explicit statement caching.