OCI Driver Connection Pooling

OCI driver connection pooling functionality, provided by the OracleOCIConnectionPool class, is part of the JDBC client. Enhanced connection pooling provides the following benefits:

Improved scalability - The pooling granularity is superior to that provided by the OraclePooledConnection class, since fewer physical connections are needed to support a large number of non-current, logical connections. This is valuable since physical connections are expensive. The physical connection of the OraclePooledConnection object is available for reuse after the application is done using it. Also, since the user session is not closed on the server-side once the OraclePooledConnection object is returned to the pool of available connection objects, every new call to the getConnection() method of the OracleConnectionCacheImpl class requires that the user remain the same. For a dedicated server instance, this results in the number of backend Oracle processes being reduced as the number of in-coming connections are also reduced. To boost performance, a physical connection is locked only for the duration of a call.

The existing connection support of mapping one JDBC user session to one physical connection, and the reuse of physical connection objects using the OraclePooledConnection class, is still supported. (See "Connection Pooling" for details.) However, it is recommended that you use the improved functionality of the OracleOCIConnectionPool class instead.

A JDBC application can have multiple pools at the same time. Multiple pools can correspond to multiple application servers, or pools to different data sources. The connection pooling provided by OCI in Oracle9i allows applications to have many logical connections, all using a small set of physical connections. Each call on this logical connection will be routed on the physical connection that is available at that time. Call-duration based pooling of connections is a more scalable connection pooling solution.

OCI Driver Connection Pooling: Background

With the Oracle9i JDBC OCI driver, there are several transaction monitor capabilities such as the fine-grained management of Oracle sessions and connections. It is possible for a high-end application server or transaction monitor to multiplex several sessions over fewer physical connections on a call-level basis, thereby achieving a high degree of scalability by pooling of connections and backend Oracle server processes.

The connection pooling provided by the OracleOCIConnectionPool interface simplifies the Session/Connection separation interface hiding the management of the physical connection pool. The Oracle sessions are the OracleOCIConnection connection objects obtained from the OracleOCIConnectionPool. The connection pool itself is normally configured with a much smaller shared pool of physical connections, translating to a backend server pool containing an identical number of dedicated server processes. Note that many more Oracle sessions can be multiplexed over this pool of fewer shared connections and backend Oracle processes.

OCI Driver Connection Pooling and Shared Servers Compared

In some ways, what OCI driver connection pooling offers on the middle tier is similar to what shared server processes offeron the backend. OCI driver connection pooling makes a dedicated server instance behave as an shared instance by managing the session multiplexing logic on the middle tier. Therefore, the pooling of dedicated server processes and incoming connections into the dedicated server processes is controlled by the OCI connection pool on the middle tier.

The main difference between OCI connection pooling and shared servers is that in case of shared servers, the connection from the client is normally to a dispatcher in the database instance. The dispatcher is responsible for directing the client request to an appropriate shared server. On the other hand, the physical connection from the OCI connection pool is established directly from the middle tier to the Oracle dedicated server process in the backend server pool.

Note that OCI connection pool is mainly beneficial only if the middle tier is multi-threaded. Each thread could maintain a session to the database. The actual connections to the database are maintained by the OracleOCIConnectionPool and these connections (including the pool of dedicated database server processes) are shared among all the threads in the middle tier.

Stateless Sessions Compared to Stateful Sessions

OCI connection pooling offers stateless physical connections and stateful sessions. If you need to work with a stateless session behavior, you can use the OracleConnectionCacheImpl interface.

Defining an OCI Connection Pool

An OCI connection pool is created at the beginning of the application. Creating connections from a pool is quite similar to creating connections using the OracleDataSource class.

The oracle.jdbc.pool.OracleOCIConnectionPool class, which extends the OracleDataSource class, is used to create OCI connection pools. From an OracleOCIConnectionPool class instance, you can obtain logical connection objects. These connection objects are of the OracleOCIConnection class type. This class implements the OracleConnection interface. The Statement objects you create from the OracleOCIConnection class have the same fields and methods as OracleStatement objects you create from OracleConnection instances.

