8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Database Resident Connection Pool (DRCP) Enhancements in Oracle Database 23c

Oracle database 23c enhanced the Database Resident Connection Pool (DRCP) functionality to include multiple named connection pools and implicit connection pooling.

The Database Resident Connection Pool (DRCP) was introduced in Oracle 11.1 to provide connection pool support for applications that currently don't support connection pooling. It was originally focused on languages such as PHP, but in Oracle 12c support for using DRCP with Java applications was introduced.

Multiple Named Connection Pools
PDB-Level Connection Pools
Implicit Connection Pooling

Multiple Named Connection Pools

In previous versions of the database the Database Resident Connection Pool (DRCP) functionality only supported a single default pool. In Oracle 23c we can create multiple named connection pools, each with a different configuration, allowing use to tailor each pool for a specific application if we need to.

We connect to the root container and display the available pools using the DBA_CPOOL_INFO view. We can see only the default pool is present.

conn / as sysdba

column connection_pool format a30

select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE

SQL>

We create a new pool using the ADD_POOL procedure in the DBMS_CONNECTION_POOL package.

PROCEDURE ADD_POOL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 POOL_NAME                      VARCHAR2                IN
 MINSIZE                        BINARY_INTEGER          IN     DEFAULT
 MAXSIZE                        BINARY_INTEGER          IN     DEFAULT
 INCRSIZE                       BINARY_INTEGER          IN     DEFAULT
 SESSION_CACHED_CURSORS         BINARY_INTEGER          IN     DEFAULT
 INACTIVITY_TIMEOUT             BINARY_INTEGER          IN     DEFAULT
 MAX_THINK_TIME                 BINARY_INTEGER          IN     DEFAULT
 MAX_USE_SESSION                BINARY_INTEGER          IN     DEFAULT
 MAX_LIFETIME_SESSION           BINARY_INTEGER          IN     DEFAULT
 MAX_TXN_THINK_TIME             BINARY_INTEGER          IN     DEFAULT

Most of the parameters have default values, so we can create a new connection pool just by providing the pool name. We can see all the default values in the DBA_CPOOL_INFO view.

exec sys.dbms_connection_pool.add_pool('test_pool_1');


select *
from   dba_cpool_info
where  connection_pool = 'TEST_POOL_1';

CONNECTION_POOL                STATUS              MINSIZE    MAXSIZE   INCRSIZE
------------------------------ ---------------- ---------- ---------- ----------
SESSION_CACHED_CURSORS INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION
---------------------- ------------------ -------------- ---------------
MAX_LIFETIME_SESSION  NUM_CBROK MAXCONN_CBROK MAX_TXN_THINK_TIME     CON_ID
-------------------- ---------- ------------- ------------------ ----------
TEST_POOL_1                    INACTIVE                  0         40          2
                    20                300            120          500000
               86400          1         40000                  0          1


SQL>

The ALTER_PARAM procedure allows us to configure an individual pool parameter, while the CONFIGURE_POOL procedure allows us to configure all pool parameters in one call. The default settings are restored using the RESTORE_DEFAULTS procedure. The pool parameters that are currently supported are listed below.

PROCEDURE CONFIGURE_POOL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 POOL_NAME                      VARCHAR2                IN     DEFAULT
 MINSIZE                        BINARY_INTEGER          IN     DEFAULT
 MAXSIZE                        BINARY_INTEGER          IN     DEFAULT
 INCRSIZE                       BINARY_INTEGER          IN     DEFAULT
 SESSION_CACHED_CURSORS         BINARY_INTEGER          IN     DEFAULT
 INACTIVITY_TIMEOUT             BINARY_INTEGER          IN     DEFAULT
 MAX_THINK_TIME                 BINARY_INTEGER          IN     DEFAULT
 MAX_USE_SESSION                BINARY_INTEGER          IN     DEFAULT
 MAX_LIFETIME_SESSION           BINARY_INTEGER          IN     DEFAULT
 MAX_TXN_THINK_TIME             BINARY_INTEGER          IN     DEFAULT

We start and stop a connection pool using the START_POOL and STOP_POOL procedures. The POOL_NAME parameter defaults to the default SYS_DEFAULT_CONNECTION_POOL pool.

exec sys.dbms_connection_pool.start_pool('test_pool_1');


select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE
TEST_POOL_1                    ACTIVE

SQL>


exec sys.dbms_connection_pool.stop_pool('test_pool_1');


select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE
TEST_POOL_1                    INACTIVE

SQL>

When we are using the default pool we can connect by adding "pooled" to the end of the EZConnect URL.

