Theory Behind Connection Pooling

Shared Server, covered across Lessons 1 through 5, already pools one scarce resource: server processes. A dispatcher queues client requests, and a small pool of shared servers, growing and shrinking dynamically between SHARED_SERVERS and MAX_SHARED_SERVERS, picks them up one at a time. That is real pooling, and it is why a handful of shared servers can support far more sessions than there are processes.
But that mechanism does not touch a second scarce resource: the physical network connections and dedicated style server resources a client would otherwise tie up for its entire session. That is what Database Resident Connection Pooling, DRCP, exists to address. DRCP is a genuinely separate, complementary technology from everything covered so far in this module, not another name for the same Shared Server mechanism, and describing it accurately is this lesson's actual objective.

DRCP pools dedicated style servers, not shared servers. A pooled server is the equivalent of a dedicated server foreground process and a database session combined, sitting ready in a pool rather than being spun up fresh for each connection. Clients requesting a pooled connection stay persistently connected to a background process called the connection broker, not directly to a dedicated server at all. When a client actually has database work to do, the broker assigns it a pooled server from the pool; the client is then connected directly to that server for the duration of the work. Once the request finishes, the server goes back into the pool and the client's connection reverts to the broker.

This matters because it decouples two things that are otherwise tied together: how many clients are connected, and how many expensive server side resources are actually in use at any given moment. A client can sit connected to the broker indefinitely without holding a pooled server the whole time, which is exactly the pattern a typical web application produces: acquire a connection, do a small amount of work, release it, and repeat, often thousands of times a minute across many middle tier processes and even many middle tier hosts.

Consider a web application running across twenty middle tier hosts, each running several application server processes, all talking to the same database. Without DRCP, each of those processes would typically maintain its own pool of dedicated connections, and the database would need enough dedicated servers to cover the sum of every middle tier process's pool, even though only a fraction of those connections are doing real work at any instant. With DRCP, all of those middle tier processes, across every host, share the same database side pool of pooled servers through the connection broker. The database only needs enough pooled servers for the actual peak concurrent workload, not for the sum of every middle tier pool added together, which is where the bulk of DRCP's resource savings actually come from.

DRCP works independently of whether Shared Server is enabled on the instance at all. It is a separate pooling layer addressing a different resource than Shared Server's own dispatcher and shared server pool, even though both exist to support more clients with fewer expensive resources. An instance can run DRCP, Shared Server, both together, or neither, depending on what the workload actually needs.

DRCP is started and stopped with the DBMS_CONNECTION_POOL package, connected as the SYS user:

EXECUTE DBMS_CONNECTION_POOL.START_POOL();
EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();

Once started, the pool stays running until explicitly stopped, and it restarts automatically along with the instance if it was active at shutdown. In an Oracle RAC environment, any instance can manage the pool, and configuration changes apply across every RAC instance.

Pool size and behavior are controlled by a handful of parameters, set with CONFIGURE_POOL for a full reconfiguration or ALTER_PARAM to change one value at a time:

• MINSIZE: the minimum number of pooled servers kept ready. The default is 4 at the database level, 0 at the PDB level.
• MAXSIZE: the maximum number of pooled servers the pool will grow to. The default is 40.
• MAXCONN_CBROK: the maximum number of connections a single connection broker process can handle, which needs to stay within whatever limit the host operating system allows for that user.

EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM('', 'MINSIZE', '10');
EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM('', 'MAXSIZE', '266');

RESTORE_DEFAULTS reverts every parameter back to its shipped default if a round of tuning needs to be undone entirely:

EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS();

DRCP can be configured for an entire database or scoped to an individual PDB; enabling it at the PDB level automatically disables the database level pool, and a PDB administrator needs explicit privileges, including execute on DBMS_CONNECTION_POOL itself, before they can manage their own PDB's pool. A related PDB level parameter, DRCP_CONNECTION_LIMIT, caps the maximum number of DRCP connections a single PDB can use; left at its default of 0 in the CDB root, it is unlimited, while a PDB that previously had a persistent SESSIONS value set for it defaults instead to ten times that value after a restart. On the client side, a connection asking for a pooled server sets (SERVER=POOLED) in its CONNECT_DATA, the same place Lesson 4 covered (SERVER=DEDICATED).

A handful of data dictionary views cover both the pool's overall health and the state of individual connections within it. DBA_CPOOL_INFO reports the pool's status along with its configured minimum and maximum size and idle timeout, a good first stop for confirming the pool is actually running the way you last configured it:

SELECT STATUS, MINSIZE, MAXSIZE, CONNECTION_TIMEOUT
FROM DBA_CPOOL_INFO;

V$CPOOL_STATS reports pool level statistics such as the number of session requests and how often a matching session was already available in the pool rather than needing to be created. V$CPOOL_CC_INFO and V$CPOOL_CC_STATS break the same kind of information down by connection class, for applications that use that feature to group related connections.

V$CPOOL_CONN_INFO is the view to reach for when the question is about specific connections rather than the pool as a whole. It reports each connection's status, either WAITING or ACTIVE, along with how long it has been in that state:

SELECT USERNAME, SERVICE, LAST_WAIT_TIME
FROM V$CPOOL_CONN_INFO
WHERE CONNECTION_STATUS = 'WAITING';

The same view, filtered the other way, shows which connections have been actively holding a pooled server the longest, which is often the more useful query when the pool feels undersized and you want to know whether a small number of long running connections are the actual cause:

SELECT USERNAME, SERVICE, ACTIVE_TIME
FROM V$CPOOL_CONN_INFO
WHERE CONNECTION_STATUS = 'ACTIVE'
ORDER BY ACTIVE_TIME DESC;

DRCP has two close relatives, both introduced briefly in Lesson 1. Proxy Resident Connection Pooling, PRCP, provides similar pooling on Oracle Connection Manager in Traffic Director Mode, using the Oracle Call Interface Session Pooling feature rather than sitting inside the database itself. Implicit Connection Pooling is a capability of PRCP, new in Oracle AI Database 26ai, that gives an application pooling benefits automatically, with no application code changes required, which matters most for architectures such as multi-process, single-threaded application servers that cannot maintain their own middle tier connection pool.

Middle tier connection pools, such as a Java application server's JDBC pool, and connection resilience features like Fast Application Notification and Fast Connection Failover, solve a related but distinct problem: managing a client side pool of connections and reacting quickly when something fails, rather than pooling the server side resources the way DRCP does. These two layers, client side pooling and DRCP, are complementary rather than competing, and a well tuned system typically uses both together: the application pools connections on its side for fast reuse, while DRCP keeps the database side lean regardless of how many middle tier processes or hosts are ultimately involved.

It's worth being clear about what none of these technologies replace, too. Nothing in this lesson changes how a long running batch job or an RMAN backup should connect, covered back in Lesson 4: work that holds a server busy for minutes rather than milliseconds still belongs on an ordinary dedicated connection, not cycling through any pool, shared server or DRCP alike, built around the assumption that most connections are idle most of the time.

The theme underneath all of this, from Shared Server's dispatcher pool through DRCP's connection broker, is the same one this whole module has returned to repeatedly: decoupling the number of clients that can stay connected from the number of expensive server side resources actually needed to serve them at any given moment. Shared Server does this for server processes; DRCP does it again, independently, for dedicated style connections and the network endpoints behind them.

The next lesson discusses how to enable connection pooling.