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?

Warton

you connection doesn't actually have "Pooling=True;", so is it even using connection pooling? have you tried it with "Pooling=True;" in the connection string. – Behah

Pooling=True is the default value – Warton

Did you manage to solve this? – Muttra

Have come up with a half way solution, basically clears up remaining sessions, see my answer. – Muttra

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)

Claptrap

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.

Muttra

Probably worth saying that this slows down the retrieval of data from the handler, probably by about a second. I'm guessing this is because it has to create a new session because the last one was closed because of the where I've put my OracleConnection.ClearAllPools(); – Muttra

I had similar issue. When I call an SP in oracle, it creates 40+ processes when checked in Session browser. It didn't clear even after using "using" or "oracleConnection.close() and dispose()". So temporarily, I had to use OracleConnection.ClearPool(oraConn). Thanks for your code. Meanwhile, did you find a different solution or the problem of this? – Repent

Nope, this took me so long to get to that I kind of ran out of time to find any way to improve it. I wonder what the resource cost is to call ClearAllPools() on the OracleCOnnection object every time we do a db call... I'd almost be willing to assume that it was something in my code, but it seemed pretty tight, the only reason I'd say that is because I haven't found anyone else with the same issue. – Muttra

in my case we figured out the problem. It was not with the c# code or the stored procedures. When the database was refreshed with new data, the DBAs have to give few indexes like parallel index in the tables which was missing. After giving them, the process got redcued and took only 1., – Repent

sigh I've left the place of work where this question originated from but when I spoke to the DBAs they just blamed it on the code. Me being a newbie to the team, they wouldn't take anything else. – Muttra

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.

Chimerical

Joel - we did a code review and we made sure that we close all open connections after executing with try, catch and finally. – Warton

That's still not clear to me that you did it right. Your comment makes it sound like the try/catch/finally occur only after executing your query, and only covers the .Close() call. The query execution must be inside the try block, and the .Close() call must be inside the corresponding finally block. If this is the case, make sure this is clearly editing into your original question. I think that you probably do have it right, but I need to be certain before we can start looking at other issues. If this is wrong in even one place, it's almost certainly the culprit. – Chimerical

Answer 4

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.

Indamine

I could not find in documentation where OracleConnection implements IDisposable - can you please show some relevant links? – Dreibund

Answer 5

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.

Profluent

I had an error "exceeded simultaneous SESSIONS_PER_USER limit" because of this. Fixed by setting "Min pooling" and "Max pooling" values. – Isolecithal