Labelling Connections In Oracle UCP

Oracle’s Universal Connection Pool (UCP) provides a feature called connection labeling where an application could attach arbitrary name/value pairs to a connection.By associating particular labels with particular connection states, an application can retrieve an already initialized connection from the pool and avoid the time and cost of re-initialization. The initialization varies and could include simple state re-initialization that requires method calls within the application code or database operations that require round trips over the network.

Connection labeling is application-driven and requires the application to implement an callback interface called oracle.ucp.ConnectionLabelingCallback. The callback determines whether or not a connection with a requested label already exists. If no connections exist, the interface allows current connections to be configured as required.

Creating a Labeling Callback

The callback interface includes two methods –

public int cost(Properties requestedLabels, Properties currentLabels);

public boolean configure(Properties requestedLabels, Connection conn);

cost – This method projects the cost of configuring connections considering label-matching differences. If cost returned is 0, the connection is an exact match. If the pool has iterated through all available connections and the lowest cost of a connection is Integer.MAX_VALUE (2147483647 by default), then no connection in the pool is able to satisfy the connection request. The pool creates and returns a new connection.

configure – This method is called by the connection pool on the selected connection before returning it to the application.

Below is a sample implementation of the callback


public class UCPConnectionLabellingCallback implements ConnectionLabelingCallback {

    private Logger logger = LoggerFactory.getLogger(UCPConnectionLabellingCallback.class);

    public int cost(Properties requestedLabels, Properties currentLabels) {
        if(requestedLabels.equals(currentLabels)) {
            System.out.println("Found a connection with the requested label");
            return 0;
        }
        System.out.println("No connection found with requested labels");
        return Integer.MAX_VALUE;
    }

    public boolean configure(Properties requestedLabels, Object connection) {
        System.out.println("Returning true always for requestedLabels");
        return true;
    }
}

The callback instance needs to be registered with the connection pool instance as shown below


PoolDataSourceImpl pds = new PoolDataSourceImpl();
.....
.....
pds.registerConnectionLabelingCallback(new UCPConnectionLabellingCallback());

ClassCastException

Note that in the above UCPConnectionLabellingCallback class the implementation of the configure method is purposefully kept blank. Ideally we should be applying the requested labels on the passed connection instance in the configure() method but using ucp 11.2.0.1.0 and ojdbc6-11.2.0.1.0 jar files, we get an class cast exception as below when the driver type used is “oci”


Exception in thread "main" java.lang.ClassCastException: oracle.jdbc.driver.T2CConnection cannot be cast to oracle.ucp.jdbc.LabelableConnection
	at ucp.UCPConnectionLabellingCallback.configure(UCPConnectionLabellingCallback.java:36)
	at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.getValidAvailableConnectionHelper(UniversalConnectionPoolImpl.java:1864)
	at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.getValidAvailableConnectionInternal(UniversalConnectionPoolImpl.java:1920)
	at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.access$2600(UniversalConnectionPoolImpl.java:1421)
	at oracle.ucp.common.UniversalConnectionPoolImpl.getValidAvailableConnection(UniversalConnectionPoolImpl.java:1333)
	at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:191)
	at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:130)
	at oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:119)
	at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:656)

If the driver type used is “thin”, the same class cast exception is thrown but with a different connection class name.

A possible reason for the above error is that ucp implementation changed after the java docs and the 11.2 jdbc developer guide were documented and now the connection object passed to the configure method is not an instance of LabelableConnection. This is according to the api though which states “A Universal Connection Pool Adapter passes in a resource-specific connection appropriate for the Adapter type. For example, a JDBC Adapter could pass in a java.sql.Connection.”

One resolution to the above error is to not have any code to apply connection labels in the configure() method (and hence no code which would cast the connection object to LabelableConnection) and implement this code after the connection is retrieved from the pooling data source.

Hence the code to retrieve a connection would look like

public void getConnection() {
    Connection conn2 = pds.getConnection(label);
    applyConnectionLabels(label, (LabelableConnection) conn2);
}

public void applyConnectionLabel(Properties reqLabels, Object conn) {
    try {
      String isoStr = (String) reqLabels.get("TRANSACTION_ISOLATION");
      ((Connection)conn).setTransactionIsolation(Integer.valueOf(isoStr));
      LabelableConnection lconn = (LabelableConnection) conn;

      // Find the unmatched labels on this connection
      Properties unmatchedLabels = lconn.getUnmatchedConnectionLabels(reqLabels);

      // Apply each label <key,value> in unmatchedLabels to conn
      for (Map.Entry<Object, Object> label : unmatchedLabels.entrySet()) {
        String key = (String) label.getKey();
        String value = (String) label.getValue();
        lconn.applyConnectionLabel(key, value);
      }
    } catch (SQLException e) {
      return false;
    }
    return true;
}

Note that the getUnmatchedConnectionLabels() and applyConnectionLabel() methods throw an SQLException which could give an indication that some database operation might be done which should not be the case since it would defeat the purpose of labelling connections (which in most of the cases is to save network round trips). As the java docs indicate, SQLException is thrown by the above methods when the connection is closed or when the key passed is either null or empty string.

Advertisements

Posted on October 18, 2011, in oracle-jdbc and tagged , . Bookmark the permalink. 5 Comments.

  1. Hi Amit,

    I have been trying to use UCP for RAC awareness and had trouble in using it. Since I did not find any other post related am using this post, sorry!

    The trouble is every time the getConnection() of UniversalConnectionPool is called it starts timer threads and UCP-worker-threads. How can I control these worker-threads as they are peeking out if my connection is waiting. (My test code has a sleep() for 3 min after the getConnection() and before the query execution).

    Can you point me to some pointers? I could figure out that the UCP-worker-threads being created every 30sec. Why does it create the worker-threads every time?

    Vas

    • Yes. I have observed UCP spawning worker-threads for its connection pool management but it uses a fixed thread pool of 24 for this. Have a look UCPTaskManagerImpl class for this. Since it uses a fixed thread pool I do not think it should much of an issue. It would be interesting to know more about the magic number 24.

      Are you facing any issue because of these worker threads?

  2. Thanks a lot Amit, for your speedy response 🙂

    Yes, I have been facing OutOfMemory Exceptions when ever I try to simulate our prod environment in my Junit, which made me think, if say, some of my connections taking more time, then there is every possibility of Threads being PEEKED out bringing prod env down.

    I just had a Junit written for 10 connectionPool min size and made them wait for 3min. And saw the threads to 240 something (24*10), which worries me a lot to have this on production.

    We have some 32 DB instance pools to maintain with on an average of 45 MaxPool size for each.

    • 32 DB instance pools sound a bit more. Do you mean your application server connects to 32 database servers? If the connection pools are connecting to the same database server but to different schemas you could instead use same connection pools and at run time get the connection for a specific schema by calling datasource.getConnection(username, password) method.

      Does that help?

      • Yes, they are just different schemas on the same server.
        So your suggestion should help us.

        Thank a lot for your valuable suggestion, it did help us to rethink our connection pooling.

        Thanks,
        Vas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: