R – Memory Leak with OracleCommand

.netoracle

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;
}

Best Solution

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.

Related Solutions

.net – Anatothe of a “Memory Leak”

The best explanation I've seen is in Chapter 7 of the free Foundations of Programming e-book.

Basically, in .NET a memory leak occurs when referenced objects are rooted and thus cannot be garbage collected. This occurs accidentally when you hold on to references beyond the intended scope.

You'll know that you have leaks when you start getting OutOfMemoryExceptions or your memory usage goes up beyond what you'd expect (PerfMon has nice memory counters).

Understanding .NET's memory model is your best way of avoiding it. Specifically, understanding how the garbage collector works and how references work — again, I refer you to chapter 7 of the e-book. Also, be mindful of common pitfalls, probably the most common being events. If object A is registered to an event on object B, then object A will stick around until object B disappears because B holds a reference to A. The solution is to unregister your events when you're done.

Of course, a good memory profile will let you see your object graphs and explore the nesting/referencing of your objects to see where references are coming from and what root object is responsible (red-gate ants profile, JetBrains dotMemory, memprofiler are really good choices, or you can use the text-only WinDbg and SOS, but I'd strongly recommend a commercial/visual product unless you're a real guru).

I believe unmanaged code is subject to its typical memory leaks, except that shared references are managed by the garbage collector. I could be wrong about this last point.

C# – Func delegate with no return type

All Func delegates return something; all the Action delegates return void.

Func<TResult> takes no arguments and returns TResult:

public delegate TResult Func<TResult>()

Action<T> takes one argument and does not return a value:

public delegate void Action<T>(T obj)

Action is the simplest, 'bare' delegate:

public delegate void Action()

There's also Func<TArg1, TResult> and Action<TArg1, TArg2> (and others up to 16 arguments). All of these (except for Action<T>) are new to .NET 3.5 (defined in System.Core).

Related Question