ODP.NET: Avoiding Connection Timeouts with Connection Pooling

Question

At one site I can connect to the Oracle Database with SQL Developer, leave it idle for a long time (e.g., > 60 minutes), and return, and it's fine. At a second site, if it stays idle for more than 5-10 minutes (I haven't counted exactly), it leaves SQL Developer in a state where new operations will timeout and I need to manually "Disconnect" and then reconnect in order to do anything useful. This seems to be a connection timeout at the second site, and I don't know what causes it (and I'd like to know how to turn it off, although this is not my main question).

My program uses ODP.NET and processes data that comes in spurts. Every 30 minutes (for the sake of discussion) it will get a bunch of data to process which will involve a number of repeated connections. It also uses Connection Pooling. I've set the Connection Pool to use a Lifetime of 5 minutes.

What I'm seeing at the second site (and not at the first) is my program will get connection timeout exceptions (e.g., ORA-03113) at the beginning of each spurt of data. What I believe is happening is that during the spurt of data, the connection pool is used as designed. At the end of the spurt the "Connection Lifetime" is checked, and the connection is not too old, so it is left in the connection pool. Then, 30 minutes later when new data arrives, the connection is taken out of the pool (and not checked for a lifetime or timeout) and used, and is timeing out, just as I see in SQL Developer.

How can I avoid the connection timeout but still take advantage of the Connection Pool during the spurts? It seems from the documentation (and my experience) that the connection is only checked for Lifetime when it goes into the pool, and not when it comes out.

Answer 1

This is a really old question but I've been experiencing some similar issues with an application and so I think some of the information might help anyone else who trips across this question.

The TL;DR summary is that ODP.NET drivers and the .NET implementation don't play nicely with each other and so your normal run of the mill connection pooling settings don't seem to work exactly how you would expect.

• Connection Lifetime is the primary offender. I'm not sure if this blog is still applicable as it is quite old but I haven't found any documentation yet to refute it and it appears to verify the behavior that I am seeing. According to the blog, Connection Lifetime does kill an older session as expected but the check against this parameter only happens when a call is made to the database. So in other words, long running idle sessions will never be killed by .NET.
• If you have IDLE_TIME set to a value in your Oracle user profile (and not UNLIMITED) then eventually these long running idle parameters will be SNIPED by the database. This can end up causing problems on the .NET side because unless you are explicitly checking that your connections are still open, .NET is going to serve up these SNIPED connections as if they are still available (thus throwing the above timeout ORA error).
• The trick around this problem is to make sure that you have Data Validation=True; in your connection string. This ensures that .NET will check for session connectivity before it serves the connection up to the next service call. When this validation sees a SNIPED session it removes it from the .NET connection pool.

Given this information, it is most likely that the OP's original problem was only appearing in the one site from a combination of different database settings and/or the frequency of the .NET calls to the database. He might have had the problem in both environments but if users in one environment were making calls frequently enough for Connection Lifetime to do it's job then he would never see these timeouts in that database.

Now I still haven't figured out how to kill an idle connection in .NET before any Oracle IDLE_TIME sniping takes place but as long as you use that Data Validation = True parameter you should hopefully be able to work around this problem.

Answer 2

If the 5 minutes Lifetime setting is doing well in first site, then I think that this might be caused by someone setting the idle session timeout in a Profile in the Oracle server side.

Yet with the 5 min Lifetime setting you might still hit timeout when your spurt becomes bigger, because when you return connections to the pool in next spurt they'll get destroyed. The pool will then be busy creating and deleting connections and might lead to connection timeout when the load is really big.