oracle ODP.NET：使用连接池避免连接超时

ODP.NET: Avoiding Connection Timeouts with Connection Pooling

提问by Andy Jacobs

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).

在一个站点，我可以使用 SQL Developer 连接到 Oracle 数据库，让它闲置很长时间（例如，> 60 分钟），然后返回，这很好。在第二个站点，如果它保持空闲超过 5-10 分钟（我没有准确计算过），它会使 SQL Developer 处于新操作将超时的状态，我需要手动“断开连接”，然后按顺序重新连接做任何有用的事情。这似乎是第二个站点的连接超时，我不知道是什么原因造成的（我想知道如何将其关闭，尽管这不是我的主要问题）。

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.

我的程序使用 ODP.NET 并处理突然出现的数据。每 30 分钟（为了讨论），它将获得一堆数据进行处理，这将涉及许多重复连接。它还使用连接池。我已将连接池设置为使用 5 分钟的生命周期。

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.

我在第二个站点（而不是第一个站点）看到的是我的程序将在每次数据突增开始时获得连接超时异常（例如，ORA-03113）。我相信正在发生的是，在数据激增期间，连接池按设计使用。在spurt结束时勾选了“Connection Lifetime”，连接不是太老，所以留在连接池中。然后，30 分钟后，当新数据到达时，连接从池中取出（并且不检查生命周期或超时）并使用，并且超时，就像我在 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.

如何避免连接超时但仍能在突增期间利用连接池？从文档（和我的经验）看来，只有当它进入池时才检查连接的 Lifetime，而不是在它出来时。

回答by DanK

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.

TL;DR 总结是 ODP.NET 驱动程序和 .NET 实现不能很好地相互配合，所以你的工厂连接池设置的正常运行似乎并不像你期望的那样工作。

• Connection Lifetimeis the primary offender. I'm not sure if this blogis 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_TIMEset to a value in your Oracle user profile (and not UNLIMITED) then eventually these long running idle parameters will be SNIPEDby 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 SNIPEDconnections 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 SNIPEDsession it removes it from the .NET connection pool.

• Connection Lifetime是罪魁祸首。我不确定这个博客是否仍然适用，因为它已经很老了，但我还没有找到任何文件来反驳它，它似乎验证了我所看到的行为。根据该博客，Connection Lifetime 确实会按预期终止旧会话，但仅在对数据库进行调用时才会检查此参数。因此，换句话说，.NET 永远不会杀死长时间运行的空闲会话。
• 如果您IDLE_TIME在 Oracle 用户配置文件中设置了一个值（而不是UNLIMITED），那么最终这些长时间运行的空闲参数将由SNIPED数据库使用。这最终可能会导致 .NET 方面出现问题，因为除非您明确检查您的连接是否仍然打开，否则 .NET 将提供这些SNIPED连接，就好像它们仍然可用一样（从而引发上述超时 ORA 错误）。
• 解决此问题的技巧是确保Data Validation=True;您的连接字符串中有。这可确保 .NET 在为下一次服务调用之前的连接提供服务之前检查会话连接。当此验证看到一个SNIPED会话时，它会将其从 .NET 连接池中删除。

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 Lifetimeto do it's job then he would never see these timeouts in that database.

鉴于此信息，OP 的原始问题很可能仅出现在一个站点中，原因是不同数据库设置和/或 .NET 调用数据库的频率不同。他可能在两种环境中都遇到了问题，但是如果一个环境中的用户频繁地进行调用Connection Lifetime以完成其工作，那么他将永远不会在该数据库中看到这些超时。

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 = Trueparameter you should hopefully be able to work around this problem.

现在我仍然没有想出如何在任何 Oracle IDLE_TIME sniping 发生之前终止 .NET 中的空闲连接，但只要您使用该Data Validation = True参数，您应该希望能够解决这个问题。

回答by victor6510

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.

如果 5 分钟 Lifetime 设置在第一个站点中运行良好，那么我认为这可能是由于有人在 Oracle 服务器端的 Profile 中设置了空闲会话超时造成的。

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.

然而，使用 5 min Lifetime 设置，当您的突增变得更大时，您可能仍然会超时，因为当您在下一次突增中将连接返回到池时，它们将被破坏。然后池将忙于创建和删除连接，并且在负载非常大时可能会导致连接超时。

回答by Tony Kh

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

您可以通过将OracleCommand.ConnectionTimeout属性设置为 0来指定无限超时。在这种情况下，不会有超时（至少在客户端）。

ConnectionPool is used in this case too.

在这种情况下也使用了 ConnectionPool。