About Database Resident Connection Pooling
(DRCP) in 11g

The pooled servers are managed within the database by a process called the connection broker.  Upon a client request, the connection broker hands the client off to an available pooled server.  If no pooled servers are available, it creates one.  If the pool has reached its defined maximum, the client request is placed on a queue until a pooled server is available.  The client communicates directly with the assigned pooled server, which essentially behaves as a dedicated server.  When the client releases it, the pooled server is returned to the connection broker. 

The DRCP connection architecture might sound similar to Oracles shared server model.  However, there are some distinct differences between using shared servers and DRCP.  Primarily, shared servers require the termination of a session when releasing database resources, whereas DRCP involves the release of the pooled server back to the pool.  Therefore, the memory requirement for DRCP is fairly consistent and relative to the number of pooled servers.  On the other hand, the memory requirement for shared servers is proportional to the sum of the shared servers since each client has a session.  The result is that DRCP can scale much better than shared servers for applications that frequently require sessions for a short amount of time. 

Using DRCP results in a significant reduction of database resources and memory required to support a large number of application end-users.  This greatly increases the scalability of both the database tier and the middle-tiers.  DRCP is designed to scale to tens of thousands of simultaneous connections. 

DRCP should be used when a large number of client connections need to be supported, and the client applications are similar and can share sessions.  The applications are similar if they connect to the database using the same credentials and the same schema.  The client applications should require each database connection for a short duration before releasing the connection. 

Configuring the Pool

Before starting DRCP, the pool settings can optionally be configured using the dbms_connection_pool.configure_pool procedure or the dbms_connection_pool.alter_param procedure.  This is only necessary if the default settings need to be changed.  Following are two important options that can be configured for the DRCP pool:

1.      minsize Minimum number of pooled servers (default is 4).

2.      maxsize   Maximum number of pooled servers (default is 40).

Additional pool options that can be configured include:

• inactivity_timeout Maximum time, in seconds, that a pooled server can stay idle in the pool before being terminated. Default is 300.  This does not apply when the number of pooled servers equals the minsize.

• incrsize Number of pooled servers by which the pool is incremented if a server is unavailable when a client request is received.  Default is 3.

• session_cached_cursors Number of cursors to cache for each pooled server session.  Default is 20.

• max_think_time Maximum time, in seconds, of inactivity for a client to hold a pooled server before the client connection is terminated (default is 30).

• max_use_session Number of times a pooled server can be taken and released by the pool.  Default is 5000.

• max_lifetime_session Maximum time, in seconds, for a pooled server to live in the pool.  Default 3600.

• num_cbrok Number of connection brokers created to handle client requests.  Default is 1.

• maxconn_cbrok Maximum number of connections that each connection broker can handle.  Default is 40000. 

Since Oracle has provided default values for each of these options, it is recommended to not change these values unless necessary.  If it happens that the customized configuration options are proving problematic, they can be restored using the defaults:

SQL> execute dbms_connection_pool.restore_defaults();