Spring Boot kept passing health checks while search requests waited for GlassFish-managed Oracle connections and eventually timed out.
The dashboard was green.
Glass Fish was running. Spring Boot answered the health endpoint. Oracle continued processing queries.
But users could not use the search page.
Requests reached the application and then waited several seconds before timing out. The search query itself took about 200 milliseconds, which did not appear slow enough to explain the failure.
The missing time was not inside the SQL query.
It happened before SQL execution began.
All 200 connections in the Glass Fish connection pool were active. New requests had to wait until another request returned a connection.
The application process was alive, but the service had no remaining capacity to perform useful work.
The Health Check Answered the Wrong Question
A health check proves only what it measures.
Our existing endpoint checked whether the Spring Boot application could respond. It did not require an Oracle connection, execute the search query, or inspect the GlassFish pool.
It answered this question:
Is the application process alive?
Users needed an answer to a different question:
Can the application still acquire the resources required to complete a search?
Those are not the same condition.
A process can accept HTTP requests while its worker threads wait for database connections. A lightweight health endpoint can continue returning 200 OK throughout the incident.
The health check was not incorrect.
Our interpretation of “healthy” was too broad.
Requests Stopped Before Reaching Oracle
The search endpoint followed a familiar path:
HTTP request
-> Spring Boot controller
-> service transaction
-> acquire connection from GlassFish
-> execute Oracle query
-> map result
-> return responseDuring normal traffic, connection acquisition was nearly immediate.
During the incident, the request stopped at the connection-pool boundary:
HTTP request
-> Spring Boot controller
-> service transaction
-> wait for available connection
-> wait
-> wait
-> request timeoutNo SQL appeared in the Oracle monitoring tools because the waiting requests had not reached Oracle yet.
This distinction matters during performance investigations. A request can be slow even when no slow query is running.
We Initially Blamed Oracle
Oracle was the first suspect.
That was reasonable. The endpoint performed a complex search, and the system handled between 200,000 and 300,000 requests during an eight-hour business period.
We checked:
- Oracle execution plans
- Database CPU and I/O
- Lock waits
- Active sessions
- Slow SQL reports
- JDBC errors
- Glass Fish thread usage
- Connection-pool statistics
One important search query took approximately 200 milliseconds.
In isolation, that did not look severe. Running the SQL once from a client produced an acceptable result.
But an isolated execution hides the capacity cost.
Every query occupies a database connection. The longer that connection remains occupied, the fewer requests the pool can serve over the same period.
A 200-millisecond query can be acceptable at 10 requests per second and dangerous during a burst of hundreds of requests per second.
Daily Request Totals Were Misleading
Three hundred thousand requests over eight hours gives an average rate of:
300,000 requests / 28,800 seconds
approximately equals 10.4 requests per secondIf every request held one connection for 200 milliseconds, the average connection demand would be approximately:
10.4 requests/second x 0.2 seconds
approximately equals 2.08 concurrent connectionsThat average cannot explain a pool of 200 connections becoming exhausted.
This was an important investigation checkpoint.
The daily total was true, but it was not useful for sizing the pool. We needed peak traffic, connection hold time, and the number of database operations performed by each request.
Production traffic is rarely distributed evenly.
It can arrive in bursts after users log in, scheduled processing completes, another system retries, or a popular search is triggered across multiple clients.
The developer should size constrained resources using peak concurrency, not daily averages.
Measure Where the Request Waits
We separated endpoint latency into four stages:
1. Connection acquisition 2. SQL execution 3. Result mapping 4. Response processing
The old monitoring combined these stages into one request duration. That made the database look responsible for time that was actually spent waiting in GlassFish.
We added explicit measurement around connection acquisition:
@Component
public class MeasuredSearchExecutor {
private final DataSource dataSource;
private final SearchRepository searchRepository;
private final SearchMetrics metrics;
public MeasuredSearchExecutor(
DataSource dataSource,
SearchRepository searchRepository,
SearchMetrics metrics) {
this.dataSource = dataSource;
this.searchRepository = searchRepository;
this.metrics = metrics;
}
public SearchResult execute(SearchCriteria criteria)
throws SQLException {
long acquisitionStarted = System.nanoTime();
try (Connection connection = dataSource.getConnection()) {
long connectionAcquired = System.nanoTime();
metrics.recordConnectionWait(
connectionAcquired - acquisitionStarted
);
long queryStarted = System.nanoTime();
SearchResult result =
searchRepository.execute(connection, criteria);
long queryCompleted = System.nanoTime();
metrics.recordSqlDuration(
queryCompleted - queryStarted
);
return result;
}
}
}The first timer measures how long getConnection() takes.
The second measures the database operation after a connection becomes available. These measurements answer different questions and should not be combined.
A request during saturation looked similar to this:
Connection acquisition: 8,000 ms SQL execution: 205 ms Result mapping: 34 ms Response processing: 18 ms Total: 8,257 ms
The query had not become much slower.
The request spent most of its time waiting for permission to run it.
Confirm Which Component Owns the Pool
GlassFish managed the Oracle connection pool and exposed it through JNDI.
Spring Boot used the JNDI resource:
spring.datasource.jndi-name=jdbc/SearchDataSource
With a managed data source, pool sizing and acquisition timeout belong primarily to GlassFish rather than HikariCP.
This is operationally important. Changing settings such as spring.datasource.hikari.maximum-pool-size will not resize a GlassFish-managed pool.
The developer should first confirm which component owns the connection pool.
The application dependencies can remain small:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>The Oracle JDBC driver must be compatible with the Java, Oracle, and GlassFish versions in use. In a GlassFish-managed setup, the driver is commonly installed in the application-server environment so the server can create the pool.
For Spring Boot 3 deployments, the developer should also verify compatibility with the selected Jakarta EE and GlassFish version.
What the Pool Looked Like During Saturation
The useful pool metrics were:
pool.active pool.idle pool.max pool.waiting pool.acquire.duration pool.acquire.timeout.count
During the incident, the pattern looked like this:
Connections
200 | active ─────────────────────
| ┌──────────────────────────────
150 | ┌──┘
| ┌──┘
100 | ┌──┘
| ┌──┘
50 | ┌──┘
|──┘
0 +------------------------------------------------
normal traffic peak traffic timeout
idle: falling to 0
active: rising to 200
waiting: rising after active reaches 200
timeouts: rising after acquisition wait reaches limitHigh active usage alone is not necessarily a failure.
The stronger saturation signal is the combination of:
- Active connections near the maximum
- Idle connections near zero
- Waiting requests increasing
- Acquisition latency increasing
- Acquisition timeouts occurring
Once the active count reached 200, additional demand became a queue.
Why Queueing Increased Latency So Quickly
Suppose 200 connections are occupied.
A new request cannot begin SQL execution until one connection returns. More requests continue arriving while it waits.
The timeline changes from this:
Normal request 0 ms acquire connection 3 ms connection available 3-203 ms execute query 203-250 ms map and return response
To this:
Request during saturation 0 ms request reaches Spring Boot 0-8,000 ms wait for pooled connection 8,000 ms acquisition timeout 8,000 ms request fails before SQL execution
The endpoint’s latency no longer tracks query duration.
It tracks queue depth, connection turnover, and the acquisition timeout.
This is why pool saturation can feel sudden. The system may appear stable until demand reaches the resource limit. After that point, waiting grows faster than the database execution time suggests.
Root Cause: Peak Demand Exceeded Connection Throughput
The root cause was not one bad number.
It was the relationship between:
- Peak requests per second
- Database operations per request
- Connection hold time
- Transaction scope
- Pool capacity
- Capacity available in Oracle
A useful starting estimate comes from Little’s Law:
required concurrent connections
approximately equals peak requests/second
x average connection hold time in seconds
x database operations per requestFor example:
500 requests/second x 0.2 seconds
approximately equals 100 concurrent connectionsAfter reducing the connection hold time:
500 requests/second x 0.02 seconds
approximately equals 10 concurrent connectionsThis is an estimate, not a final pool-size formula.
Real systems need additional headroom for traffic variation, slow-tail queries, transactions, retries, reporting jobs, and other endpoints sharing the pool.
The calculation also assumes one database operation per request. If one search performs three sequential operations that each hold a connection for 200 milliseconds, its total demand is different.
The developer should measure the full connection hold time, not only the SQL duration.
Connection Hold Time Can Be Wider Than the Query
A connection may remain occupied after SQL execution finishes.
For example:
@Transactional(readOnly = true)
public SearchResponse search(SearchCriteria criteria) {
List<SearchRow> rows = repository.search(criteria);
SearchResponse response = mapRows(rows);
auditSearch(criteria, rows.size());
return response;
}Depending on transaction and connection handling, the connection may remain associated with the transaction while mapping and audit work continue.
The developer should inspect:
- Transaction boundaries
- Multiple repository calls in one request
- Remote calls inside transactions
- Large result mapping
- Lazy loading after the main query
- Logging or auditing inside transactions
- Connection release mode
- Exception paths that fail to close resources
Database connections should not be held while the application calls another HTTP service, uploads a file, or performs unrelated CPU-heavy work.
Keep transaction boundaries as narrow as correctness allows.
Rule Out Leaks Before Resizing the Pool
A connection leak can produce the same pool symptoms.
Before increasing capacity, verify that connections are always returned:
try (Connection connection = dataSource.getConnection();
PreparedStatement statement =
connection.prepareStatement(SEARCH_SQL)) {
bindCriteria(statement, criteria);
try (ResultSet resultSet = statement.executeQuery()) {
return mapResults(resultSet);
}
}Try-with-resources closes the result set, statement, and connection even when mapping throws an exception.
In a framework-managed transaction, Spring handles the connection lifecycle. The developer should avoid manually mixing transaction-managed and manually managed connections without understanding ownership.
Useful evidence against a leak includes:
- Active connections fall after traffic decreases.
- Borrowed and returned connection counts remain balanced.
- No steadily increasing active baseline appears over time.
- Leak-detection or pool diagnostics do not identify abandoned connections.
- Heap or thread evidence does not show connections retained unexpectedly.
Our active count followed traffic and eventually recovered. That pointed toward capacity pressure rather than a permanent leak.
Fix Part 1: Reduce Connection Hold Time
The first fix was query optimization.
The search query filtered by customer, status, and creation time. Before optimization, the plan performed too much work for a frequently executed request.
A simplified query looked like this:
SELECT transaction_id,
customer_id,
status,
amount,
created_at
FROM search_transaction
WHERE customer_id = :customer_id
AND status = :status
AND created_at >= :start_time
AND created_at < :end_time
ORDER BY created_at DESC;The original Oracle execution plan was similar to:
-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 8421 | | 1 | SORT ORDER BY | | 4200 | 8421 | | 2 | TABLE ACCESS FULL | SEARCH_TRANSACTION | 4200 | 7980 | --------------------------------------------------------------------------------
The developer can inspect an actual plan with:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
NULL,
NULL,
'ALLSTATS LAST'
)
);ALLSTATS LAST helps compare estimated rows with actual rows when runtime statistics are available.
After reviewing the predicates and ordering, an index matching the access pattern was introduced:
CREATE INDEX idx_search_txn_customer_status_time
ON search_transaction (
customer_id,
status,
created_at DESC
);The resulting plan changed toward an index range scan:
-------------------------------------------------------------------------------- | Id | Operation | Name | Cost | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 128 | | 1 | TABLE ACCESS BY INDEX ROWID | SEARCH_TRANSACTION | 128 | | 2 | INDEX RANGE SCAN | IDX_SEARCH_TXN_CUSTOMER_STATUS_TIME | 24 | --------------------------------------------------------------------------------
The query duration fell from approximately 200 milliseconds to 20 milliseconds.
This index is an example, not a universal recommendation. Column order should be based on actual predicates, selectivity, ordering, data distribution, and the workload’s write cost.
Every additional index consumes storage and adds work to inserts, updates, deletes, statistics collection, and maintenance.
Faster Queries Improve Pool Throughput
Reducing query time from 200 milliseconds to 20 milliseconds is more than an endpoint improvement.
One connection can theoretically complete many more operations during the same period.
Simplifying the idea:
At 200 ms per operation: 1 connection can complete about 5 operations/second At 20 ms per operation: 1 connection can complete about 50 operations/second
Production behavior will not reach this theoretical maximum because of network time, transaction overhead, mapping, contention, and tail latency.
Still, the direction is important.
A connection returned sooner becomes available to another waiting request. Lower hold time improves effective pool throughput.
Fix Part 2: Increase Pool Capacity Carefully
The second change increased the GlassFish pool maximum from 200 to 600 connections per application-server instance.
Before:
<jdbc-connection-pool
name="SearchPool"
datasource-classname="oracle.jdbc.pool.OracleDataSource"
res-type="javax.sql.DataSource"
steady-pool-size="50"
max-pool-size="200"
pool-resize-quantity="25"
max-wait-time-in-millis="8000">
</jdbc-connection-pool>After:
<jdbc-connection-pool
name="SearchPool"
datasource-classname="oracle.jdbc.pool.OracleDataSource"
res-type="javax.sql.DataSource"
steady-pool-size="100"
max-pool-size="600"
pool-resize-quantity="50"
max-wait-time-in-millis="3000">
</jdbc-connection-pool>The exact GlassFish attributes and administration commands can vary by version.
The important changes were the maximum size and a bounded acquisition wait. Requests should fail predictably instead of waiting indefinitely and consuming application threads.
Six hundred connections should not become a copied default.
The increase was acceptable only after peak-load testing and Oracle-side capacity checks showed that the database could support the resulting session count.
Calculate the Total, Not Only One Pool
Pool limits multiply across deployed instances.
The total theoretical session demand is:
application instances
x pools per instance
x maximum connections per poolFor example:
3 application instances x 600 connections
= 1,800 possible Oracle sessionsIf there are two clusters with three instances each:
2 clusters x 3 instances x 600 connections
= 3,600 possible Oracle sessionsThis can exceed Oracle’s configured session capacity or create severe CPU, I/O, lock, and memory pressure.
The developer should review:
- Oracle
PROCESSESandSESSIONSlimits - Existing sessions from other applications
- Database CPU capacity
- Storage I/O
- Wait events
- Query concurrency
- Parallel execution
- Maximum sessions during failover
- Connection storms during application startup
A larger application pool can simply move the queue from GlassFish to Oracle.
That is not a capacity improvement.
It is a bottleneck relocation.
Make Pool Pressure Observable
The new dashboard tracked each application instance separately.
At minimum, it included:
pool.active pool.idle pool.max pool.utilization pool.waiting pool.acquire.duration pool.acquire.timeout.count
Useful derived values include:
pool.utilization = pool.active / pool.max
And:
pool.headroom = pool.max - pool.active
Alerts should focus on sustained pressure rather than a single brief spike.
For example:
Warning: pool utilization > 80% for 5 minutes Critical: pool utilization > 95% and pool waiting > 0 for 2 minutes Critical: connection acquisition p99 > 500 ms Critical: acquisition timeout count > 0
The thresholds should reflect the application’s latency target and traffic behavior.
Pool metrics should be broken down by cluster and instance. An aggregate value can hide one saturated instance behind another instance with idle capacity.
Log the Stage That Failed
We also added structured timing fields to search logs:
{
"event": "search_completed",
"requestId": "4f13ec70-3d33-4906-9712-cef7b36579f0",
"instance": "search-cluster-a-02",
"connectionAcquireMs": 5,
"sqlDurationMs": 21,
"mappingDurationMs": 8,
"totalDurationMs": 42,
"resultCount": 37,
"status": "SUCCESS"
}A failed acquisition looked different:
{
"event": "search_failed",
"requestId": "21427d76-421a-4023-ae17-796193ea26f5",
"instance": "search-cluster-a-02",
"connectionAcquireMs": 3001,
"poolActive": 600,
"poolIdle": 0,
"poolWaiting": 84,
"status": "CONNECTION_ACQUISITION_TIMEOUT"
}These logs made the failure stage visible without assuming every slow search was a slow Oracle query.
Be careful with search criteria in logs. Customer identifiers, account numbers, names, and free-text search values may contain sensitive information.
Separate Liveness, Readiness, and Capacity
We kept liveness lightweight.
A liveness check should answer whether the process is running and capable of making progress. Making liveness depend on an already saturated connection pool can create a restart loop.
For example:
Pool becomes busy
-> liveness fails
-> orchestrator restarts instance
-> remaining instances receive more traffic
-> their pools become busy
-> more instances restartReadiness can include dependency information, but it also needs careful design.
If every saturated instance immediately becomes unready, the load balancer may remove capacity and concentrate traffic on fewer remaining instances.
Pool saturation is primarily a capacity and performance signal. It usually belongs in dashboards, alerts, and traffic-control decisions rather than a simplistic binary liveness rule.
A practical model is:
- Liveness: Is the process alive and its critical threads functioning?
- Readiness: Can this instance accept normal traffic under current dependency conditions?
- Capacity metrics: How close is the instance to resource exhaustion?
- Service-level indicators: Are real requests completing within their latency and error targets?
One green endpoint cannot replace all four.
Verify the Fix Under Peak Load
The team tested both changes together and separately.
A useful load test should reproduce peak traffic, not the eight-hour average.
For example:
Warm-up: 5 minutes at 100 requests/second Normal load: 10 minutes at 250 requests/second Peak load: 10 minutes at 500 requests/second Burst: 2 minutes at 700 requests/second Recovery: 5 minutes at 100 requests/second
The search criteria should reflect production data distribution. Repeating one highly cached query can make both Oracle and the application look unrealistically fast.
During the test, measure:
- Search latency at p50, p95, and p99
- Connection-acquisition latency at p50, p95, and p99
- Active, idle, and waiting connections
- Acquisition timeouts
- HTTP timeout rate
- Oracle sessions
- Database CPU and I/O
- Oracle wait events
- Query execution plans
- Application thread count
- Garbage collection and heap usage
Also test the limit deliberately.
A system should fail predictably when the pool is exhausted. Bounded acquisition time, clear error handling, and overload protection are better than requests waiting until every application thread is occupied.
Result After the Changes
The final result combined efficiency, capacity, and observability.
Representative production measurements looked like this:
Query duration: 200 ms -> 20 ms Connection acquisition: 8,000 ms -> 5 ms Waiting requests: 140 -> 0 Search timeout rate: 12% -> 0.1%
These values should be replaced with measurements from the actual environment before they are used as formal performance claims.
More importantly:
- Connections returned to the pool sooner.
- Acquisition latency stayed low during expected peaks.
- Waiting requests remained near zero.
- Search timeouts dropped.
- Oracle stayed within validated session and resource limits.
- Pool pressure became visible before users reported failures.
The application was no longer considered healthy only because its process could answer a lightweight endpoint.
Should Workloads Use Separate Pools?
Separate pools can isolate workloads such as interactive searches and large reporting jobs.
For example, a report should not consume every connection needed by user-facing requests.
However, separate pools do not create database capacity. Their maximum sizes still contribute to Oracle’s total session and workload limits.
Use separate pools when:
- Workloads have genuinely different latency requirements.
- One workload can monopolize shared capacity.
- Each pool has an intentional limit.
- Oracle can support the combined maximum.
- Operational ownership is clear.
Do not use separate pools only to hide saturation metrics.
Lessons Learned
- Liveness proves that a process can respond. It does not prove that the service has enough capacity to serve users.
- User-visible latency includes connection acquisition, not only SQL execution.
- Daily request totals hide peak concurrency and are insufficient for pool sizing.
- Query efficiency reduces connection hold time, while pool size controls concurrency capacity.
- Transaction scope can keep a connection occupied after the main query finishes.
- A larger pool must be validated against total Oracle session and resource capacity.
- Pool pressure should be visible per instance before waiting requests become timeouts.
Conclusion
Our server was healthy according to the health check we had written.
Our users were waiting according to the system we had actually built.
The search query took about 200 milliseconds, but some requests waited eight seconds for a connection before that query could even begin.
Optimizing the query improved connection turnover. Increasing the pool added concurrency headroom. Monitoring made the pressure visible.
The real lesson was not to increase every connection pool to 600.
It was to ask a better operational question.
Not only:
Is the application responding?
But also:
Can it still acquire the resources required to serve users?
