Learn Tips and Best Practices from the Oracle JDBC Development

Mid-tier Routing using Oracle Universal Connection Pool (UCP)

Oracle Universal Connection Pool (UCP) adds a new feature called "Mid-tier Routing" in the Oracle Database 18c release. The feature is targeted for customers using Oracle Sharded Database.
Currently mid-tier connection pools route database requests to specific shards. This leads to a situation where each mid-tier connection pool establishes connections to each shard. This will create too many connections to the Database. The problem can be solved by affinitizing mid-tiers with shards.

In such scenario it is desirable to have dedicated mid-tier (web server/application server) for each data center or cloud, and to have client requests routed directly to the right mid-tier where the shard containing the client data (corresponding to the client shard key) resides.
A common term used for this kind of setup is “swim lanes”, where each lane is a dedicated stack, from web server to application server all the way to the Database.

UCP solves the above problem by providing mid-tier routing API which can be used to route the client requests to right mid-tier. The mid-tier API is exposed by UCP’s OracleShardRoutingCache class . An instance of this class represents UCP's internal shard routing cache which can be created by providing connection properties such as user, password and url.
The routing cache connects to the sharding catalog to retrieve the key to shard mapping topology and stores it in its cache.

The routing cache is used by mid-tier API getShardInfoForKey(shardingKey,superShardingKey) which  accepts sharding key as input and returns a set of ShardInfo instances mapped to the input sharding key. The ShardInfo instance encapsulates a unique shard name and priority of the shard. The application using the mid-tier API can map the returned unique shard name value to a mid-tier which has connections to a specific shard.

The routing cache is automatically refreshed/updated on chunk move/split by subscribing to respective ONS events.

Public APIs/Interfaces/classes for Mid-tier Routing:

 * This class extends the UCP's internal shard routing cache and makes the basic
 * routing cache feature available to the WLS /Mid tier routers or Load
 * Balancers.
public class OracleShardRoutingCache extends ShardRoutingCache {
   * Creates an instance of a Shard Routing cache that can be used by a mid-tier
   * that needs to do shard-based routing. Once this cache is created,
   * getShardInfoForKey can be used for every Sharding Key to know which shard
   * needs to be used.
   * @param dataSourceProps
   *          Required datasource properties to create a connection to shard
   *          catalog.
   * @throws UniversalConnectionPoolException
   * @see getShardInfoForKey
  public OracleShardRoutingCache(Properties dataSourceProps)
      throws UniversalConnectionPoolException {
   * Gets the information of each sharded database that maps to the sharding
   * keys.
   * @param key
   *          sharding key for which the Sharded Database information is
   *          requested
   * @param superKey
   *          super sharding key for which the Sharded Database information is
   *          requested
   * @return set of ShardInfo objects each of which contains information about
   *         the shard name and the priority of the shard for the corresponding
   *         shard keys.
  public Set<ShardInfo> getShardInfoForKey(OracleShardingKey key,
    OracleShardingKey superKey) {

 * When the routing cache is queried for shard information corresponding to
 * particular sharding keys, a set of objects of this type is returned. Each
 * such object encapsulates all the required information about one of the shards
 * that corresponds to the sharding keys.
public interface ShardInfo {

   * Returns the encapsulated shard name.
   * @return shard name
  String getName();

   * Returns the encapsulated shard's priority.
   * @return shard priority
  int getPriority();


Below is a simple code example which illustrates the usage of UCP’s mid-tier routing API.

import java.sql.SQLException;
import java.util.Properties;
import java.util.Random;
import java.util.Set;

import oracle.jdbc.OracleShardingKey;
import oracle.jdbc.OracleType;
import oracle.ucp.UniversalConnectionPoolException;
import oracle.ucp.routing.ShardInfo;
import oracle.ucp.routing.oracle.OracleShardRoutingCache;

 * The code example illustrates the usage of UCP's mid-tier routing feature. The
 * API accepts sharding key as input and returns the set of ShardInfo instances
 * mapped to the sharding key. The ShardInfo instance encapsulates unique shard
 * name and priority. The unique shard name then can be mapped to a specific
 * mid-tier server which connects to a specific shard.
public class MidtierShardingExample {

  private static String user = "testuser1";
  private static String password = "testuser1";
  // catalog DB URL
  private static String url = "jdbc:oracle:thin:@//hostName:1521/catalogServiceName";
  private static String region = "regionName";

  public static void main(String args[]) throws Exception {

  static void testMidTierRouting() throws UniversalConnectionPoolException,
      SQLException {

    Properties dbConnectProperties = new Properties();
    dbConnectProperties.setProperty(OracleShardRoutingCache.USER, user);
    dbConnectProperties.setProperty(OracleShardRoutingCache.PASSWORD, password);
    // Mid-tier routing API accepts catalog DB URL
    dbConnectProperties.setProperty(OracleShardRoutingCache.URL, url);
    // Region is required to get the ONS config
    dbConnectProperties.setProperty(OracleShardRoutingCache.REGION, region);

    OracleShardRoutingCache routingCache = new OracleShardRoutingCache(

    final int COUNT = 10;
    Random random = new Random();

    for (int i = 0; i < COUNT; i++) {
      int key = random.nextInt();
      OracleShardingKey shardKey = routingCache.getShardingKeyBuilder()
          .subkey(key, OracleType.NUMBER).build();
      OracleShardingKey superShardKey = null;

      Set<ShardInfo> shardInfoSet = routingCache.getShardInfoForKey(shardKey,

      for (ShardInfo shardInfo : shardInfoSet) {
        System.out.println("Sharding Key=" + key + " Shard Name="
            + shardInfo.getName() + " Priority=" + shardInfo.getPriority());


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.