UCP pool tuning for resilience and validation

Problem

UCP has dozens of properties, and the defaults work. What I wanted was predictable behavior under stress: callers that fail fast instead of hanging, bad connections that are caught before they reach the application, and a pool that does not quietly use up the database's session budget.

Options and trade-offs

• A small pool with a modest maximum: gentle on the database, and it may add latency while the pool warms up.
• A larger minimum or initial size: faster first requests, and idle sessions you are paying for.
• Validation on borrow: safer, with a small cost on each borrow. A short trust window reduces that cost.

My recommendation

Set the minimum and maximum sizes on purpose, turn on validation on borrow with a short trust window, and give callers a wait timeout. Then measure and adjust. Change one property at a time, so you know which change helped.

Worked example

Start from the database's limit and work backwards. If a database can comfortably serve 200 sessions for this application and you run 8 service instances, a maximum of 20 per instance already reaches 160, which leaves room for failover and administration.

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("<jdbc_url>");
pds.setUser(System.getenv("DB_USER"));
pds.setPassword(System.getenv("DB_PASSWORD"));
 
// Sizing
pds.setInitialPoolSize(2);
pds.setMinPoolSize(2);
pds.setMaxPoolSize(20);
 
// Resilience
pds.setValidateConnectionOnBorrow(true);
pds.setSecondsToTrustIdleConnection(5);   // skip re-validation for 5 s
pds.setConnectionWaitTimeout(3);          // seconds a caller waits for a connection
pds.setInactiveConnectionTimeout(300);    // release idle connections after 5 min

To watch the pool, log the borrowed and available counts on a timer, or expose them through JMX:

log.info("ucp borrowed={} available={}",
         pds.getBorrowedConnectionsCount(),
         pds.getAvailableConnectionsCount());

If borrowed stays close to the maximum and callers start timing out, the first suspect is a slow query or a leaked connection, not the pool size.

Checklist

1. Derive the maximum from the database's session budget and the number of instances.
2. Turn on validation on borrow, and use a short trust window if the overhead matters.
3. Set a connection wait timeout so callers cannot hang.
4. Log or export the borrowed, available and waiting counts.
5. Test failover and a database restart.
6. Revisit sizes after load testing, in small steps.

Where this doesn't apply

Large Oracle RAC deployments that rely on Fast Application Notification and replay need high-availability features configured, and that is a separate tuning exercise.

One honest limitation

Validation costs a little on every borrow. Only widen the trust window after the basic reliability picture is clear, or you may hide the very failures you wanted to catch.

Sources

• Oracle Universal Connection Pool Developer's Guide: Setting Connection Pool Properties
• Oracle UCP Java API Reference: PoolDataSource
• Oracle UCP Developer's Guide: Monitoring the Pool with JMX