Oracle connections do not close

Question

We have an ASP.NET application that connects to the oracle database using odp.net.

Recently, we began to experience some performance issues. It seems that Oracle connections do not close and eventually accumulate until they destroy our website.

As a first step, we reviewed the code, and we made sure that after all open connections are completed, we close.

OracleConnection cn = Helpers.ConnectToDB(); try { cn.Open(); //do somtehing } catch (Exception ex) { //log error } finally { cn.Close(); cn.Dispose(); } 

but it didn’t help, every few hours connections pile up and break our site.

Here is the connection 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 

The failure point occurred at 14:27:44, and after restarting the application, the connections started to drop out.

connection string used:

 <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?

Do you need to define or modify one of these properties:

 Connection Lifetime, Decr Pool Size, Max Pool Size, Min Pool Size? 

What are the recommended settings in this situation?

Answer 1

You need to explicitly place all Oracle.DataAccess objects, including Connections, Commands, and Parameters.

See sample code in the comments here:

https://nhibernate.jira.com/browse/NH-278

A few other notes:

• Prefer to use the keyword as this ensures removal even in exceptional cases.
• The ODP parameter object is special (compared to the usual ADO.NET contract), since it also requires explicit deletion (whereas, for example, the version of SQL Server does not work)

Answer 2

I know this question is pretty old, but I found a solution that seems to work for me.

My solution calls the ASHX handler, which then returns the image; on average, this service is called between 10-14 times to load a page on a specific page.

I am using the ODP.NET Oracle.DataAccess.Client V4.112.3.60 namespace for 64 bits.

I have all the code in using operators (obfuscation here):

 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 have tried many things:

• Verify that other connections open simultaneously
• Re-wrote the SQL data source control so I have more control over the connections
• Using System.Data.OracleClient)

But when it came to passing the code, I found that sometimes the code did not reach the end of the used block, and the next request would come to the handler before it could reach the end (I guess something to do with the maximum handler requests?) , this led to some sessions remaining open in V $ SESSION, which I had to close manually.

I came across this bit of code:

 OracleConnection.ClearAllPools(); 

And I tried to start it, although the sessions will remain open by the handler, at least they will be closed by this code, currently it starts at the end of the use block for OracleConnection (therefore, every time the service clears the pools, which hopes that the handler will be able to execute it is far!).

So using the ClearAllPools method seems to work, but I know this is not an ideal solution.

Answer 3

Be sure to wrap all connections in a try / finally block. This is not enough to just call .Close () for each .Open (). You must put the .Close () call in the finally block. The easiest way to do this is to create your connections using a block.

Answer 4

Try to wrap using OracleConnection inside the block you are using (if you are using C #):

 using (OracleConnection conn = new OracleConnection(connectionString)) { ... } 

This will make sure that it is disposed of correctly when you finish using it. OracleConnection and OracleDataReader (as another example) implement IDisposable , so they should be used in the using statement.