Configuring Database Resident Connection Pooling (DRCP)

Database Resident Connection Pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers. A pooled server is the equivalent of a server foreground process and a database session combined.

Connection Broker process (CMON) manages the pooled servers in the database instance. Clients are persistently connected and authenticated to the Broker. Clients request the Broker to provide pooled servers when they need to perform some database activity, use them, and then release them back for reuse by other clients (eg;  PHP applications).

Upon a request from the client on the persistent channel, the broker picks the appropriate pooled server and hands-off the client to that pooled server. The client directly communicates with the pooled server for all its database activity. The pooled server is handed back to the broker when the client releases it.

When DRCP is used with RAC, each database instance has its own connection broker and pool of servers. Each pool has the identical configuration.

DRCP is well suited when multiple clients access to the database with

• Large connections with less memory usage
• Similar client connections which can reuse sessions
• Applications which acquire connections for short time and release it

Configuration
1- DRCP requires little configuration. It is preinstalled (User defined pools are currently not supported) in any Oracle 11g database, and merely needs to be started. The procedure brings up the Broker, which registers itself with the database listener. Lsnrctl sevices will also show CMON process

SQL> exec dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.

-- see the status
SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL	STATUS	MAXSIZE
SYS_DEFAULT_CONNECTION_POOL	ACTIVE	40

You can use the below procedure if the default settings have to be changedSQL> execute dbms_connection_pool.configure_pool(null,minsize=>4,maxsize=>40,inactivity_timeout=>300,max_think_time=>600)
PL/SQL procedure successfully completed.
After the timed out, resource will be freed from the pool, allowing another session to complete.

c:\TEMP>lsnrctl services

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 11-NOV-2013 19:30
:43
.....
      "N000" established:5 refused:0 current:0 max:40000 state:ready
         CMON
         (ADDRESS=(PROTOCOL=tcp)(HOST=D-OR-01.HOMETEST.LOC)(PORT=50479))
The command completed successfully


2-  Net service name needs to have (SERVER=POOLED) in CONNECT_DATA section for DRCP to be used. Modify the TNS entry to use DCRP
HOMEDEVNEW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.202)(PORT = 1521))
    (CONNECT_DATA =
      ##(SERVER = DEDICATED)
 (SERVER = POOLED)
      (SERVICE_NAME = homedevnew)
    )
  )

3- Stop the pool. Once enabled, the pool automatically restarts when the instance restarts, unless explicitly stopped.

SQL> exec dbms_connection_pool.stop_pool;
PL/SQL procedure successfully completed.

4- Monitoring DRCP
-- display configuration
select connection_pool, status, maxsize from dba_cpool_info;

CONNECTION_POOL	STATUS	MAXSIZE
SYS_DEFAULT_CONNECTION_POOL	ACTIVE	40

--display information about the DRCP statistics
select num_requests, num_hits, num_misses, num_waits from v$cpool_stats;

NUM_REQUESTS	NUM_HITS	NUM_MISSES	NUM_WAITS
4	0	4	0

-- display information about the connection class level statistics for the pool
select cclass_name, num_requests, num_hits, num_misses from v$cpool_cc_stats;

CCLASS_NAME	NUM_REQUESTS	NUM_HITS	NUM_MISSES
HOME.SHARED	4	0	4

-- insight into client processes that are connected to the connection broker
select username,cclass_name,service,program,machine,connection_mode,connection_status from V$CPOOL_CONN_INFO

USERNAME	CCLASS_NAME	SERVICE	PROGRAM	MACHINE	CONNECTION_MODE	CONNECTION_STATUS
HOME	HOME.SHARED	homedevnew	sqlplus.exe	HOME\INAM-PC	RESERVED	ACTIVE

DRCP Restrictions
You cannot perform the following activities with pooled servers:

• Shut down the database
• Stop the database resident connection pool
• Change the password for the connected user
• Use shared database links to connect to a database resident connection pool
• Use Advanced Security Option (ASO) options such as encryption, certificates, and so on
• Neither supported with JDBC-THIN nor beneficial with JDBC-OCI. DRCP is available to clients that use the OCI driver with C, C++, and PHP.