Oracle connections not closing

Question

We have ASP.NET app that connects to oracle database with odp.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.

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.

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?

Answer 1

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

See the code sample in the comments here:

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)

Answer 2

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.

I'm using the ODP.NET Oracle.DataAccess.Client namespace V4.112.3.60 for 64 bit.

I have all of my code in using statements (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 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)

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.

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

So using the ClearAllPools method seems to work, but I know it's not the ideal solution.

Answer 3

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.