Multi-pool Database Resident Connection Pooling (DRCP) in Oracle Database 23ai

Photo by Igor Omilaev on Unsplash

Database Resident Connection Pooling (DRCP) is an Oracle Database feature that enables applications and mid-tier services to handle multiple data requests, while optimizing database resource usage. DRCP makes any application tier using Oracle Database extremely scalable and high-performant, capable of supporting highly concurrent connection traffic.

However, DRCP allowed the creation of only one pool per database instance — the default pool (SYS_DEFAULT_CONNECTION_POOL) — so far, which mitigated the flexibility of the DRCP pool configuration.

Oracle Database 23ai introduced the Multi-pool DRCP feature to enable creation of multiple, named pools with different configurations. With this feature, DRCP pools can be added or removed by the Database Administrators. This feature can be configured at both CDB and PDB level. The application will have to specify the pool name in the connect string to access a specific DRCP named pool.

Multi-pool DRCP architecture

Multi-pool DRCP does not require any special configuration on the database server side, other than enabling DRCP.

Configuring Multiple Pools in DRCP

How do I add and remove DRCP pools on the database server side?

A new procedure dbms_connection_pool.add_pool() adds the new pool.

PROCEDURE add_pool(
    pool_name  IN VARCHAR2, --Case insensitive name with alphabets, unique to a PDB or CDB, where it is being created
    minsize    IN PLS_INTEGER := 0,
    maxsize    IN PLS_INTEGER := 20,
    incrsize   IN PLS_INTEGER := 2,
    session_cached_cursors   IN PLS_INTEGER := 20,
    inactivity_timeout       IN PLS_INTEGER := 300,
    max_think_time           IN PLS_INTEGER := 120,
    max_use_session          IN PLS_INTEGER := 500000,
    max_lifetime_session     IN PLS_INTEGER := 86400,
    max_txn_think_time       IN PLS_INTEGER := 0);

If the configuration is known at the time of adding the pool, We can supply all the config parameters as shown above. If the configuration is not known or the pool is to be reconfigured later, the existing configure_pool() procedure can be used, where you can pass the new pool name and its configuration values.

An error is thrown if add_pool()is called with the same name again in the same PDB or CDB.

Note that SYS_DEFAULT_CONNECTION_POOL will still be automatically created when DRCP is enabled, and remains the default DRCP pool.

dbms_connection_pool.remove_pool(pool_name IN VARCHAR2)removes the pool.

How do I configure applications to work with Multi-pool DRCP?

The application will need to specify (POOL_NAME=<pool_name>) in the connect string with (SERVER=POOLED) specified, for DRCP to mark a client connection against the appropriate pool. For example:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=port_number))
(CONNECT_DATA=(SERVICE_NAME=db_service.company.com>)(SERVER=POOLED)
(POOL_NAME=my_pool)))

When a connect or GET request comes from the application with my_pool pool name to the DRCP connection broker and if my_pool is ACTIVE, then the connection broker will hand-off the connection request to my_pool. If my_pool is inactive or not created, an appropriate error will be thrown.

Managing Multiple Pools in DRCP

• All the existing DRCP procedures start_pool(), stop_pool(), configure_pool(), alter_param() and restore_defaults() on the dbms_connection_pool package can be used to manage the newly created pools
• Authentication pool and connection brokers are common for the default and newly created pools
• All the DRCP pools are restarted if the status is ACTIVE when the instance restarts or the PDB reopens

Monitoring Multiple Pools in DRCP

A new POOL_NAME column is added in the V$CPOOL_CC_STATS and V$CPOOL_CONN_INFO dynamic views for monitoring all the DRCP pools. The information statistics are updated in all the views for the newly created pools.

Conclusion

Multi-pool DRCP provides configuration flexibility to Database Administrators and helps organize the database connections based on the type of incoming application requests.