Oracle UCP PoolDataSource, unexpected behaviour

Question

We have a REST-service java-app that uses

oracle.ucp.jdbc.PoolDataSource 
from:  com.oracle.ojdbc:ojdbc10:jar:19.3.0.0

Not very high traffic, so often there is only a single request running at a time, while sometimes there are a few requests running in parallel. Each request typically takes from 0.2 secs up to ca 10 secs to execute, depending on which method is called.

The issue we have, seems to be that we are going through a firewall, that will drop/disconnect an open but idle connection after 60 mins.

On the PoolDataSource, I have set these properties:

pds.setInitialPoolSize(6);
pds.setMinPoolSize(6);
pds.setMaxPoolSize(12);
pds.setTimeoutCheckInterval(20);
pds.setInactiveConnectionTimeout(60);
pds.setTimeToLiveConnectionTimeout(60);

Note, the 3 last property-values are in seconds (thus, waaaaay less than the 60 mins limit in the firewall) so with these settings I would expect no issue with the firewall, since (from my understanding of the docs) every connection anyways should be invalidated (closed+recreated) within a minute or two, shouldn't they?

But still, we sometimes see:

SQLRecoverableException: Closed Connection

in the logs, and the request will fail, which got me to look into the details of how the pool behaves...

Monitoring v$session, while I run a test-case with up to 3 parallel request, I can see that, as expected, 6 sessions are created when starting the app.

But -- the pool will only invoke the three lowest available (first created) connections over and over again.

Therefore if, after more than 60 mins, 4 or more parallel request are invoked, the pool will then give out connections much older than specified by the params:

pds.setInactiveConnectionTimeout(60);
pds.setTimeToLiveConnectionTimeout(60);

which will then cause SQLRecoverableException: Closed Connection due to the firewall rule.

So I wonder, is there a way to force "rotation" of the available connections (like a loadbalancer), so all of them get used equally much?

Or some other reasonable way to resolve this issue?

---

EDIT: hmm... after another test, i now realized one can probably just set:

pds.setInitialPoolSize(0);
pds.setMinPoolSize(0); 

then the pool can go down to 0 connections within a couple of minutes, so no connections will ever go stale even if no requests or only a single request come in for a while...

But that partially kind of defeats the purpose of the connection-pool...

So still, it seems a bit weird... doesnt seem like the intended behaviour from the documentation:

"setInactiveConnectionTimeout: Sets the inactive connection timeout. This timeout determines how long an available connection remains in the connection pool before it is removed from the pool."

This is then only true as long as the number of parallel incoming requests never falls below minPoolSize ... confusing, and not mentioned in the docs, what I can see... (and actually, if this is true, minPoolSize becomes truly meaningless)

Anyone can shed some light?

Answer 1

Firstly, I would recommend you to use the latest 19c release jdbc/ucp jars, 19.3 is very old one. The latest is 19.21 which you can download from maven or from otn. https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

I think the issue you described above occurs because UCP does not shrink its pool size below min and it ignores inactive connection timeout if the pool has only min pool size of connections. If it shrinks below min pool size due to inactive timer then there will be a lot of new open and closed connections even though the pool is idle. If firewall is closing the idle connections, I think for that you can enable tcp keepAlive by setting JDBC connection property "oracle.net.keepAlive" value to "true". You can pass in this property in UCP datasource by calling setConnectionProperties(props) API.

PoolDataSouce pds = .....
Properties props = new Properties();
props.setProperty("oracle.net.keepAlive", "true");
pds.setConnectionProperties(props);