ODP.NET: Eliminating Connection Pool Connection Timeouts

Question

On one site, I can connect to the Oracle database using SQL Developer, stand idle for a long time (for example> 60 minutes) and return, and this is normal. On the second site, if it remains inactive for more than 5-10 minutes (I didn’t take it into account exactly), it leaves SQL Developer in a state when new operations will time out, and I need to manually “Disable” and then reconnect to the order to do something useful. This is apparently the connection timeout on the second site, and I don't know what causes it (and I would like to know how to disable it, although this is not my main question).

My program uses ODP.NET and processes the data that goes into spurts. Every 30 minutes (for discussion), he will receive a bunch of data for processing, which will be associated with several repeating connections. It also uses Connection Pooling. I set up a connection pool for use within 5 minutes.

What I see on the second site (and not on the first) is that my program will receive connection timeout exceptions (for example, ORA-03113) at the beginning of each data burst. What I think is happening is that during the distribution of the data, the connection pool is used as developed. At the end of the burst, the “Connection time” is checked, and the connection is not too old, so it remains in the connection pool. Then, 30 minutes after the new data arrives, the connection is taken out of the pool (and not checked for the lifetime or timeout) and used, and the time expires, as I see in SQL Developer.

How can I avoid the connection timeout, but still use the connection pool during bursts? From the documentation (and my experience) it is clear that the connection is checked only for the lifetime, when it enters the pool, and not when it appears.

Answer 1

If setting up for 5 minutes in standby mode works on the first site, I think this could be because someone set the session idle timeout in the profile on the server side of Oracle.

However, with a 5-minute life time setting, you can still use the timeout when your jerk gets larger, because when you return to the pool in the next jerk, they will be destroyed. Then the pool will be busy creating and deleting connections and may lead to a connection timeout when the load is really large.

Answer 2

This is a really old question, but I had some similar problems with the application, so I think some of the information may help someone who is posting on this issue.

Summary of TL; DR lies in the fact that the ODP.NET drivers and the .NET implementation do not mix well with each other, and therefore your normal startup of the connection pool settings in the mills does not work exactly as you expected.

• Communication life is a major offender. I'm not sure if this blog is still applicable as it is quite old, but I have not yet found any documentation to disprove it. seems to check the behavior I see. According to the blog, Connection Lifetime kills the old session as expected, but checking this option only occurs when it is called into the database . In other words, long idle sessions will never be killed by .NET.
• If you have IDLE_TIME set to your Oracle user profile (and not UNLIMITED ), then ultimately these long unused parameters will be SNIPED in the database. This can lead to problems on the .NET side, because if you do not explicitly verify that your connections are still open, .NET will serve these SNIPED connections as if they were still available (thus throwing the above error ORA).
• The focus of this problem is to make sure you have Data Validation=True; in your connection string. This ensures that .NET verifies the connection to the session before serving the connection until the next service call. When this check sees the SNIPED session, it removes it from the .NET connection pool.

Given this information, it is most likely that the original OP problem appeared on only one site from a combination of various database parameters and / or .NET call frequency in the database. He may have had a problem in both environments, but if users in the same environment often made calls to Connection Lifetime to complete this task, he would never see these timeouts in this database.

Now I still have not figured out how to kill an idle connection in .NET before sniping Oracle IDLE_TIME, but as long as you use this parameter Data Validation = True , you can probably work around this problem.

Answer 3

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

In this case, ConnectionPool is also used.