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.

Allowedly

not knowledgeable enough in these matters, but we had a very similar problem (changed data centers). the new data center would kill any port that was open w/o activity for anything longer than X minutes (we had the EXACT same problem as you did in SQL Developer and other long running processes. We balked to the hosting co. and they ended up not killing the ora ports (but they curtly recommended that we implement a pulse just for ora connections which was rejected out-of-hand) but it required work on their part. good luck! – Wandering

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.

Loreenlorelei

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.

Daren

Answer 3

ODP.NET by default creates and holds 1 connection open in the connection pool at all times. Oracle Server can be configured with a server side connection timeout that will close idle connections after a period of time. If your application is idle for a long time this might be the problem, because when the Server closes a connection on its own the ODP.NET client doesn't know that connection is closed. The client still has that connection in the pool and will try to use it and then you will get an error.

There are two solutions.

1. Do not set a server side idle timeout for connections when using ODP.NET connection pooling. It really doesn't make much sense to have the server drop idle connections if you are using connection pooling.
2. If you can't do #1 then you can use the ODP.NET connection settings to set MinPoolSize=0. Having MinPoolSize=0 means that when the application is idle the ODP.NET client will let the pool drop to 0 connections. This could impact performance when the application has to do work again because it will not have an available connection to handle the initial request and will have to open one, but if your application does large bursts of work with long idle periods in between then it might make sense to do this.

Perform

Answer 4

You may specify infinite timeout by setting OracleCommand.ConnectionTimeout property to 0. In this case there will be no timeout (at least on client-side).

ConnectionPool is used in this case too.

Neuropsychiatry

Thanks Tony, but I believe this setting tells Oracle how long to wait for a response. The problem I'm seeing is that the connection gets into a state where it will never get a response. So this setting would avoid the exception, but my program would still never get a response - so I don't view this as a solution. – Allowedly