Enabling and Monitoring DRCP in 11g

In order to enable DRCP, the connection broker needs to be started.  The following command starts the broker, which registers itself with the database listener from sys user :

SQL> execute dbms_connection_pool.start_pool(); 

Once the pool is started, it remains in this state until it is explicitly stopped.  The pool is automatically restarted if it is active when the database instance is restarted. 

In order for the client connections to use the connection pool, the connect string must specify the connect type as POOLED.  For example, the TNS connect descriptor to use DRCP for client connections could be:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=databasehost)
       (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ora11gr1)
       (SERVER=POOLED)))

The DRCP pool can be stopped when all client requests managed by DRCP have completed using the following command from sys user:

SQL> execute dbms_connection_pool.stop_pool(); 

Considerations with DRCP

Since the pooled servers are essentially a pool of preserved dedicated servers, there are certain special considerations that apply to the use of DRCP in the database:

• Modifying database users of DRCP - Since pooled servers are dedicated servers owned by the user, it is not possible to change the password of a user while that user is connected.  Also, when dropping a user, make sure that no sessions authenticated by that user are in the pool.

• Shutting down the database - When client connections are actively being handled by DRCP, the database needs to wait until all pooled servers are returned to the connection broker before stopping the pool and shutting down the database. 

• Advanced Security Option (ASO) - ASO options are not available for pooled servers. 

Monitoring DRCP

Oracle 11g has provided several dynamic views for monitoring database resident connection pooling including:

• dba_cpool_info - Contains information about the connection pool including the maximum and minimum number of connections, pool status, and timeout for idle sessions.

• v$cpool_stats - Contains pool statistics including the number of session requests, the total wait time for a session request, and the number of times a session that matches the request was found in the pool.

• v$cpool_cc_stats - Contains connection-class level statistics for the pool.