Oracle connections do not close

advertisements

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?


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)