Connection pool sizing on a two-node VDP cluster
A media company runs three Denodo servers (two Virtual DataPort nodes plus a Scheduler node) against one Oracle 19c warehouse licensed for 150 concurrent sessions. Each VDP node defines the warehouse data source with initial connections = 20 and maximum connections = 200; pooling is enabled, but no idle timeout and no validation query are configured.
Every weekday at 08:00 the DBA sees ORA-00020 within four minutes. Denodo monitoring reports 150-190 active connections on that source, yet only 8-12 are actually executing queries; the rest sit idle. Analysts then receive 'cannot obtain connection' errors for 10-15 minutes, and three base views hit the 60-second query timeout. Nothing in the load pattern changed in the last quarter except a new dashboard release.
A month ago the warehouse failed over to standby; afterwards every node threw connection-reset errors against that source for roughly 40 minutes, while newly opened sessions worked normally.
The estate has 40 base views and 12 derived views on the source, about 120 named users, and a Scheduler query job that fires every 10 minutes.
1. Explain how the effective session count reaches 150-190 when only a dozen queries are running, and state exactly what you would change in the data source connection pool configuration. Show the arithmetic.
Read the worked answer
Why the ceiling is blown. Denodo's pool is per VDP server node and per data source, and it is a pool, not a concurrency governor. Two nodes at maximum connections = 200 can each open up to 200 Oracle sessions: a theoretical 400 against a 150-session ceiling. The observed 150-190 means the pool is simply growing to whatever the load asks for until Oracle refuses. The 8-12 executing queries prove the rest are idle-but-held sessions, which is exactly what a missing idle timeout produces: sessions are opened during the 08:00 burst and never returned.
Sizing. Reserve Oracle sessions for the DBA and ETL first, say 20, leaving 130 for Denodo across two nodes: about 60 per node with headroom. So set maximum connections = 60, initial connections = 5-10 (do not pre-open 20 on both nodes for a peak that lasts 20 minutes), and an idle timeout of 5-10 minutes so the 08:00 peak does not permanently occupy the source.
Back-pressure. Set the pool's connection-wait timeout so excess requests queue inside Denodo instead of erroring with Oracle errors, and set a per-user maximum so one analyst running several parallel dashboard tabs cannot consume the pool.
Also: the Scheduler job connects to VDP, not directly to Oracle, so it consumes VDP sessions that in turn consume source sessions - it must be inside the same arithmetic, not treated as free. Enlarging the pool is never the fix: it converts a Denodo queue into an Oracle outage.
2. After the standby failover, why did every node keep failing for ~40 minutes while new sessions worked, and what configuration change prevents a repeat?
Read the worked answer
The pool held sockets that pointed at a database instance that no longer existed. Oracle's failover killed the server-side sessions, but Denodo had no way to know: with no validation, a pooled connection is handed out on trust and only fails when the driver tries to use it. New sessions worked because they were created against the new primary, which is the classic signature of stale pooled connections.
Fix in the data source's connection pool settings:
- Validation query (
SELECT 1 FROM DUALfor Oracle) with validation on borrow, so a dead socket is discarded and replaced before the query reaches the user. - Idle timeout so connections are recycled routinely rather than surviving for days; this bounds the blast radius of any failover, DNS change or firewall idle-drop.
- Maximum connection lifetime if the source enforces one, so Denodo does not sit on connections the database will kill.
Do not treat restarting the VDP server as the remedy - it works, which is precisely why it hides a configuration defect and why this will recur at the next failover, at night, with nobody watching. After enabling validation, re-test with a controlled failover during business hours and confirm the errors are limited to the queries in flight.
3. The platform owner proposes raising `maximum connections` to 300 per node. Evaluate that proposal and describe two alternative levers that actually reduce source concurrency.
Read the worked answer
Reject it. The constraint is Oracle's 150 sessions, not Denodo's pool. Raising the pool to 300 per node means Denodo will happily try to open 600 sessions and the failure mode changes from 'Denodo queue timeouts' to 'ORA-00020 for everyone, including the ETL and the DBA'. Pool size is a fuse, not a performance dial; it can only be sized downward relative to the source limit.
Lever 1 - reduce concurrent demand rather than cap it. The 40 base views are not the problem; the number of simultaneously executing source queries is. Check which derived views fail to delegate and instead pull large result sets into VDP, because those hold a source session for the whole transfer. Where a join is between views of the same Oracle source, make sure it is fully delegated (one session) rather than executed by Denodo (two sessions plus a lot of memory). Each such fix removes sessions from the peak.
Lever 2 - take repeat work off the source. The dashboards almost certainly re-run the same parameterised aggregations. A full cache on the slowest derived view, refreshed by a Scheduler job right after the nightly load, serves the 08:00 burst entirely from the cache engine with zero Oracle sessions. That is far cheaper than a bigger pool, and it also removes the 60-second timeouts.
Measure before and after: sessions at peak, wait time in the pool, and cache hit ratio. If peak sessions do not fall, the load is genuinely concurrent and the answer is a queue plus a capacity conversation with the DBA - not a larger fuse.