DEV Community

Shubham Anandani
Shubham Anandani

Posted on Originally published at imshubh.com on

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
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)