CODEWITHSUNDEEP
oracle-databaseconnection-poolingodp.net
baba-dev
baba-dev

Reputation: 2632

Oracle connections not closing

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?

Upvotes: 6

Views: 18316

Answers (5)

cbailiss
cbailiss

Reputation: 1344

A simple solution for this problem (in my case) was to disable connection pooling by specifying Pooling=False; in the client connection string.

Obviously in a high concurrency scenario such as a web server this is not a good solution, but in my case (a long series of batch jobs that execute a new job every few seconds) the overhead of not using pooling was negligible and better than causing 100+ sessions to be left open within Oracle.

As a PS, I had using{} blocks present in all the necessary places in code and was still seeing this issue until I disabled pooling.

Upvotes: 1

Robbie
Robbie

Reputation: 337

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.

Upvotes: 3

Martin Suchanek
Martin Suchanek

Reputation: 3016

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)

Upvotes: 3

GriffeyDog
GriffeyDog

Reputation: 8386

Try wrapping your use of OracleConnection inside of a using block (if you're using C#):

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

That will make sure it gets properly disposed of when you are done using it. OracleConnection and OracleDataReader (as another example) implement IDisposable, so should be used within a using statement.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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.

Upvotes: 1

Related Questions