Connection caching, generally implemented in the middle tier, is a means of keeping and using the cache of physical database connections.
Note:
The previous cache architecture, based onOracleConnectionCache andOracleConnectionCacheImpl, is deprecated. Oracle recommends that you take advantage of the new architecture, which is more powerful and offers better performance.Theimplicit connection cache is an improved Java Database Connectivity (JDBC) 3.0-compliant connection cache implementation forDataSource. Java and Java2 Platform, Enterprise Edition (J2EE) applications benefit from transparent access to the cache, support for multiple users, and the ability to request connections based on user-defined profiles.
An application turns the implicit connection cache on by callingsetConnectionCachingEnabled(true) on anOracleDataSource. After implicit caching is turned on, the first connection request to theOracleDataSource transparently creates a connection cache. There is no need for application developers to write their own cache implementations.
This chapter is divided into the following sections:
Note:
The concept of connection caching is not relevant to the server-side internal driver, where you always use the default connection. Connection caching is relevant only to the client-side JDBC drivers and the server-side Thin driver.The connection caching architecture has been redesigned so that caching is transparently integrated into the data source architecture.
The connection cache uses the concept of physical connections and logical connections. Physical connections are the actual connections returned by the database and logical connections are containers used by the cache to manipulate physical connections. You can think of logical connections as handles. The caches always return logical connections, which implement the same interfaces as physical connections.
The implicit connection cache offers the following:
Driver independence
Both the JDBC Thin and JDBC Oracle Call Interface (OCI) drivers support the implicit connection cache.
Transparent access to the JDBC connection cache
After an application turns implicit caching on, it uses the standardOracleDataSource application programming interfaces (APIs) to get connections. With caching enabled, all connection requests are serviced from the connection cache.
When an application calls theOracleConnection.close method to close the logical connection, the physical connection is returned to the cache.
Single cache perOracleDataSource instance
When connection caching is turned on, each cache-enabledOracleDataSource has exactly one cache associated with it. All connections obtained through that data source, no matter what user name and password are used, are returned to the cache. When an application requests a connection from the data source, the cache either returns an existing connection or creates a new connection with matching authentication information.
Note:
Caches cannot be shared betweenDataSource instances. There is a one-to-one mapping between cache-enabledDataSource instances and caches.Heterogeneous user names and passwords per cache
Unlike the previous cache implementation, all connections obtained through the same data source are stored in a common cache, no matter what user name and password the connection requests.
Support for JDBC 3.0 connection caching, including support for multiple users and the required cache properties
Property-based configuration
Cache properties define the behavior of the cache. The supported properties set timeouts, the number of connections to be held in the cache, and so on. Using these properties, applications can reclaim and reuse abandoned connections. The implicit connection cache supports all the JDBC 3.0 connection cache properties.
OracleConnectionCacheManager
The newOracleConnectionCacheManager class provides a rich set of administrative APIs that applications can use to manage the connection cache. Each virtual machine has one distinguished instance ofOracleConnectionCacheManager. Applications manage a cache through the singleOracleConnectionCacheManager instance.
User-defined connection attributes
The implicit connection cache supports user-defined connection attributes that can be used to determine which connections are retrieved from the cache. Connection attributes can be thought of as labels whose semantics are defined by the application, not by the caching mechanism.
Callback mechanism
The implicit connection cache provides a mechanism for users to define cache behavior when a connection is returned to the cache, when handling abandoned connections, and when a connection is requested but none is available in the cache.
Connect-time load balancing
Implicit connection caching provides connect-time load balancing when a connection is first created by the application. The database listener distributes connection creation across Oracle Real Application Clusters instances that would perform the best at the time of connection creation.
Run-time connection load balancing
Run-time connection load balancing of work requests uses Service Metrics to route work requests to an Oracle Real Application Clusters instance that offers the best performance. Selecting a connection from the cache based on service, to execute a work request, greatly increases the throughput and scalability.
This section discusses how applications use the implicit connection cache. It covers the following topics:
An application turns the implicit connection cache on by callingOracleDataSource.setConnectionCachingEnabled(true). After implicit caching is turned on, the first connection request to theOracleDataSource class transparently creates a connection cache.
Example 21-1 provides a sample code that uses the implicit connection cache.
Example 21-1 Using the Implicit Connection Cache
// Example to show binding of OracleDataSource to JNDI,// then using implicit connection cache import oracle.jdbc.pool.*; // import the pool package Context ctx = new InitialContext(ht);OracleDataSource ods = new OracleDataSource(); // Set DataSource propertiesods.setUser("Scott");ods.setConnectionCachingEnabled(true); // Turns on cachingctx.bind("MyDS", ods);// ...// Retrieve DataSource from the InitialContextods =(OracleDataSource) ctx. lookup("MyDS"); // Transparently create cache and retrieve connection conn = ods.getConnection();// ...conn.close(); // return connection to the cache// ...ods.close() // close datasource and clean up the cacheAfter you have turned connection caching on, whenever you retrieve a connection through theOracleDataSource.getConnection method, the JDBC drivers check to see if a connection is available in the cache.
ThegetConnection method checks if there are any free physical connections in the cache that match the specified criteria. If a match is found, then a logical connection is returned, wrapping the physical connection. If no physical connection match is found, then a new physical connection is created, wrapped in a logical connection, and returned.
There are four variations ongetConnection, two that make no reference to the connection cache and two that specify which sort of connections the cache may return. The non-cache-specificgetConnection methods behave in the standard manner.
Note:
When implicit connection cache is enabled, the connection returned byOracleDataSource.getConnection may not have the state reset. You must, therefore, reset all the connection states, such as auto-commit, batch size, prefetch size, transaction status, and transaction isolation, before putting the connection back into the cache.TheConnectionCacheName property ofOracleDataSource is an optional property used by the Connection Cache Manager to manage a connection cache. You can set this property by calling the following method:
public void synchronized setConnectionCacheName(String cacheName) throws SQLException
When this property is set, the name is used to uniquely identify the cache accessed by the cache-enabledOracleDataSource. If the property is not set, then a default cache name is created using the conventionDataSourceName#HexRepresentationOfNumberOfCaches.
Note:
ThegetConnectionCacheName() method will return the name of the connection cache only if thesetConnectionCacheName method is called after thesetConnectionCachingEnabled method is called.You can fine-tune the behavior of the implicit connection cache using thesetConnectionCacheProperties method to set various connection properties.
See Also:
"Connection Cache Properties"Note:
Before setting the cache-specific properties, you must enable caching on the data source; otherwise thesetConnectionCacheProperties method will have no effect.
Although these properties govern the behavior of the connection cache, they are set on the data source, and not on the connection or on the cache itself.
An application returns a connection to the cache by calling theclose method. There are two variants of theclose method: one with no arguments and one that takes aConnection object as argument.
Note:
Applications must close connections to ensure that the connections are returned to the cache.
When implicit connection cache is enabled, you must reset all the connection states, such as auto-commit, batch size, prefetch size, transaction status, and transaction isolation, before putting the connection back into the cache. This ensures that any subsequent connection retrieved from the cache will have its state reset.
Example 21-2 demonstrates creating a data source, setting its caching and data source properties, retrieving a connection, and closing that connection in order to return it to the cache.
Example 21-2 Connection Cache Example
import java.sql.*;import javax.sql.*;import java.util.*;import javax.naming.*;import javax.naming.spi.*;import oracle.jdbc.*;import oracle.jdbc.pool.*; ... // create a DataSource OracleDataSource ods = new OracleDataSource(); // set cache properties java.util.Properties prop = new java.util.Properties(); prop.setProperty("MinLimit", "2"); prop.setProperty("MaxLimit", "10"); // set DataSource properties String url = "jdbc:oracle:oci8:@"; ods.setURL(url); ods.setUser("hr"); ods.setPassword("hr"); ods.setConnectionCachingEnabled(true); // be sure set to true ods.setConnectionCacheProperties (prop); ods.setConnectionCacheName("ImplicitCache01"); // this cache's name // We need to create a connection to create the cache Connection conn = ds.getConnection(user, pass); Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("select user from dual"); conn.close(); ods.close();Each connection obtained from a data source can have user-defined attributes. Attributes are specified by the application developer and arejava.lang.Properties name and value pairs.
An application can use connection attributes to supply additional semantics to identify connections. For instance, an application may create an attribute namedconnection_type and then assign it the valuepayroll orinventory.
Note:
The semantics of connection attributes are entirely application-defined. The connection cache itself enforces no restrictions on the key or value of connection attributes.The methods that get and set connection attributes are found on theOracleConnection interface. This section covers the following topics:
The first connection you retrieve has no attributes. You must set them. After you have set attributes on a connection, you can request the connection by specifying its attribute, using the specialized forms of thegetConnection method:
getConnection(java.util.Properties cachedConnectionAttributes
Requests a database connection that matches the specifiedcachedConnectionAttributes.
See Also:
For a discussion on connection attributes, see"Other Properties" .getConnection(java.lang.String user, java.lang.String password, java.util.Properties cachedConnectionAttributes)
Requests a database connection from the implicit connection cache that matches the specifieduser,password andcachedConnectionAttributes. If null values are passed foruser andpassword, theDataSource defaults are used.
The rules for matchingconnectionAttributes come in two variations:
Basic
In this variation, the cache is searched to retrieve the connection that matches the attributes. The connection search mechanism as follows:
If an exact match is found, the connection is returned to the caller.
If an exact match is not found and theClosestConnectionMatch data source property is set, then the connection with the closest match is returned. The closest matched connection is one that has the highest number of the original attributes matched. Note that the closest matched connection may match a subset of the original attributes, but does not have any attributes that are not part of the original list. For example, if the original list of attributes is A, B and C, then a closest match may have A and B set, but never a D.
If none of the existing connections matches, a new connection is returned. The new connection is created using the user name and password set on theDataSource. IfgetConnection(String, String, java.util.Properties) is called, then the user name and password passed as arguments are used to open the new connection.
Advanced
In this variation, the attributes may be associated with weights. The connection search mechanism is similar to the basicconnectionAttributes based search, except that the connections are searched not only based on theconnectionAttributes, but also using a set of weights that are associated with the keys on theconnectionAttributes. These weights are assigned to the keys as a one-time operation and is supported as a connection cache property,AttributeWeights.
An application sets connection attributes using the following:
applyConnectionAttributes(java.util.Properties connAttr)
No validation is done onconnAttr. Applying connection attributes is cumulative. Each time you callapplyConnectionAttributes, theconnAttr attribute you supply is added to those previously in force.
When an application requests a connection with specified attributes, it is possible that no match will be found in the connection cache. When this happens, the connection cache creates a connection with no attributes and returns it. The connection cache cannot create a connection with the requested attributes, because the Connection Cache manager is ignorant of the semantics of the attributes.
Note:
If theclosestConnectionMatch property has been set, then the cache manager looks for close attribute matches rather than exact matches.For this reason, applications should always check the attributes of a returned connection. To do this, use thegetUnMatchedConnectionAttributes method, which returns a list of any attributes that were not matched in retrieving the connection. If the return value of this method isnull, you know that you must set all the connection attributes.
Example 21-3 illustrates using connection attributes.
Example 21-3 Using Connection Attributes
java.util.Properties connAttr = new java.util.Properties(); connAttr.setProperty("connection_type", "payroll"); // retrieve connection that matches attributes Connection conn = ds.getConnection(connAttr); // Check to see which attributes weren't matched unmatchedProp = ((OracleConnection)conn).getUnMatchedConnectionAttributes(); if ( unmatchedProp != null ) { // apply attributes to the connection ((OracleConnection)conn).applyConnectionAttributes(connAttr); } // verify whether conn contains property after apply attributes connProp = ((OracleConnection)conn).getConnectionAttributes(); listProperties (connProp);The connection cache properties govern the characteristics of a connection cache. This section lists the supported connection cache properties. It covers the following topics:
Applications set cache properties in one of the following ways:
Using theOracleDataSource methodsetConnectionCacheProperties
When creating a cache usingOracleConnectionCacheManager
When reinitializing a cache usingOracleConnectionCacheManager
These properties control the size of the cache.
Sets how many connections are created in the cache when it is created or reinitialized. When this property is set to an integer value greater than 0, creating or reinitializing the cache automatically creates the specified number of connections, filling the cache in advance of need.
Default: 0
Sets the maximum number of connection instances the cache can hold. The default value isInteger.MAX_VALUE, meaning that there is no limit enforced by the connection cache, so that the number of connections is limited only by the number of database sessions configured for the database.
Default:Integer.MAX_VALUE (no limit)
Note:
If the number of concurrent connections exceeds the maximum number of sessions configured at the database server, then you will getORA-00018 error. To avoid this error, you must set a value for theMaxLimit property. This value should be less than the value of theSESSIONS parameter configured for the database server.Sets the maximum number of statements that a connection keeps open. When a cache has this property set, reinitializing the cache or closing the data source automatically closes all cursors beyond the specifiedMaxStatementsLimit.
Default: 0
Sets the minimum number of connections the cache maintains.
Default: 0
Note:
Setting theMinLimit property does not initialize the cache to contain the minimum number of connections. To do this, use theInitialLimit property.
WhenInitialLimit is greater thanMinLimit, it is possible to have any number of connections specified byInitialLimit up to a value specified byMaxLimit. Therefore,InitialLimit does not depend onMinLimit.
Connections can fall below the minimum limit set on the connection pool when JDBC Fast Connection Failover DOWN events are processed. The processing removes affected connections from the pool.MinLimit will be honored as requests to the connection pool increase and the number of connections get past theMinLimit value.
These properties control the lifetime of an element in the cache.
Sets the maximum time a physical connection can remain idle in a connection cache. An idle connection is one that is not active and does not have a logical handle associated with it. WhenInactivityTimeout expires, the underlying physical connection is closed. However, the size of the cache is not allowed to shrink belowminLimit, if it has been set.
Default: 0 (no timeout in effect)
Sets the maximum time in seconds that a logical connection can remain open. WhenTimeToLiveTimeout expires, the logical connection is unconditionally closed, the relevant statement handles are canceled, and the underlying physical connection is returned to the cache for reuse.
Default: 0 (no timeout in effect)
Sets the maximum time that a connection can remain unused before the connection is closed and returned to the cache. A connection is considered unused if it has not had SQL database activity.
WhenAbandonedConnectionTimeout is set, JDBC monitors SQL database activity on each logical connection. For example, whenstmt.execute is called on the connection, a heartbeat is registered to convey that this connection is active. The heartbeats are set at each database execution. If a connection has been inactive for the specified amount of time, the underlying connection is reclaimed and returned to the cache for reuse.
Default: 0 (no timeout in effect)
Sets the time interval at which the Connection Cache Manager inspects and enforces all specified cache properties.PropertyCheckInterval is set in seconds.
Default: 900 seconds
These properties control miscellaneous cache behavior.
AttributeWeights sets the weight for each attribute set on the connection.
See Also:
"AttributeWeights"ClosestConnectionMatch causes the connection cache to retrieve the connection with the closest approximation to the specified connection attributes.
See Also:
"ClosestConnectionMatch"Specifies cache behavior when a connection is requested and there are alreadyMaxLimit connections active. IfConnectionWaitTimeout is equal to zero, then each connection request waits for zero seconds, that is,null connection is returned immediately. IfConnectionWaitTimeout is greater than zero, then each connection request waits for the specified number of seconds or until a connection is returned to the cache. If no connection is returned to the cache before the timeout elapses, then the connection request returnsnull.
Default: zero
Sets the lower threshold limit on the cache. The default is 20 percent of theMaxLimit on the connection cache. This property is used whenever areleaseConnection() cache callback method is registered.
SettingValidateConnection totrue causes the connection cache to test every connection it retrieves against the underlying database. If a valid connection cannot be retrieved, then an exception is thrown.
Default:false
Example 21-4 demonstrates how an application uses connection properties.
Example 21-4 Using Connection Properties
import java.sql.*;import javax.sql.*;import java.util.*;import javax.naming.*;import javax.naming.spi.*;import oracle.jdbc.*;import oracle.jdbc.pool.*;... OracleDataSource ds = (OracleDataSource) ctx.lookup("..."); java.util.Properties prop = new java.util.Properties (); prop.setProperty("MinLimit", "5"); // the cache size is 5 at least prop.setProperty("MaxLimit", "25"); prop.setProperty("InitialLimit", "3"); // create 3 connections at startup prop.setProperty("InactivityTimeout", "1800"); // seconds prop.setProperty("AbandonedConnectionTimeout", "900"); // seconds prop.setProperty("MaxStatementsLimit", "10"); prop.setProperty("PropertyCheckInterval", "60"); // seconds ds.setConnectionCacheProperties (prop); // set properties Connection conn = ds.getConnection(); conn.dosomework(); java.util.Properties propList=ds.getConnectionCacheProperties(); // retrieveOracleConnectionCacheManager provides administrative APIs that the middle tier can use to manage available connection caches. This section provides an example of using the Connection Cache Manager.
Example of ConnectionCacheManager Use
Example 21-5 demonstrates theOracleConnectionCacheManager interface.
Example 21-5 Connection Cache Manager Example
import java.sql.*;import javax.sql.*;import java.util.*;import javax.naming.*;import javax.naming.spi.*;import oracle.jdbc.*;import oracle.jdbc.pool.*;...// Get singleton ConnectionCacheManager instance OracleConnectionCacheManager occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance(); String cacheName = "foo"; // Look for a specific cache // Use Cache Manager to check # of available connections // and active connections System.out.println(occm.getNumberOfAvailableConnections(cacheName) " connections are available in cache " + cacheName); System.out.println(occm.getNumberOfActiveConnections(cacheName) + " connections are active"); // Refresh all connections in cache occm.refreshCache(cacheName, OracleConnectionCacheManager.REFRESH_ALL_CONNECTIONS); // Reinitialize cache, closing all connections java.util.Properties newProp = new java.util.Properties(); newProp.setProperty("MaxLimit", "50"); occm.reinitializeCache(cacheName, newProp);This section discusses cache functionality that is useful for advanced users, but is not essential to understanding or using the implicit connection cache. This section covers the following topics:
There are two connection cache properties that enable the developer to specify which connections in the connection cache are accepted in response to agetConnection request. When you set theClosestConnectionMatch property totrue, you are telling the Connection Cache Manager to return connections that match only some of the attributes you have specified.
If you do not specifyattributeWeights, then the Connection Cache Manager returns the connection that matches the highest number of attributes. If you specifyattributeWeights, then you can control the priority the manager uses in matching attributes.
SettingClosestConnectionMatch totrue causes the connection cache to retrieve the connection with the closest approximation to the specified connection attributes. This can be used in combination withAttributeWeights to specify what is considered a closest match.
Default:false
Sets the weights for eachconnectionAttribute. This property is used whenClosestConnectionMatch is set totrue to determine which attributes are given highest priority when searching for matches. An attribute with a high weight is given more importance in determining a match than an attribute with a low weight.
AttributeWeights contains a set ofStrings representing key-value pairs. Each key/value pair sets the weights for eachconnectionAttribute for which the user intends to request a connection. EachString is in the format written by thejava.util.Properties.Store(OutputStream, String) method, and thus can be read by thejava.util.Properties.load(InputStream) method. TheKey is aconnectionAttribute and theValue is the weight. A weight must be an integer value greater than 0. The default weight is 1.
For example,TRANSACTION_ISOLATION could be assigned a weight of 10 andROLE a weight of 5. IfClosestConnectionMatch is set totrue, when aconnectionAttribute based connection request is made on the cache, connections with a matchingTRANSACTION_ISOLATION will be favored over connections with a matchingROLE.
Default: NoAttributeWeights
The implicit connection cache offers a way for the application to specify callbacks to be called by the connection cache. Callback methods are supported with theOracleConnectionCacheCallback interface. This callback mechanism is useful to take advantage of the special knowledge of the application about particular connections, supplementing the default behavior when handling abandoned connections or when the cache is empty.
OracleConnectionCacheCallback is an interface that must be implemented by the user and registered withOracleConnection. The registration API is as follows:
public voidregisterConnectionCacheCallback(OracleConnectionCacheCallback cbk, Object usrObj, int cbkflag);
In this interface,cbk is the user implementation of theOracleConnectionCacheCallback interface. TheusrObj parameter contains any parameters that the user wants supplied. This user object is passed back, unmodified, when the callback method is called. Thecbkflag parameter specifies which callback method should be called. It must be one of the following values:
OracleConnection.ABANDONED_CONNECTION_CALLBACK
OracleConnection.RELEASE_CONNECTION_CALLBACK
OracleConnection.ALL_CALLBACKS
WhenALL_CALLBACKS is set, all the connection cache callback methods are called. For example,
// register callback, to invoke all callback methods((OracleConnection)conn).registerConnectionCacheCallback( new UserConnectionCacheCallback(), new SomeUserObject(), OracleConnection.ALL_CALLBACKS);
An application can register aConnectionCacheCallback on anOracleConnection. When a callback is registered, the connection cache calls thehandleAbandonedConnection method of the callback before reclaiming the connection. If the callback returnstrue, then the connection is reclaimed. If the callback returnsfalse, then the connection remains active.
TheUserConnectionCacheCallback interface supports two callback methods to be implemented by the user,releaseConnection andhandleAbandonedConnection.
The following are the use cases for theTimeToLiveTimeout andAbandonedConnectionTimeout timeout mechanisms when used with implicit connection cache. Note that these timeout mechanisms are applicable to the logical connection when it is retrieved from the connection cache.
The application considers the connections completely stateless.
When the connections are stateless, either of the timeout mechanisms can be used. The connections for which the timeout expires are put back into the connection cache for reuse. These connections are valid for reuse because there is no session state associated with them.
The application maintains state on each connection, but has a cleanup routine that can render the connections stateless when they are returned to the connection cache.
In this case,TimeToLiveTimeout cannot be used. There is no way for the connection cache to ensure that a connection returned to the cache is in a reusable condition.However,AbandonedConnectionTimeout can be used in this scenario, only ifOracleConnectionCacheCallback is registered on the connection. ThehandleAbandonedConnection callback method is implemented by the application and ensures that the necessary cleanup is done. The connection is closed when the timeout processing invokes this callback method. The closing of this connection by the callback method causes the connection to be put back into the connection cache in a state where it is reusable.
Note:
Do not to close the connection after calling handleAbandonedConnection method because the connection could be in an invalid state. JDBC internally knows how to reclaim a connection even when it is in an invalid state.See Also:
"Connection Cache Callbacks"The application maintains state on each connection, but has no control over the connection and, therefore, cannot ensure cleaning up of state for reuse of connections by other applications or users.
The use of either of the timeout mechanisms isnot recommended.