AWS Database Blog

Implement Oracle Database Resident Connection Pool with Amazon RDS for Oracle

This post describes how to configure Oracle Database Resident Connection Pool (DRCP) in an Amazon Relational Database Service (Amazon RDS) for Oracle environment. You can use DRCP with application servers that can’t do middle-tier connection pooling. You can also use DRCP to manage hundreds or thousands of database connections from clients spread across multiple application servers.

Solution overview

DRCP is useful with application servers that can perform middle-tier connection pooling, if multiple application tiers are deployed. This is explained in the following paragraphs.

The following diagram illustrates a single application tier with a middle-tier connection pool size of 100. Although 1,500 end-users are connected to the application tier, the application connection pool maintains 100 connections to the database server.

The following diagram illustrates a multi-application tier environment, each with a middle-tier connection pool size of 100. Because there are 20 application tiers, each with a minimum connection pool size of 100, the database server has 2,000 (20 * 100) corresponding server processes running. Typically, an average of only 10% of application tier connection pool sessions and their associated server processes are active at any given time. So, out of the 2,000 server processes, only 200 server processes are active at any given time. This leads to over 1,800 unused server processes on the database server. These unused processes lead to wasted resources, such as memory and threads, that could otherwise be allocated to database processing.

The preceding architecture doesn’t have DRCP enabled. Therefore, the RDS for Oracle instance has 2,000 database connections. With DRCP enabled (as in the following diagram), you can set up connection pooling at the RDS for Oracle instance level and limit the database connections. Because we calculated a maximum of 200 database connections (2,000 * 10%), we set the DRCP connection pool maximum size to 200. The 2,000 sessions from the application tiers connect to the RDS for Oracle instance; however, the 2,000 database sessions share 200 database connections.

DRCP significantly lowers memory consumption on the database server because of the reduced number of server processes running, thereby increasing the scalability of the database server.

You can monitor DRCP using the following database views: v$cpool_conn_info, v$cpool_stats and dba_cpool_info.

You should monitor DRCP waits to make sure the DRCP pool is healthy. The following SQL statement displays the DRCP status and waits for your connection pool:

select cpi.connection_pool, cpi.status, cs.num_waits from dba_cpool_info cpi, v$cpool_stats cs where cpi.connection_pool = cs.pool_name;

The number of waits represents the total number of client requests that had to wait due to non-availability of free pooled servers. If this value is large, you should consider increasing your DRCP connection pool maxsize parameter.

DRCP is supported with pluggable databases. DRCP in a multi-tenant container database (CDB) is configured and managed in the root container. You can configure, start, and stop the DRCP pool when you’re connected to the root container.

DRCP is supported with Oracle Standard Edition and Oracle Enterprise Edition databases starting on Oracle Database version 11.1.0.7.

Configure Oracle DRCP with Amazon RDS for Oracle

DRCP is installed on the database by default (including Amazon RDS for Oracle), but the database administrator (DBA) must start and configure it using the SYS.DBMS_CONNECTION_POOL package. A default pool named sys_default_connection_pool is created when the RDS for Oracle instance is created. As of this writing, Oracle only allows a single, default connection pool. User-defined pools aren’t currently supported.

Configuration options include, but aren’t limited to, minimum and maximum number of pooled servers, number of connection brokers, time to live for idle sessions, time to live for pooled sessions, and maximum number of connections that each connection broker can handle.

To configure DRCP within an Amazon RDS for Oracle environment, complete the following steps:

  1. Using your preferred tool, log in to the RDS for Oracle instance using the administrator account or an account with DBA privileges. In this post, I used SQL*Plus.
  1. Determine the default DRCP connection pool by running:
SQL> select connection_pool, status from dba_cpool_info;

CONNECTION_POOL                   STATUS
SYS_DEFAULT_CONNECTION_POOL       INACTIVE
  1. Modify the default DRCP connection pool by running the configure_pool procedure:
begin
sys.dbms_connection_pool.configure_pool(
pool_name => 'sys_default_connection_pool',
minsize => 20,
maxsize => 200,
incrsize => 8);
end;
/
  1. Activate the default DRCP connection pool using the start_pool procedure:
begin
sys.dbms_connection_pool.start_pool(
pool_name => 'sys_default_connection_pool');
end;
/
  1. Verify the DRCP connection pool has been started by running:
SQL> select connection_pool, status from dba_cpool_info;

CONNECTION_POOL                   STATUS
SYS_DEFAULT_CONNECTION_POOL       ACTIVE
  1. Determine the DRCP service name by running:
SQL> select name from dba_services where name = (select db_unique_name from v$database);

NAME
ORCLAPEX_A

Amazon RDS for Oracle appends an underscore and a letter (such as _A, _B, and so on) to the original service name.

Connect to DRCP on the RDS for Oracle instance

You can connect to DRCP using multiple methods: Oracle Easy Connect, a tnsnames.ora file entry that specifies the POOLED server type, or a JDBC connection. An example of each follows.

With the pool started, you can make a connection using the Easy Connect method:

SQL> connect admin/pwd@orclapex.cccncccccccn.us-east-1.rds.amazonaws.com:1521/ORCLAPEX_A:POOLED

Connected.

Alternatively, add an entry to the tnsnames.ora file. Afterwards, you can connect using the TNS alias specified.

ORCLDRCP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orclapex.cccncccccccn.us-east-1.rds.amazonaws.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = ORCLAPEX_A)
    )
  )

SQL> connect admin/pwd@orcldrcp
Connected.

To connect to DRCP using Java, you can specify SERVER=POOLED in the short URL as follows:

jdbc:oracle:thin:@//<host>:<port>/<service_name>[:POOLED]

For example, we can use

jdbc:oracle:thin:@//orclapex.cccncccccccn.us-east-1.rds:1521/orclapex_a:POOLED

Stop the DRCP on the RDS for Oracle instance

After DRCP is enabled, you don’t need to stop DRCP. If the connection pool is enabled when the database is shut down, the connection pool is enabled upon database restart. If an administrator needs to schedule application downtime and prevent application end-users from accessing the database, the administrator can stop the listener, shut down the database, or stop DRCP.

You can stop DRCP using the dbms_connection_pool.stop_pool method:

begin
sys.dbms_connection_pool.stop_pool(
pool_name => 'sys_default_connection_pool');
end;
/

Conclusion

In this post, we configured and enabled Oracle DRCP with Amazon RDS for Oracle.  You can use DRCP with application servers that can’t do middle-tier connection pooling. You can also use DRCP to manage hundreds or thousands of database connections from clients spread across multiple application servers.

When you use DRCP with an RDS for Oracle instance, you can manage database connections more efficiently, which allows Amazon RDS for Oracle to handle more application users with fewer database resources. Try the solution and leave your thoughts in the comments.

About the Author

Marvin Vinson is an Oracle Database Specialist for AWS. He works in the Worldwide Public Sector (WWPS) organization. He is focused on migrating Oracle workloads to AWS.