The following code shows header information for the OracleOCIConnectionPool class:

/*
* @param us ConnectionPool user-id.
* @param p ConnectionPool password
* @param name logical name of the pool. This needs to be one in the
* tnsnames.ora configuration file.
@param config (optional) Properties of the pool, if the default does not
suffice. Default connection configuration is min =1, max=1,
incr=0
Please refer setPoolConfig for property names.
Since this is optional, pass null if the default configuration
suffices.
* @return
*
* Notes: Choose a userid and password that can act as proxy for the users
* in the getProxyConnection() method.
If config is null, then the following default values will take
effect
CONNPOOL_MIN_LIMIT = 1
CONNPOOL_MAX_LIMIT = 1
CONNPOOL_INCREMENT = 0
*/
public synchronized OracleOCIConnectionPool
(String user, String password, String name, Properties config)
throws SQLException
/*
* This will use the user-id, password and connection pool name values set
LATER using the methods setUser, setPassword, setConnectionPoolName.
* @return
*
* Notes:
No OracleOCIConnection objects can be created on
this class unless the methods setUser, setPassword, setPoolConfig
are invoked.
When invoking the setUser, setPassword later, choose a userid and
password that can act as proxy for the users
* in the getProxyConnection() method.
*/
public synchronized OracleOCIConnectionPool ()
throws SQLException

Importing the oracle.jdbc.pool and oracle.jdbc.oci Packages

Before you create an OCI connection pool, import the following to have Oracle OCI connection pooling functionality:

import oracle.jdbc.pool.*;
import oracle.jdbc.oci.*;

The oracle.jdbc.pool.* package contains the OracleDataSource, OracleConnectionPoolDataSource, and OracleOCIConnectionPool classes, in addition to classes for connection caching and event-handling. The oracle.jdbc.oci.* package contains the OracleOCIConnection class and the OracleOCIFailover interface.

Creating an OCI Connection Pool

The following code show how you create an instance of the OracleOCIConnectionPool class called cpool:

poolConfig is a set of properties which specify the connection pool. If poolConfig is null, then the default values are used. For example, consider the following:

poolConfig.put (OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "4");

poolConfig.put (OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "10");

poolConfig.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, "2");

As an alternative to the above constructor call, you can create an instance of the OracleOCIConnectionPool class using individual methods to specify the user, password, and connection string.

OracleOCIConnectionPool cpool = new OracleOCIConnectionPool ( );
cpool.setUser("SCOTT");
cpool.setPassword("TIGER");
cpool.setURL("jdbc:oracle:oci:@(description=(address=(host=
myhost)(protocol=tcp)(port=1521))(connect_data=(INSTANCE_NAME=orcl)))");
cpool.setPoolConfig(poolConfig); // In case you want to specify a different
// configuration other than the default
// values.

Setting the OCI Connection Pool Parameters

The connection pool configuration is determined by the following OracleOCIConnectionPool class attributes:

CONNPOOL_MIN_LIMIT : Specifies the minimum number of physical connections that can be maintained by the pool.

CONNPOOL_MAX_LIMIT : Specifies the maximum number of physical connections that can be maintained by the pool.

CONNPOOL_INCREMENT : Specifies the incremental number of physical connections to be opened when all the existing ones are busy and a call needs one more connection; the increment is done only when the total number of open physical connections is less than the maximum number that can be opened in that pool.

CONNPOOL_TIMEOUT : Specifies how much time must pass before an idle physical connection is disconnected; this does not affect a logical connection.

CONNPOOL_NOWAIT : When enabled, this attributes specifies that an error is returned if a call needs a physical connection while the maximum number of connections in the pool are busy; if disabled, a call waits until a connection is available. Once this attribute is set to "true", it cannot be reset to "false".

You can configure all of these attributes dynamically. Therefore, an application has the flexibility of reading the current load (number of open connections and number of busy connections) and adjusting these attributes appropriately, using the setPoolConfig() method.

Note:

The default values for the CONNPOOL_MIN_LIMIT, CONNPOOL_MAX_LIMIT, and CONNPOOL_INCREMENT parameters are 1, 1, and 0, respectively.

