Database Resident Connection Pool (DRCP)

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Database Resident Connection Pool

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.

The pool is managed using the DBMS_CONNECTION_POOL package. he 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> EXEC DBMS_CONNECTION_POOL.stop_pool;

PL/SQL procedure successfully completed.

The ALTER_PARAM procedure allows you to configure an individual pool parameter, while the CONFIGURE_POOL allows you 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

With the pool started, set the server type to POOLED in the tnsnames.ora file to allow connection pooling for a connection string.

EXAMPLE_POOL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydb.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = MYDB)
      (SERVER = POOLED)
    )
  )

It’s also possible using easy connect method:

SQL> CONNECT test/test@mydb.localdomain:1521/MYDB:POOLED
Connected.

In 11g DRCP does not support JDBC connections. With makes sense, once pool is configured on application side.
Since Database 12c, DRCP is possible using JDBC. Don’t ask me why. 🙂

And that’s it!
Cheers!