ODP.NET Managed ConnectionPool closes/opens every 3 minutes

Question

We are using the latest Official ODP.NET Managed (Published: 2015-10-14 | Version: 12.1.2400) from Oracle to a Oracle 12 database (non RAC) configuration and we are unable to keep database connections alive for more than typically < 3 minutes.

Our connection string specifies:

MAX POOL SIZE=10;MIN POOL SIZE=5;INCR POOL SIZE=1;

and we have also tried

CONNECTION LIFETIME=90000;MAX POOL SIZE=10;MIN POOL SIZE=5;INCR POOL SIZE=1;

When we use PerfMon on the server and watch the counters for HardConnects/HardDisconnects we se that the connection pool closes and reopen 5 connections every 3 minutes and this is not what we expected.

We have this behavior in both a webapp that uses EF6 for DataAccess and an app that has no ORM (just plain old SQL).

According to the Oracle Documentation:

The connection pooling service closes connections when they are not used; connections are closed every 3 minutes. The Decr Pool Size attribute of the ConnectionString property provides connection pooling service for the maximum number of connections that can be closed every 3 minutes.

To me - as long as the connection is within the lifetime limit there should be MIN POOL SIZE of valid connection for a much longer duration than 3 minutes in the ConnectionPool.

We have another app that use Devart's Oracle driver and this driver har pooled connections that stays alive for a long time.

Has anyone else seeen this "misbehavior" of the ConnectionPool in ODP.NET Managed Driver and found a solution? Or could this be a bug in the ConnectionPool of ODP.NET Managed?

UPDATE 2016.01.27:

I have added a demo app on my github account to demonstrate the issue:

https://github.com/jonnybee/OraConnTest

This is just a small winforms app where you add the connection string and click the button to start a background worker that runs "SELECT 'OK' FROM DUAL" every 3 seconds.

My connection string contains: POOLING=True;MAX POOL SIZE=10;DECR POOL SIZE=1;CONNECTION LIFETIME=86400;INCR POOL SIZE=1;MIN POOL SIZE=5 + you must add the USER ID, PASSWORD and DATA SOURCE.

Every 3 minutes you will see that 5 existing connections are closed and 5 new connections (MIN POOL SIZE setting) is created.

Run this SQL to see the actual connections: select sid, logon_time, prev_exec_start, wait_time_micro/1000 from v$session where program like '%OraWinApp%' order by logon_time desc

While the program and perfmon is running and you will see this behavior as the old connections gets closed and the new connections with new login_time is created.

Answer 1

We faced the same behavoir when using the connection pool with ODP.Net. The database connections belonging to the pool where closed and reopend every 3 minutes.

Turned out, the reason for that must be a bug in NuGet-Package Oracle.ManagedDataAccess version 12.1

After updating NuGet-Packages to 12.2 this behavoir was no longer observable and the connection pool works perfectly fine.

Answer 2

I agree with your assessment on how it should work, but connection pooling in ODP.net is a bit strange. The key here is that connection lifetime is only obeyed "when the application closes a connection" where decr pool size seems to have thread of it's own. I have no idea why they did that - validating only when returning the pool creates a senario where you can pull a dead connection (terminated by firewall timeouts) out of the pool.

I actually set min pool size=0. This makes sure that the pool is empty when the app is idle. I can almost guarantee that you will not notice the difference unless you are on a very slow network or already have an already overloaded oracle instance. Connection pooling is important, but a connection can generally be established in just a few milliseconds.

The only thing throwing me off here is that all 5 connections are closed - decr pool size defaults to 1.