The setPoolConfig() method is used to configure OCI connection pool properties. The following is a typical example of how the OracleOCIConnectionPool class attributes can be set:

CONNPOOL_MIN_LIMIT, CONNPOOL_MAX_LIMIT, and CONNPOOL_INCREMENT are mandatory.

CONNPOOL_MIN_LIMIT must be a value greater than zero.

CONNPOOL_MAX_LIMIT must be a value greater than or equal to CONNPOOL_MIN_LIMIT plus CONNPOOL_INCREMENT.

CONNPOOL_INCREMENT must be a value greater than or equal to zero

CONNPOOL_TIMEOUT must be a value greater than zero.

CONNPOOL_NOWAIT must be "true" or "false" (case insensitive).

Checking the OCI Connection Pool Status

To check the status of the connection pool, use the following methods from the OracleOCIConnectionPool class:

int getMinLimit() : Retrieves the minimum number of physical connections that can be maintained by the pool.

int getMaxLimit() : Retrieves the maximum number of physical connections that can be maintained by the pool.

int getConnectionIncrement() : Retrieves the incremental number of physical connections to be opened when all the existing ones are busy and a call needs a connection.

int getTimeout() : Retrieves the specified time (in seconds) that a physical connection in a pool can remain idle before it is disconnected; the age of a connection is based on the Least Recently Used (LRU) scheme.

String getNoWait() : Retrieves whether the NOWAIT property is enabled. It returns a string of "true" or "false".

int getPoolSize() : Retrieves the number of physical connections that are open. This should be used only as estimate and for statistical analysis.

int getActiveSize() : Retrieves the number of physical connections that are open and busy. This should be used only as estimate and for statistical analysis.

boolean isPoolCreated() : Retrieves whether the pool has been created. The pool is actually created when OracleOCIConnection (user, password, url, poolConfig) is called or when setUser, setPassword, and setURL has been done after calling OracleOCIConnection().

Connecting to an OCI Connection Pool

The OracleOCIConnectionPool class, through a getConnection() method call, creates an instance of the OracleOCIConnection class. This instance represents a connection. See "Data Sources" for database connection descriptions that apply to all JDBC drivers.

Since the OracleOCIConnection class extends OracleConnection class, it has the funtionality of this class too. Close the OracleOCIConnection objects once the user session is over, otherwise, they are closed when the pool instance is closed.

There are two ways of calling getConnection():

OracleConnection getConnection(String user, String password) : Get a logical connection identified with the specified user and password, which can be different from that used for pool creation.

OracleConnection getConnection() : If you do not supply the user name and password, then the default user name and password used for the creation of the connection pool are used while creating the connection objects.

As an enhancement to OracleConnection, the following new method is added into OracleOCIConnection as a way to change password for the user:

Statement Handling and Caching

Statement caching is supported with OracleOCIConnectionPool. The caching improves performance by not having to open, parse and close cursors. When OracleOCIConnection.prepareStatement ("SQL query") is done, the statement cache is searched for a statement that matches the SQL query. If a match is found, we can reuse the Statement object instead of incurring the cost of creating another Statement object. The cache size can be dynamically increased or decreased. The default cache size is zero.

Note:

The OracleStatement object created from OracleOCIConnection has the same behavior as one that is created from OracleConnection.

Statement caching in OracleOCIConnectionPool is a little different from the standard functionality in OracleConnectionCacheImpl. The setStmtCacheSize() method sets the statement cache sizes of all the OracleOCIConnection objects retrieved from this pool. But unlike logical (OracleConnection) connection objects obtained from OracleConnectionCacheImpl, the individual cache sizes of the logical (OracleOCIConnection) connection objects can also be changed if desired. (The default cache size is zero.)

The following code shows the signatures of the getConnection() method:

Types of Statement Caching used with the OCI Connection Pool

