DRCP - Database Resident Connection Pooling

Database Resident Connection Pool (DRCP) is a connection pool in the server that is shared across many clients. Best practice is to use DRCP in connection pools where the number of active connections is fairly less than the number of open connections. DRCP increases Database server scalability and resolves the resource wastage issue that is associated with middle-tier connection pooling. The Database Resident Connection Pool implementation creates a pool on the server side, which is shared across multiple client pools. This significantly lowers memory consumption on the server because of reduced number of server processes on the server and increases the scalability of the Database server.



According to the Tom Kytes : Connection pooling is generally the practice of a middle tier (application server) getting N connections to a database. These connections are stored in a pool in the middle tier, an "array" if we will. Each connection is set to "not in use" . When a user submits a web page to the application server, it runs a piece of code, our code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to us. We generate the page, format the html whatever -- and then return the connection to the pool where someone else can use it.In this fashion, using connections to the database, we can avoid the connect/disconnect overhead.

Senario with multiple application servers

For example, in a middle-tier connection pool, if the minimum pool size is 100, then the connection pool has 100 connections to the server, and the Database server has 100 server processes associated with these connections. If there are 30 middle tiers with a connection pool of minimum size 100, then the server has 3000 (100 * 30) corresponding server processes running. Typically, on an average only 5% of the connections, and in turn, server processes are in use at any given time. So, out of the 3,000 server processes, only 150 server processes are active at any given time. This leads to over 2,850 unused server processes on the server. These unused processes are the wasted resources on the server.

Start DRCP

Use the dbms_connection_pool.start_pool to start DRCP with default settings

sqlplus /nolog
   connect / as sysdba
   execute dbms_connection_pool.start_pool();

Stop DRCP

Use the dbms_connection_pool.stop_pool to stop DRCP

sqlplus /nolog
   connect / as sysdba
   execute dbms_connection_pool.stop_pool();

Routing Client Connection Requests to the Connection Pool

server01.joords.nl:1521/books.joords.nl:POOLED
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=server01.joords.nl)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=book.joords.nl)(SERVER=POOLED)))
jdbc:oracle:thin:@//server01.joords.nl:5221/book.joords.nl:POOLED

RAC and DRCP

Database Resident Connection Pool is certified to work with Real Acppliaction Cluster. Each database instance has its own connection broker and pool of servers. Each pool has the identical configuration. For example all pools will start with minsize server processes. A single dbms_connection_pool command will alter the pool of each instance at the same time.

Compatibility Database Resident Connection Pooling

In Oracle 11
     DRCP is available to clients that use the OCI driver with C, C++, and PHP.
In Oracle 12
     DRCP is compatible with JDBC

References

1. Documentation DBMS_CONNECTION_POOL 
2. Summary of DBMS_CONNECTION_POOL Subprograms