X

Learn Tips and Best Practices from the Oracle JDBC Development

  • March 9, 2017

UCP Multi-Tenant Shared Pool Configuration

Oracle Universal Connection Pool (UCP) adds a new capability called as "Shared Pool" in the Oracle Database 12c Release 2 (12.2). 'Shared Pool' is particularly targeted for customers leveraging multi-tenant architecture that consists of multiple pluggable databases (PDBs)/tenants inside a single container database (CDB). Each PDB is an independent datasource configured using a connection string that contains its own database service name, username and password. 'Shared Pool' enables sharing a common pool of connections across multiple PDBs/datasources. The benefits of a 'Shared Pool' are highlighted below.

Without Shared Pool With Shared Pool
  • Using an individual pool per tenant/PDB would result in wastage of expensive resources
  • Causes scalability, manageability and diagnose-ability issues when more tenants are added.
  • 'Shared Pool' consolidates many datasources/PDBs by sharing connections across PDBs.
  • Shared Pool decreases the number of database connections thus improving usage of resources, scalability, manageability and diagnose-ability
  • Uneven distribution of load, as some PDBs are heavily loaded and others are lightly loaded
  • Causes an inefficient usage of resources as idle connections cannot be moved between PDBs
  • 'Shared Pool' adds flexibility and distributes the connections to datasources based on their work load.
  • 'Shared Pool' also allows setting a maximum number of connections per service that prevents connection starvation and ensures a fair use of the shared pool
  • A connection assigned to a tenant cannot be used for any other tenant
  • Repurposing/reusing connections across PDBs is impossible when an individual pool per tenant is used
  • For Public or Private Cloud multi-tenant deployments, a 'Shared Pool' with a common user is capable of repurposing connections from other tenants
  • This new capability of switching the service between tenants leads to better resource sharing and improved density

 

 

Shared Pool Architecture:

