5

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.

Jonny Bekkum
  • 93
  • 1
  • 6
  • In my understanding, the connection pooling is done in the client side (=driver), and it is enforcing this kind of 'hard reset' on the connections, so theoretically you always have the `min pool size` statisfied, just sometimes they are reset for some reason. The other driver simply does not do this. As we are speaking about Oracle, I personally doubt you can change this behaviour. If this affects performance for you, use the other driver. But I hope someone more experienced comes this way :) – Koshinae Jan 22 '16 at 09:09
  • I can't reproduce this. I enable ODP.NET counters, and use your connect string, I open 5 connections, do some work on them and close and dispose them all, and then monitor the HardDisconnectsPerSecond ODP.NET counter. Even for 10 minutes I see no Hard Disconnects. Are you doing something different than what I did? – Christian Shay Jan 26 '16 at 23:43
  • See my update in OP. I believe you must be doing continuous work as the connection pooling cleanup kicks in when connections is returned to the pool. – Jonny Bekkum Jan 27 '16 at 22:13
  • Do I understand you correctly: You are NOT monitoring ODP.NET performance counter HardDisconnectsPerSecond. Instead you are using SQL to monitor connections? – Christian Shay Jan 28 '16 at 09:52
  • I do both. I get both HardDisconnets in PerfMon and using the SQL I can also watch as the ConnectionPool closes all connection and then recreates MIN POOL SIZE connections on the next create connection from pool. This coincides with the HardConnects in PerfMon and occurs every 3 minutes. – Jonny Bekkum Jan 28 '16 at 12:29
  • @ChristianShay I can give live demo with Lync/Skype if you wish. My id is jonny(a)webcruiter.no – Jonny Bekkum Jan 28 '16 at 12:39
  • I will try out your code and see what happens and will let you know. I tested something similar already and could not reproduce it. – Christian Shay Jan 28 '16 at 18:18
  • Follow the performance counter HardConnectsPerSecond and the select on v$session. Can't remember if I also get HardDisconnectsPerSecond – Jonny Bekkum Jan 28 '16 at 20:03

2 Answers2

1

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.

b_levitt
  • 7,059
  • 2
  • 41
  • 56
  • See my update and sample app. I ran the sample app with MIN POOL SIZE=0 and CONNNECTION LIFETIME = 86400 (=24 Hours) and even with these settings and running a SQL every 3 seconds the connection pool will close that connection every 3 minutes. From what I can see ODP NET does not honor the DECR POOL SIZE and CONNECTION LIFETIME setting and only allows connection to stay alive only if they are busy when the cleanup is executing – Jonny Bekkum Jan 28 '16 at 07:15
0

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.

Steffen Karge
  • 1