We are having an issue with connections staying idle in Oracle. To give you some background, our users connect to Denodo which in turn has a data source that connects to Oracle. This data source works with one user name and password and creates a pool. The pool has an initial size of 4 and a Maximum number of active connections of 20.
Connections start coming in from clients using JDBC, ODBC and so on. Some clients are other server requesting data (Spotfire and BusinessObjects), and others are just regular users that have developed scripts in R, python, C# and others. They can also connect with tools such as DBeaver. The Oracle user has settings to maintain up to 100 connections idle.
Now, users connect with their scripts and they have code (that we have checked) that opens a connection to Denodo, gets the data through a query, gets the data returned, and closes the connection to Denodo. Denodo in turns does the same and opens the connection to Oracle, passes the query from the client to Oracle, gets the data and relays it back to the client. And this is the part we are not too sure about. We were expecting Denodo to close the connection to Oracle and it does not. The connection stays open in Oracle and shows as idle. Eventually we have enough connections idle to fill up the quota set up for the User (100).
Based on this, we have done some tune up in Denodo with the connection to Oracle and applied these settings to the connection:
FETCHSIZE = 10000
BATCHINSERTSIZE = 200
VALIDATIONQUERY = 'SELECT COUNT(*) FROM SYS.DUAL'
INITIALSIZE = 4 #initial size of pool
MAXIDLE = 25 #max number of idle connections
MINIDLE = 5 #min number of idle connections
MAXACTIVE = 20 #max active in the pool
EXHAUSTEDACTION = 1
TESTONBORROW = true
TESTONRETURN = false
TESTWHILEIDLE = false
TIMEBETWEENEVICTION = 300000 #time between evictions in milliseconds
NUMTESTPEREVICTION = 10 #10 connections to be evaluated for eviction
MINEVICTABLETIME = 900000 #min evictable time in milliseconds
POOLPREPAREDSTATEMENTS = false
MAXSLEEPINGPS = 4
INITIALCAPACITYPS = 8
After applying this setting, we thought it would clear the idle connections. Problem is that it has not. You can see the connections start creeping in and it eventually fills up again and does not allow for any other connection.
What I would like to see is Denodo open the connection that it needs, use it and release it. Not keep a connection in Idle in Oracle. Oracle connections do not seem to be evicted ever and eventually they reach 100 again.
Any help would be appreciated