conn testuser1/testuser1@//localhost:1521/freepdb1:pooled

To connect to a named pool we need to specify the SERVER as POOLED and the POOL_NAME in the connect description. Here is an example of such a "tnsnames.ora" file entry.

TEST_POOL_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = FREEPDB1)
      (SERVER = POOLED)
      (POOL_NAME = TEST_POOL_1)
    )
  )

This allows us to connect to the named pool using a TNS alias.

SQL> conn testuser1/testuser1@test_pool_1
Connected.
SQL>

Once we connect we can see our session is pooled.

conn / as sysdba

select server
from   v$session
where  username = 'TESTUSER1';

SERVER
---------
POOLED

SQL>

We can look at the V$CPOOL_CC_INFO, V$CPOOL_CC_STATS and V$CPOOL_STATS views to check statistics for the pool usage.

We remove a pool using the following commands.

exec sys.dbms_connection_pool.stop_pool('test_pool_1');
exec sys.dbms_connection_pool.remove_pool('test_pool_1');

PDB-Level Connection Pools

By default connection pools are managed at the root container level, but this behaviour can be changed by setting the ENABLE_PER_PDB_DRCP parameter to true, as demonstrated below.

By default PDB-level connection pools are not available. An attempt to create one will result in the following error.

SQL> exec sys.dbms_connection_pool.add_pool('test_pool_1');
BEGIN sys.dbms_connection_pool.add_pool('test_pool_1'); END;

*
ERROR at line 1:
ORA-56515: DRCP: Operation not allowed from a Pluggable Database
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 3
ORA-06512: at line 1

SQL>

Before we can use PDB-level connection pools we must set some parameters in the root container.

conn / as sysdba
alter system set connection_brokers='((type=pooled)(brokers=1)(connections=40000))';
alter system set enable_per_pdb_drcp=true scope=spfile;
shutdown immediate;
startup;

Once the ENABLE_PER_PDB_DRCP parameter is set to true we can no longer manage connection pools from the root container.

conn / as sysdba
exec sys.dbms_connection_pool.add_pool('test_pool_1');

BEGIN sys.dbms_connection_pool.add_pool('test_pool_1'); END;

*
ERROR at line 1:
ORA-56615: DRCP: Operation not allowed from the ROOT
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 3
ORA-06512: at line 1


SQL>

Connection pool management inside the PDB should be performed by the PDB ADMIN user, although the SYS user can also be used.

conn sys/SysPassword1@localhost:1521/freepdb1 as sysdba

alter user pdbadmin identified by PdbAdmiPassword1 account unlock;

conn pdbadmin/PdbAdmiPassword1 @localhost:1521/freepdb1

Once connected to the PDB ADMIN user we can manage the pools as described in the previous section.

exec sys.dbms_connection_pool.add_pool('test_pool_1');


select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE
TEST_POOL_1                    INACTIVE

SQL>

To clean up and revert to the previous state we need to remove the pool and reset the ENABLE_PER_PDB_DRCP parameter.

exec sys.dbms_connection_pool.stop_pool('test_pool_1');
exec sys.dbms_connection_pool.remove_pool('test_pool_1');

conn / as sysdba
alter system set enable_per_pdb_drcp=false scope=spfile;
shutdown immediate;
startup;

Implicit Connection Pooling

Implicit connection pooling gives an additional level of control over database resident connection pools by indicating time boundaries. Setting the POOL_BOUNDARY to STATEMENT or TRANSACTION indicates at which point a connection can be released back to the connection pool.

STATEMENT : The connection is released back to the connection pool when the session is implicitly stateless.
TRANSACTION : The connection is released back to the connection pool when a transaction ends.

To be considered implicitly stateless, a session must satify all these conditions. If any of these conditions are not met, the session is considered implicitly stateful.

All the open cursors have been fetched through to completion.
No active transactions.
No temporary LOBs.
No global temporary tables containing rows.
No open private temporary tables.

Our choice of pool boundary will depend on how our application handles sessions. For applications where sessions are unlikely to be implicitly stateless, but we want to force the release back to the pool, then a pool boundary of transaction is the best option. If we know our sessions are mostly implicitly stateless, then the statement pool boundary is fine.

We add the POOL_BOUNDARY setting to our previous "tnsnames.ora" file entry to enable implicit connection pooling with a boundary of TRANSACTION.

TEST_POOL_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = FREEPDB1)
      (SERVER = POOLED)
      (POOL_NAME = TEST_POOL_1)
      (POOL_BOUNDARY=TRANSACTION)
    )
  )

We connect in the same way we did before.

SQL> conn testuser1/testuser1@test_pool_1
Connected.
SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.