Pre-requisites for using a 'Shared Pool':

  • Must specify the Shared Pool configurations through a UCP XML config file. Each datasource using a Shared Pool must be present in UCP XML config file.
  • The services configured for tenants must be an application service. Also, services must be homogeneous (should have similar properties wrt AC, TG, DRCP etc.). Note that repurposing of connections is not supported with admin services or default PDB services.
  • Repurposing of connections in the shared pool works ONLY when it is configured with a common user (user starting with C##) and satisfies the following requirements.
    • Common user should have the privileges to create session, alter session and set container.
    • Common user should have execute permission on 'dbms_service_prvt' package.
    • Any specific roles or password for common user should also be specified in the UCP XML config file.

 

Sample Code to demonstrate "Shared Pool" feature:

  • In the sample code two datasources "pds1" and "pds2" are accessing shared pool - "pool1"
  • Pool is initialized with root service connections and each datasource requests connections for its pdb specific service using getConnection() method.
  • On receiving a connection borrow request from a datasource
    • the pool looks for an available connection for that particular pdb service and returns it to datasource
    • If there are no available connections for the requested pdb service, the pool tries to repurpose existing available connections of other pdb services.
    • If there are no available connections in the pool for any service then the pool creates a new connection for the service requested by the datasource.
  • Using UCP Statistics we can print the count of number of connections repurposed by the pool.
  • To validate/test the connections borrowed from the pool we are executing a simple query on the connection which prints the connection attributes like - connection username, service and DB name.
  • In the example we have also configured "max-connections-per-service" attribute on pool which indicates that the pool cannot repurpose existing connections / can not create new connections for a service if max connections per service limit is reached
    for that particular service. It gives a fair chance to all the services configured in the shared pool.

 

Pre-requisites for running the sample code:

  • Oracle JDBC driver 12.2 (ojdbc8.jar)
  • UCP 12.2 (ucp.jar)
  • Oracle Database 12c Release 2 (12.2)
  • JDK8
The setup required to run Shared Pool sample code:
  • An environment with one CDB and two PDBs. Following example uses a CDB service name of 'cdb_root_app_service_name' and PDB service names of 'pdb1_app_service_name' and 'pdb2_app_service_name'.
  • UCP Config XML (SharedPool_config.xml) that contains all details about datasources using the shared pool. Refer to \oracle\ucp\xml\configuration.xsd present in ucp.jar.
  • Specify the location of the UCP Config XML file through a system property "oracle.ucp.jdbc.xmlConfigFile".
Below is the sample UCP Config XML file used in sample code.

SharedPool_config.xml



	
		
		
		
		
		
		
		
		
	

Refer to the code sample for configuring a Shared Pool using above UCP XML configuration file.

SharedPoolConfig.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.ucp.UniversalConnectionPool;
import oracle.ucp.UniversalConnectionPoolException;
import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.oracle.OracleJDBCConnectionPoolStatistics;

public class SharedPoolConfig {
  static String xmlFileURI = "file:/test/ucp/config/SharedPool_config.xml";

  public static void main(String[] args) {
    try {
      // Java system property to specify the location of xml configuration file
      System.setProperty("oracle.ucp.jdbc.xmlConfigFile", xmlFileURI);
      // Get the datasource instance, named as "pds1" in xml config file
      PoolDataSource pds1 = PoolDataSourceFactory.getPoolDataSource("pds1");
      Connection pds1Conn = pds1.getConnection();
      // Run a SQL query to test the connection
      testConnection(pds1Conn);
      pds1Conn.close();
      // Get the datasource instance, named as "pds2" in xml config file
      PoolDataSource pds2 = PoolDataSourceFactory.getPoolDataSource("pds2");
      Connection pds2Conn = pds2.getConnection();
      testConnection(pds2Conn);
      pds2Conn.close();
      final int COUNT = 5;
      Connection conn[] = new Connection[COUNT];
      // Borrow 5 connections of pdb1 service using datasource pds1
      for (int i = 0; i < COUNT; i++) {
        conn[i] = pds1.getConnection();
      }
      // Return the connections to pool
      for (int i = 0; i < COUNT; i++) {
        if (conn[i] != null)
          conn[i].close();
      }
      // Borrow 5 connections of pdb2 service using datasource pds2
      for (int i = 0; i < COUNT; i++) {
        conn[i] = pds2.getConnection();
      }
      // Return the connections to pool
      for (int i = 0; i < COUNT; i++) {
        if (conn[i] != null)
          conn[i].close();
      }
      // Print UCP pool statistics for pool1
      final String poolName = "pool1";
      UniversalConnectionPool pool = UniversalConnectionPoolManagerImpl
          .getUniversalConnectionPoolManager().getConnectionPool(poolName);
      OracleJDBCConnectionPoolStatistics stats = (OracleJDBCConnectionPoolStatistics) pool
          .getStatistics();
      System.out.println("Pool : " + poolName
          + " --> Connection # Repurpose Count = "
          + stats.getConnectionRepurposeCount());
      System.out.println("Available Connection Count = "
          + stats.getAvailableConnectionsCount());
      System.out.println("Borrowed Connection Count = "
          + stats.getBorrowedConnectionsCount());
    } catch (SQLException sqlexc) {
      sqlexc.printStackTrace();
    } catch (UniversalConnectionPoolException ucpEx) {
      ucpEx.printStackTrace();
    }
  }

  static void testConnection(Connection conn) throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      String query = "select sys_context('userenv', 'instance_name'),"
          + "sys_context('userenv', 'server_host'),"
          + "sys_context('userenv', 'service_name'),"
          + "sys_context('userenv', 'db_unique_name')" + ",user" + " from dual";
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        String serviceName = rs.getString(3);
        String dbName = rs.getString(4);
        String userName = rs.getString(5);
        System.out.println("Connection Db name from sys context=" + dbName);
        System.out.println("Connection Svc name from sys context="
            + serviceName);
        System.out.println("Connection user Name : " + userName);
      }
    } catch (SQLException sqlexc) {
      throw sqlexc;
    } finally {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
    }
  }
}
Also see blog which explains how we can use UCP Multi-Tenant Shared Pool feature with Tomcat.

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.