Memory Leak with OracleCommand

Question

I'm using ODP.Net version 11.1.0 to insert data into a database, and I'm seeing a memory leak. If I comment out the code below, it goes away. This code is called thousands of times in my application, and I can watch # of bytes in all heaps grow steadily as it runs. cmdStr contains an insert statement that inserts into a table with 375 columns. The fields are all NUMBER except for two - one is a DATE, and the other is a VARCHAR2(20). Is there something else I need to do to clean up the OracleCommand? No exceptions are thrown here - the insert command is successful every time.

Edit: I tried moving the return statement, and that had no effect as expected - using is really a try-finally block.

Update: I used CLRProfiler to see what is using up the memory, and it's a bunch of string objects, ~2800 of them. Their references are held by HashTable objects that are owned by Oracle.DataAccess.Client.ConnDataPool objects. Why is ODP.NET keeping these around?

try
{
    using (OracleCommand cmd = new OracleCommand(cmdStr, conn))
    {
        cmd.CommandTimeout = txTimeout;
        int nRowsAffected = cmd.ExecuteNonQuery();
        errMsg = null;
        return EndpointResult.Success;
    }
}
catch (OracleException e)
{
    return BFOracleAdapter.HandleOracleException(e, out errMsg);
}
catch (Exception e)
{
    errMsg = "OracleInsertOperation Exception: " + e.Message;
    return EndpointResult.Error;
}

Answer 1

Try to move the return statement outside the using block.

Answer 2

Try restructuring it to something like:

  object o;
  using (OracleCommand cmd = new OracleCommand(cmdStr, conn))
  {
    try
    {
      cmd.CommandTimeout = txTimeout;
      int nRowsAffected = cmd.ExecuteNonQuery();
      errMsg = null;
      o = EndpointResult.Success;
    }
    catch (OracleException e)
    {
      o = BFOracleAdapter.HandleOracleException(e, out errMsg);
    }
    catch (Exception e)
    {
      errMsg = "OracleInsertOperation Exception: " + e.Message;
      o = EndpointResult.Error;
    }
    finally 
    {
      // clean up
    }
  }
  return o

Answer 3

Do you keep your connection open? Try opening a new connection every time you need to issue this command (it's pooled anyway so it won't affect performance), close & dispose it after you're done with the transaction and see if the memory leak goes away.

Answer 4

Try wrapping a using statement with the OracleConnection around your using statement like this:

try
{
    using (OracleConnection conn = new OracleConnection(connectionString))
    {
        using (OracleCommand cmd = new OracleCommand(cmdStr, conn))
        {
        ....
        }
    }
}
catch (OracleException e)
{
  ....
}

This would get rid of the OracleConnection object as soon as possible -- even when an OracleException would occure inside the using statements.

Answer 5

What is the max number of connections on your connection pool? By default, connection pooling is enabled with 100 max connections.

You don't show the part of the code that creates /disposes connections. I would suspect that is more likely where the problem is going to be found.