There are two forms of statement caching: implicit and explicit. (See Chapter 14, "Statement Caching" for a complete description of implicit and explicit statement caching.) Both forms of statement caching use the setStmtCacheSize() method. Explicit statement caching requires the JDBC application to provide a key while opening and closing Statement objects. Implicit statement caching does not require the JDBC application to provide the key; the caching is transparent to the application. Also in explicit statement caching, the fetch state of the result set is not cleared. So after doing a Statement.close(key="abc"), Connection.preparedStatement(key="abc") will return the Statement object and fetches will continue with the fetch state when the previous Statement.close(key="abc") is done.

For implicit statement caching, the fetch state is cleared and the cursor is re-executed, but the cursor meta data is cached to improve performance. In some cases, the client may also need to clear the meta data (through the clearMetaData parameter).

JNDI and the OCI Connection Pool

The Java Naming and Directory Interface (JNDI) feature makes persistent the properties of Java object so these properties can be used to construct a new instance of the object (such as cloning the object). The benefit is that the old object can be freed, and at a later time a new object with exactly the same properties can be created. The InitialContext.bind() method makes persistent the properties, either on file or in a database, while the InitialContext.lookup() method retrieves the properties from the persistent store and creates a new object with these properties.

OracleOCIConnectionPool objects can be bound and looked up using the JNDI feature. No new interface calls in OracleOCIConnectionPool are necessary.

Middle-Tier Authentication Through Proxy Connections

Middle-tier authentication allows one JDBC connection (session) to act as proxy for other JDBC connections. A proxy session could be required for one of the following:

If the middle tier does not know the password of the proxy user. This is done by first authenticating using:

alter user jeff grant connect through scott with roles role1, role2;

Then the method allows you to connect as "jeff" using the already authenticated credentials of "scott". It is sometimes a security concern for the middle tier to know the passwords of all the database users. Though the created session will behave much like "jeff" was connected normally (using "jeff"/"jeff-password"), "jeff" will not have to divulge its password to the middle tier. The schema which this proxy session has access to is schema of "jeff" plus what is indicated in the list of roles. Therefore, if "scott" wants "jeff" to access its table EMP, the following code can be used:

create role role1;
grant select on EMP to role1;

The role clause can also be thought as limiting "jeff's" access to only those database objects of "scott" mentioned in the list of the roles. The list of roles can be empty.

For accounting purposes. The transactions made via proxy sessions can be better accounted by proxying the user ("jeff"), under different users such as "scott", "scott2" assuming "scott"and "scott2" are authenticated. Transactions made under these different proxy sessions by "jeff" can be logged separately.

There are three ways to create proxy sessions in the OCI driver. Roles can be associated with any of the following options:

USER NAME : This is done by supplying the user name and/or the password. The reason why the "password" option exists is so that database operations made by the user ("jeff"), can be accounted. The SQL clause is:

CERTIFICATE : This is a more encrypted way of passing the credentials of the user (to be proxied) to the database. The certificate contains the distinguished encoded name. One way of generating it is by creating a wallet (using "runutl mkwallet"), then decoding the wallet to get the certificate. It is then necessary to authenticate as:

The following code shows signatures of the getProxyConnection() method with information about the proxy type process:

/*
* For creating a proxy connection. All macros are defined
* in OracleOCIConnectionPool.java
*
* @param proxyType Can be one of following types
PROXYTYPE_USER_NAME
- This will be the normal mode of specifying the user
name in proxyUser as in Oracle8i
PROXYTYPE_DISTINGUISHED_NAME
- This will specify the distinguished name of the user
in proxyUser
PROXYTYPE_CERTIFICATE
- This will specify the proxy certificate
The Properties (ie prop) should be set as follows.
If PROXYTYPE_USER_NAME
PROXY_USER_NAME and/or PROXY_USER_PASSWORD depending
on how the connection-pool owner was authenticated
to act as proxy for this proxy user
PROXY_USER_NAME (String) = user to be proxied for
PROXY_PASSWORD (String) = password of the user to be proxied for
else if PROXYTYPE_DISTINGUISHED_NAME
PROXY_DISTINGUISHED_NAME (String) = (global) distinguished name of the
user to be proxied for
else if PROXYTYPE_CERTIFICATE (byte[])
PROXY_CERTIFICATE = certficate containing the encoded
distinguished name
PROXY_ROLES (String[]) Set of roles which this proxy connection can use.
Roles can be null, and can be associated
with any of the above proxy methods.
*
* @return connection object
*
* Notes: The user and password used to create OracleOCIConnectionPool()
* must be allowed to act as proxy for user 'us'.
*/
public synchronized OracleConnection getProxyConnection(String proxyType,
Properties prop)
throws SQLException

