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 serversFor 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 DRCPUse the dbms_connection_pool.start_pool to start DRCP with default settings
sqlplus /nolog connect / as sysdba execute dbms_connection_pool.start_pool();
Stop DRCPUse 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 DRCPDatabase 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 PoolingIn 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