ODP.NET Managed Connection Closes / Opens Every 3 Minutes

Question

We use the latest official ODP.NET Managed (Posted: 2015-10-14 | Version: 12.1.2400) from Oracle to the Oracle 12 database configuration (without RAC), and we cannot maintain database connections for more typically <3 minutes.

Our connection string indicates:

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

and we 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 observe the counters for HardConnect / HardDisconnects, we collect that the connection pool closes and resumes 5 connections every 3 minutes, and this is not what we expected.

We have this behavior both in a webapp that uses EF6 for DataAccess and in an application that does not have ORM (just plain old SQL).

According to Oracle Documentation :

The join service closes connections when they are not in use; connections close every 3 minutes. The Decr pool size attribute of the ConnectionString property provides a pooling service for the maximum number of connections that can be closed every 3 minutes.

For me, as long as the connection is within the service life, there should be a MIN in ConnectionPool. BALLET SIZE of a valid compound for a longer time than 3 minutes.

We have another application that uses the Devart Oracle driver, and this driver combines connections that remain alive for a long time.

Has anyone else evaluated this “incorrect behavior” of ConnectionPool in the ODP.NET managed driver and found a solution? Or could this be a bug in ConnectionPool for ODP.NET?

UPDATE 2016.01.27:

I added a demo application to my github account to demonstrate the problem:

https://github.com/jonnybee/OraConnTest

This is a small winforms application where you add a connection string and click a button to start a background worker that launches "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 USER ID, PASSWORD and DATA SOURCE.

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

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 in logon_time desc

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

Answer 1

I agree with your assessment of how it should work, but pooling in ODP.net is a bit weird. The key point here is that the connection lifetime is only respected “when the application closes the connection”, where the decr pool size seems to have its own thread. I have no idea why they did this - checking only when the pool returns creates a senario where you can pull the dead connection (terminated by firewall timeouts) from the pool.

I set the minimum pool size = 0. This ensures that the pool is empty when the application is down. I almost guarantee that you will not notice the difference if you are on a very slow network or already have an overloaded instance of oracle. Pooling is important, but a connection can be established in just a few milliseconds.

The only thing that pushes me away is that all 5 connections are closed - the default decr pool size is 1.