OCI Driver Transparent Application Failover

Transparent Application Failover (TAF) or simply Application Failover is a feature of the OCI driver. It enables you to automatically reconnect to a database if the database instance to which the connection is made goes down. In this case, the active transactions roll back. (A transaction rollback restores the last committed transaction.) The new database connection, though created by a different node, is identical to the original. This is true regardless of how the connection was lost.

TAF is always active and does not have to be set.

For additional details regarding OCI and TAF, see the Programmer's Guide to the Oracle Call Interface.

Failover Type Events

The following are possible failover events in the OracleOCIFailover interface:

FO_SESSION : Is equivalent to FAILOVER_MODE=SESSION in the tnsnames.ora file CONNECT_DATA flags. This means that only the user session is re-authenticated on the server-side while open cursors in the OCI application need to be re-executed.

FO_SELECT : Is equivalent to FAILOVER_MODE=SELECT in tnsnames.ora file CONNECT_DATA flags. This means that not only the user session is re-authenticated on the server-side, but open cursors in the OCI can continue fetching. This implies that the client-side logic maintains fetch-state of each open cursor.

FO_NONE : Is equivalent to FAILOVER_MODE=NONE in the tnsnames.ora file CONNECT_DATA flags. This is the default, in which no failover functionality is used. This can also be explicitly specified to prevent failover from happening. Additionally, FO_TYPE_UNKNOWN implies that a bad failover type was returned from the OCI driver.

FO_BEGIN : Indicates that failover has detected a lost connection and failover is starting.

FO_END : Indicates successful completion of failover.

FO_ABORT : Indicates that failover was unsuccessful and there is no option of retrying.

FO_REAUTH : indicates that a user handle has been re-authenticated.

FO_ERROR : indicates that failover was temporarily un-successful, but it gives the application the opportunity to handle the error and retry failover. The usual method of error handling is to issue the sleep() method and retry by returning the value FO_RETRY.

FO_RETRY : See above.

FO_EVENT_UNKNOWN : A bad failover event.

TAF Callbacks

TAF callbacks are used in the event of the failure of one database connection, and failover to another database connection. TAF callbacks are callbacks that are registered in case of failover. The callback is called during the failover to notify the JDBC application of events generated. The application also has some control of failover.

Note:

The callback setting is optional.

Java TAF Callback Interface

The OracleOCIFailover interface includes the callbackFn() method, supporting the following types and events:

Handling the FO_ERROR Event

In case of an error while failing-over to a new connection, the JDBC application is able to retry failover. Typically, the application sleeps for a while and then it retries, either indefinitely or for a limited amount of time, by having the callback return FO_RETRY.

Handling the FO_ABORT Event

Callback registered should return the FO_ABORT event if the FO_ERROR event is passed to it.

OCI HeteroRM XA

Unlike the regular JDBC XA feature which works only with Oracle8i 8.1.6 and later databases, JDBC HeteroRM XA also allows you to do XA operations in Oracle8i releases prior to 8.1.6. In general, the HeteroRM XA is recommended for use whenever possible.

Configuration and Installation

The Solaris shared libraries, libheteroxa9.so and libheteroxa9_g.so, enable the HeteroRM XA feature to support access to Oracle8i releases prior to release 8.1.6. The NT version of these libraries are heteroxa9.dll and heteroxa9_g.dll. In order for the HeteroRM XA feature to work properly, these libraries need to be installed and available in either the Solaris search path or the NT DLL path, depending on your system.

Note:

Libraries with the _g suffix are debug libraries.

Exception Handling

When using the HeteroRM XA feature in distributed transactions, it is recommended that the application simply check for XAException or SQLException, rather than OracleXAException or OracleSQLException.

