12c: Database Resident Connection Pooling

Database Resident Connection Pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios. It complements middle-tier connection pools that share connections between threads in a middle-tier process. DRCP is relevant for architectures with multi-process single threaded application servers (such as PHP/Apache) that cannot perform middle-tier connection pooling. DRCP is available to clients that use the OCI driver with C, C++, and PHP.

Oracle Database includes a default connection pool called SYS_DEFAULT_CONNECTION_

POOL. By default, this pool is created, but not started. To enable database resident connection pooling, you must explicitly start the connection pool. As per documentaion currently, only the default connection pool is supported. When you start the pool a new CMON process will be running.

Start DRCP
To start DRCP, Run the command

SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
PL/SQL procedure successfully completed.

Verify Check

You can check DRCP values and confirm running

SQL> select * from dba_cpool_info;

C:\Users\inam.HOME>lsnrctl services

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 25-AUG-2013 10:25:41

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

Service "or12c.home.domain" has 1 instance(s).

  Instance "or12c", status READY, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:2 refused:0 state:ready

         LOCAL SERVER

      "N000" established:0 refused:0 current:0 max:40000 state:ready

         CMON

         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=62511))

Service "or12cXDB.home.domain" has 1 instance(s).

  Instance "or12c", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER

         (ADDRESS=(PROTOCOL=tcp)(HOST=Inam-pc.home.domain)(PORT=60760))

Service "pdb_plug_move.home.domain" has 1 instance(s).

  Instance "or12c", status READY, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:2 refused:0 state:ready

         LOCAL SERVER

      "N000" established:0 refused:0 current:0 max:40000 state:ready

         CMON

         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=62511))

The command completed successfully

Using DRCP
Net service name needs to have (SERVER=POOLED) in CONNECT_DATA section for DRCP to be used.

DRCPTEST = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = inam-pc.home.domain)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
      (SERVER = POOLED) 
      (SERVICE_NAME = or12c.home.domain) 
    ) 
  )

Stopping DRCP

SQL> EXECUTE DBMS_CONNECTION_POOL.STOP_POOL('SYS_DEFAULT_CONNECTION_POOL');
PL/SQL procedure successfully completed.

OR

SQL> EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();

The connection pool is automatically restarted when the database instance is restarted if the pool was active at the time of instance shutdown. 

Views
Information about connection pooling is displayed using the following views:
DBA_CPOOL_INFO
V$CPOOL_CC_INFO
V$CPOOL_CC_STATS
V$CPOOL_STATS

DRCP and Java

DRCP is neither supported with JDBC-THIN nor beneficial with JDBC-OCI. JDBC OCI does not have OCI session pool hence the real benefit of DRCP cannot be leveraged with JDBC. The user can still connect to DRCP specifying the DRCP Connect String but it will be equivalent to dedicated mode. Hence using JDBC for DRCP is not recommended.