Enable Connection Pooling

Lesson 6 covered the theory: Shared Server pools server processes, and Database Resident Connection Pooling, DRCP, separately pools dedicated style servers through a connection broker. The two are complementary, not interchangeable, and this lesson is the hands on companion to that theory: verifying Shared Server is actually enabled the way you expect, then enabling and verifying DRCP specifically, since "connection pooling" without further qualification usually means DRCP in current Oracle material rather than Shared Server itself.

Before enabling DRCP, confirm Shared Server itself is in the state you actually expect, since a nonzero SHARED_SERVERS does not always mean what it looks like it means:

SHOW PARAMETER DISPATCHERS;
SHOW PARAMETER SHARED_SERVERS;

If a database was created with the Database Configuration Assistant, these may already show a SHARED_SERVERS value of 1, not because anyone deliberately enabled Shared Server for regular use, but because DBCA configures a dispatcher for Oracle XML DB, which requires it. That configuration only allows sessions connecting to the XDB service to use shared server; ordinary SQL sessions still need their own dispatcher configuration added, exactly as covered in Lesson 2, before they can use it at all. Do not take a nonzero SHARED_SERVERS at face value without checking what it is actually configured for; it is an easy way to assume Shared Server is ready for your application when it is really only serving XDB traffic.

To enable Shared Server for regular sessions, if it is not already configured that way:

ALTER SYSTEM SET SHARED_SERVERS = 4;
ALTER SYSTEM SET MAX_SHARED_SERVERS = 20;
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=2)';

None of this requires a restart. Every one of these parameters is dynamic, exactly as established in Lessons 2 and 4; there is no separate switch, and no step in this lesson involves bouncing the instance.

DRCP's own enabling and sizing, DBMS_CONNECTION_POOL.START_POOL(), along with MINSIZE and MAXSIZE, was covered in Lesson 6 and is not repeated here. What actually determines whether a given connection uses DRCP, Shared Server, or a dedicated server is the same connect descriptor attribute in every case, just with a different value:

(CONNECT_DATA=(SERVICE_NAME=your_service)(SERVER=shared))

requests Shared Server, (SERVER=pooled) requests DRCP, and (SERVER=dedicated), covered in Lesson 4, bypasses both. Mixing these up is the single most common way "connection pooling isn't working" turns out to mean the client never actually asked for it in the first place; the pool can be started, sized correctly, and fully healthy, and a connection that omits SERVER=pooled simply will not use it.

It is worth double checking this on the application side specifically, since it is usually set once in a connection string or a JDBC URL and then forgotten about. A middle tier application that was originally written against a dedicated or default connection, then later pointed at a database with DRCP enabled, gets none of DRCP's benefit until its own connect string is updated to actually request a pooled connection.

Put the two gotchas above together and a familiar support ticket falls out naturally. A team reports that "connection pooling doesn't seem to be doing anything," CPU and memory usage on the database server look the same as before they supposedly enabled it. The first check, SHOW PARAMETER SHARED_SERVERS, comes back with a value of 1, which looks encouraging at first glance, until a look at DISPATCHERS shows only the default XDB configuration DBCA created, not one covering the application's own protocol and service. Shared Server was technically enabled the whole time, just not for anything the application actually uses.

After adding a proper dispatcher configuration and confirming V$DISPATCHER shows it running, the team reports the problem persists. The second check is the application's own connection string, which turns out to specify no SERVER attribute at all, a leftover from before Shared Server was ever configured. Oracle falls back to a dedicated connection whenever SERVER is omitted and dispatchers aren't otherwise reachable in the way the application expects, so the fix is adding SERVER=shared explicitly rather than assuming the server side configuration alone would be enough. Neither half of this problem was visible from the database side or the application side in isolation; it took checking both.

For Shared Server, confirm connections are landing where expected:

SELECT * FROM V$SHARED_SERVER;
SELECT * FROM V$DISPATCHER;

An empty or unchanging result from V$SHARED_SERVER after a client connects with SERVER=shared is a sign the connection didn't actually end up on a shared server at all, worth checking against the client's own connect descriptor before assuming the server side configuration is broken.

For DRCP specifically, Lesson 6 already covered V$CPOOL_CONN_INFO for checking individual connection state by username, service, and how long each has been active or waiting. A quick confirmation that the pool itself is actually running is a useful first check before digging into individual connections:

SELECT STATUS FROM DBA_CPOOL_INFO;

If this doesn't return ACTIVE, nothing downstream, correct connect strings included, will actually use the pool; DBMS_CONNECTION_POOL.START_POOL() from Lesson 6 is the fix.

It's worth checking both layers together rather than assuming one implies the other. A pool that reports ACTIVE in DBA_CPOOL_INFO but shows no rows in V$CPOOL_CONN_INFO for the service you expect means the pool is healthy but nothing is actually connecting to it, almost always a client-side connect string issue rather than a server-side one. The reverse, connections appearing in V$CPOOL_CONN_INFO but the pool showing anything other than ACTIVE, would be unusual enough to investigate on its own.

If you need to disable Shared Server, setting SHARED_SERVERS to 0 stops new shared connections without cutting off existing ones. Oracle keeps whichever is smaller of the previous SHARED_SERVERS or MAX_SHARED_SERVERS value running until those particular clients disconnect naturally, rather than terminating active sessions mid-work. Setting both parameters to 0 instead terminates every shared server immediately, and queues any requests that were still in flight until one of the two values is raised again. Once every shared server client has actually disconnected, this statement terminates the dispatchers themselves:

ALTER SYSTEM SET DISPATCHERS = '';

DRCP is turned off the same way it's turned on, through DBMS_CONNECTION_POOL.STOP_POOL() from Lesson 6, rather than through any Shared Server parameter; the two remain separate switches for separate technologies, which is worth remembering here as much as anywhere else in this lesson.

Nothing in this lesson replaces client-side connection pooling, and it's worth being clear that the two aren't competing options. Oracle's own Universal Connection Pool for Java applications, and third-party pools such as HikariCP commonly used with Hibernate or Spring, solve a different problem: reusing connections on the application side of the wire, before a request ever reaches the database. These pair naturally with DRCP rather than substituting for it, exactly as covered in Lesson 6; a well built application typically pools connections on its own side and requests them from DRCP on the database side, getting the benefit of both layers rather than choosing between them.

Oracle's Automatic Workload Repository and Automatic Database Diagnostic Monitor reports are worth knowing about here too, not as connection pooling features themselves, but as the tools you would reach for if a system that should be benefiting from pooling still shows signs of connection related strain; they surface the wait events and resource contention that point to whether a pooling configuration is actually doing its job.

Pulled together, enabling connection pooling on an instance that doesn't have it yet comes down to a short list, in order: confirm what SHARED_SERVERS is already set to and why, rather than assuming; enable Shared Server, DRCP, or both depending on the actual workload, using the dynamic parameters and DBMS_CONNECTION_POOL procedures covered across this module, no restart required either way; update client connect strings to actually request SERVER=shared or SERVER=pooled rather than leaving them on whatever default they were written against; and verify with V$SHARED_SERVER, V$DISPATCHER, or DBA_CPOOL_INFO and V$CPOOL_CONN_INFO that connections are actually landing where you configured them to, rather than assuming success from the configuration alone.

Of the four, the last step is the one most often skipped, and it's the one the worked scenario above turned on twice over. A configuration that looks correct in every parameter file and every ALTER SYSTEM statement can still fail to do anything at all if the client side was never updated to match, so treat verification as part of enabling connection pooling, not as an optional follow-up once the "real" work is done.