The mapping from SQL error codes to standard XA error codes does not apply to the HeteroRM XA feature.

HeteroRM XA Code Example

The following portion of code shows how to enable the HeteroRM XA feature.

// Create a XADataSource instance
OracleXADataSource oxds = new OracleXADataSource();
oxds.setURL(url);
// Set the nativeXA property to use HeteroRM XA feature
oxds.setNativeXA(true);
// Set the tnsEntry property to an older DB as required
oxds.setTNSEntryName("ora805");

Table 17-2 describes the arguments of the setPlsqlIndexTable() method.

Table 17-2 Arguments of the setPlsqlIndexTable () Method

Argument

Description

int paramIndex

This argument indicates the parameter position within the statement.

Object arrayData

This argument is an array of values to be bound to the PL/SQL index-by table parameter. The value is of type java.lang.Object, and the value can be a Java primitive type array such as int[] or a Java object array such as BigDecimal[].

int maxLen

This argument specifies the maximum table length of the index-by table bind value which defines the maximum possible curLen for batch updates. For standalone binds, maxLen should use the same value as curLen. This argument is required.

int curLen

This argument specifies the actual size of the index-by table bind value in arrayData. If the curLen value is smaller than the size of arrayData, only the curLen number of table elements is passed to the database. If the curLen value is larger than the size of arrayData, the entire arrayData is sent to the database.

int elemSqlType

This argument specifies the index-by table element type based on the values defined in the OracleTypes class.

int elemMaxLen

This argument specifies the index-table element maximum length in case the element type is CHAR, VARCHAR, or RAW. This value is ignored for other types.

The following code example uses the setPlsqlIndexTable() method to bind an index-by table as an IN parameter:

Accessing the OUT Parameter Values

To access the OUT bind value, the OracleCallableStatement class defines multiple methods that return the index-by table values in different mapping styles. There are three mapping choices available in JDBC drivers:

Table 17-4 Argument of the getPlsqlIndexTable () Method

Argument

Description

int paramIndex

This argument indicates the parameter position within the statement.

The return value is a Java array. The elements of this array are of the default Java type corresponding to the SQL type of the elements. For example, for an index-by table with elements of NUMERIC typecode, the element values are mapped to BigDecimal by the Oracle JDBC driver, and the getPlsqlIndexTable() method returns a BigDecimal[] array. For a JDBC application, you must cast the return value to a BigDecimal[] array to access the table element values. (See "Datatype Mappings" for a list of default mappings.)

Oracle Mappings

Table 17-5 describes the argument of the getOraclePlsqlIndexTable() method.

Table 17-5 Argument of the getOraclePlsqlIndexTable () Method

Argument

Description

int paramIndex

This argument indicates the parameter position within the statement.

The return value is an oracle.sql.Datum array and the elements in the Datum array will be the default Datum type corresponding to the SQL type of the element. For example, the element values of an index-by table of numeric elements are mapped to the oracle.sql.NUMBER type in Oracle mapping, and the getOraclePlsqlIndexTable() method returns an oracle.sql.Datum array that contains oracle.sql.NUMBER elements.

The following code example uses the getOraclePlsqlIndexTable() method to access the elements of a PL/SQL index-by table OUT parameter, using Oracle mapping. (The code for registration is omitted.)

Table 17-6 describes the arguments of the getPlsqlIndexTable() method.

Table 17-6 Arguments of the getPlsqlIndexTable () Method

Argument

Description

int paramIndex

This argument indicates the parameter position within the statement.

Class primitiveType

This argument specifies a Java primitive type to which the index-by table elements are to be converted. For example, if you specify java.lang.Integer.TYPE, the return value is an int array.

The following are the possible values of this parameter:

java.lang.Integer.TYPE

java.lang.Long.TYPE

java.lang.Float.TYPE

java.lang.Double.TYPE

java.lang.Short.TYPE

The following code example uses the getPlsqlIndexTable() method to access the elements of a PL/SQL index-by table of numbers. In the example, the second parameter specifies java.lang.Integer.TYPE, so the return value of the getPlsqlIndexTable() method is an int array.