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

Database Resident Connection Pool (DRCP) in Oracle Database 11g Release 1

The database resident connection pool (DRCP) reduces the resource requirements of applications that currently don't support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented. DRCP is only supported for database connections using the OCI driver.

DBMS_CONNECTION_POOL
Pooled Connections
Views
DRCP and Java
12c Update

DBMS_CONNECTION_POOL

The pool is managed using the DBMS_CONNECTION_POOL package. Although the package appears to support multiple connection pools, the document states that it currently only supports the default pool name (SYS_DEFAULT_CONNECTION_POOL).

The DRCP is started and stopped using the START_POOL and STOP_POOL procedures respectively.

SQL> EXEC DBMS_CONNECTION_POOL.start_pool;

PL/SQL procedure successfully completed.

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

Pooled Connections

With the pool started, a connection can be made using the easy connect method.

SQL> CONNECT test/test@ole45.localdomain:1521/DB11G.WORLD:POOLED
Connected.
SQL>

Set the server type to POOLED in the tnsnames.ora file to allow connection pooling for a connection string.

DB11G_POOL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ole45.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
      (SERVER = POOLED)
    )
  )

Pooled connections are then possible using the amended connection string.

SQL> CONNECT test/test@DB11G_POOL
Connected.
SQL>

If a session remains idle for longer than the inactivity_timeout it is killed to free up space in the connection pool.

SQL> SELECT sydate FROM dual;
select sydate from dual
                      *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7902
Session ID: 170 Serial number: 20


SQL>

Attempting to connect to the pool when the pool is stopped results in the following error.

SQL> conn / as sysdba
Connected.
SQL> EXEC DBMS_CONNECTION_POOL.stop_pool;

PL/SQL procedure successfully completed.

SQL> CONNECT test/test@DB11G_POOL
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server


Warning: You are no longer connected to ORACLE.
SQL>

Views

Information about connection pooling is displayed using the following views:

DRCP and Java

The JDBC OCI driver does not currently support OCI session pool, so although configuring a pooled connection will not fail, it will result in dedicated connections on the server side, as described in MOS Note [ID 1087381.1].

Until the JDBC OCI driver is updated to support OCI session pool, there will be no benefit in using DRCP with Java, so session pooling must be implemented in the application server layer.

12c Update

Oracle Database 12c allows Java to use the Database Resident Connection Pool (DRCP), as described here.

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.