Oracle 连接未关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8937753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle connections not closing
提问by baba-dev
We have ASP.NET app that connects to oracle database with odp.net.
我们有使用 odp.net 连接到 oracle 数据库的 ASP.NET 应用程序。
Lately we started to experienced some performance issues. It seems that Oracle connections do not close and eventually pile up until it crash our website.
最近我们开始遇到一些性能问题。似乎 Oracle 连接不会关闭并最终堆积起来，直到它使我们的网站崩溃。
As a first step we did a code review and we made sure that we close all open connections after executing.
作为第一步，我们进行了代码，并确保在执行后关闭所有打开的连接。
OracleConnection cn = Helpers.ConnectToDB();
try
{
cn.Open();
//do somtehing
}
catch (Exception ex)
{
//log error
}
finally
{
cn.Close();
cn.Dispose();
}
but that didn't help, every several hours the connections are piling up and crash our website.
但这无济于事，每隔几个小时，连接就会堆积起来，使我们的网站崩溃。
Here is the connections log from yesterday:
这是昨天的连接日志：
TO_CHAR(DATE_TIME,'DD/MM/YYYY MACHINE STATUS CONNECTIONS
19/01/2012 14:40:03 WORKGROUP\OTH-IIS-1 ACTIVE 1
19/01/2012 14:38:00 WORKGROUP\OTH-IIS-1 ACTIVE 2
19/01/2012 14:35:57 WORKGROUP\OTH-IIS-1 ACTIVE 2
19/01/2012 14:34:55 WORKGROUP\OTH-IIS-1 ACTIVE 28
19/01/2012 14:33:54 WORKGROUP\OTH-IIS-1 ACTIVE 26
19/01/2012 14:31:51 WORKGROUP\OTH-IIS-1 ACTIVE 34
19/01/2012 14:30:49 WORKGROUP\OTH-IIS-1 ACTIVE 96
19/01/2012 14:29:47 WORKGROUP\OTH-IIS-1 ACTIVE 73
19/01/2012 14:28:46 WORKGROUP\OTH-IIS-1 ACTIVE 119
19/01/2012 14:27:44 WORKGROUP\OTH-IIS-1 ACTIVE 161
19/01/2012 14:26:43 WORKGROUP\OTH-IIS-1 ACTIVE 152
19/01/2012 14:25:41 WORKGROUP\OTH-IIS-1 ACTIVE 109
19/01/2012 14:24:40 WORKGROUP\OTH-IIS-1 ACTIVE 74
19/01/2012 14:23:38 WORKGROUP\OTH-IIS-1 ACTIVE 26
19/01/2012 14:22:36 WORKGROUP\OTH-IIS-1 ACTIVE 2
19/01/2012 14:21:35 WORKGROUP\OTH-IIS-1 ACTIVE 2
Crash point occurred at 14:27:44 and after restarting the application the connections started to drop down.
崩溃点发生在 14:27:44，重新启动应用程序后，连接开始下降。
the connection string we using is:
我们使用的连接字符串是：
<add name="OracleRead" connectionString="Data Source=xxx;User Id=yyy;Password=zzz;Max Pool Size=250;Connection Timeout=160;" providerName="Oracle.DataAccess"/>
So what is the problem here?
那么这里的问题是什么？
Do we need to define or change one of these properties:
我们是否需要定义或更改以下属性之一：
Connection Lifetime, Decr Pool Size, Max Pool Size, Min Pool Size?
What is the recommended settings in this situation?
在这种情况下推荐的设置是什么？
回答by Martin Suchanek
You need to explicitly dispose all Oracle.DataAccess objects, including Connections, Commands, and Parameters.
您需要显式处理所有 Oracle.DataAccess 对象，包括连接、命令和参数。
See the code sample in the comments here:
请参阅此处注释中的代码示例：
https://nhibernate.jira.com/browse/NH-278
https://nhibernate.jira.com/browse/NH-278
A couple other notes:
其他一些注意事项：
- Prefer the using keyword, as that will guarantee disposal even in exceptional cases
- The ODP Paramter object is special (compared to the regular ADO.NET parameter contract) because it too requires explicit disposal (whereas, for instance, the SQL Server version does not)
- 更喜欢 using 关键字，因为即使在特殊情况下也能保证处理
- ODP 参数对象是特殊的（与常规的 ADO.NET 参数约定相比），因为它也需要显式处理（而例如，SQL Server 版本则不需要）
回答by Robbie
I know this question is quite old, but I have found a solution which seems to work for me.
我知道这个问题已经很老了，但我找到了一个似乎对我有用的解决方案。
My solution is calling a ASHX handler which then returns an image, on average this service is called between 10-14 times per page load of a certain page.
我的解决方案是调用一个 ASHX 处理程序，然后返回一个图像，平均而言，该服务在特定页面的每个页面加载之间被调用 10-14 次。
I'm using the ODP.NET Oracle.DataAccess.Client namespace V4.112.3.60 for 64 bit.
我在 64 位使用 ODP.NET Oracle.DataAccess.Client 命名空间 V4.112.3.60。
I have all of my code in using statements (obfuscation here):
我的所有代码都在 using 语句中（此处进行了混淆）：
using (OracleConnection conn = new OracleConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["####"].ConnectionString))
{
using (OracleCommand cmd = new OracleCommand(query, conn))
{
OracleParameter p = new OracleParameter("####", OracleDbType.Varchar2, 10);
p.Direction = ParameterDirection.Input;
p.Value = val;
cmd.Parameters.Add(p);
conn.Open();
using(OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (reader.HasRows)
{
while (reader.Read())
{
OracleBlob lob = reader.GetOracleBlob(0);
//OracleLob lob = reader.GetOracleLob(0);
srcImage = new Bitmap(lob);
}
newImage = resizeImage(srcImage, new Size(120, 150));
newImage.Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
}
else
{
srcImage = new Bitmap("Images/none.jpg");
newImage = resizeImage(srcImage, new Size(120, 150));
newImage.Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
ProcessError(ref context, 500);
}
}
p.Dispose();
}
}
I tried many things:
我尝试了很多事情：
- Checking other connections open at the same time
- Re-wrote an SQL data source control so that I had more control over connections
- Using System.Data.OracleClient)
- 检查其他连接同时打开
- 重新编写了一个 SQL 数据源控件，以便我对连接有更多的控制
- 使用 System.Data.OracleClient)
But when it came to stepping through the code I found that sometimes the code wouldn't reach the end of the using block and the next request would come into the handler before it could reach the end (I'm guessing something to do with max requests to handler?) this resulted in some sessions being left open in V$SESSION which I had to manually close.
但是当涉及到单步执行代码时，我发现有时代码不会到达 using 块的末尾，而下一个请求会在到达末尾之前进入处理程序（我猜与 max请求处理程序？）这导致一些会话在 V$SESSION 中保持打开状态，我不得不手动关闭。
I came across this bit of code:
我遇到了这段代码：
OracleConnection.ClearAllPools();
And tried running it, although the sessions would be left open by the handler, at least these would be closed off by this code, currently it runs at the end of the using block for the OracleConnection (so every time the service is called it clears the pools, that's hoping that the handler manages to execute that far!).
并尝试运行它，尽管处理程序将保持会话打开状态，但至少这些会话将被此代码关闭，目前它在 OracleConnection 的 using 块的末尾运行（因此每次调用服务时它都会清除池，希望处理程序能够执行那么远！）。
So using the ClearAllPools method seems to work, but I know it's not the ideal solution.
所以使用 ClearAllPools 方法似乎有效，但我知道这不是理想的解决方案。
回答by Joel Coehoorn
Make sure to wrap all connections in a try/finally block. It's not enough to just call .Close() for every .Open(). You must place the .Close() call in the finally block. The easiest way to do this is to create your connections with a using block.
确保将所有连接包装在 try/finally 块中。仅仅为每个 .Open() 调用 .Close() 是不够的。您必须将 .Close() 调用放在 finally 块中。最简单的方法是使用 using 块创建连接。
回答by GriffeyDog
Try wrapping your use of OracleConnectioninside of a using block (if you're using C#):
尝试将您的 use 包装OracleConnection在 using 块内（如果您使用的是 C#）：
using (OracleConnection conn = new OracleConnection(connectionString))
{
...
}
That will make sure it gets properly disposed of when you are done using it. OracleConnectionand OracleDataReader(as another example) implement IDisposable, so should be used within a usingstatement.
这将确保在您使用完后得到妥善处理。OracleConnection和OracleDataReader（作为另一个例子） implement IDisposable，所以应该在using语句中使用。
