1

I'm reading data from an Oracle database using the DbDataReader (OracleDataReader) class. I'm running the program in Visual Studio 2010, with a 64-bit Windows 10 Enterprise laptop with 32GB RAM (20GB free), using .NET 3.5 (upgrading to a newer .NET version isn't an option. The application itself is a 32-bit application (not by choice).

The code looks something like this:

//...some code to setup database connection, command, etc.
DbDataReader reader = null;
int count = 0;

try {
    reader = command.ExecuteReader();
    if(reader.HasRows) {
        while(reader.Read()) {
            count++;
        }
    }
}
finally {
    if(reader != null) { reader.Close(); }
}

Stack trace: 

System.OutOfMemoryException was unhandled
    Message=Exception of type 'System.OutOfMemoryException' was thrown.
    Source=Oracle.DataAccess
    StackTrace:
    at Oracle.DataAccess.Client.OracleDataReader.Dispose(Boolean disposing)
    at Oracle.DataAccess.Client.OracleDataReader.Close()
    at Test.Program.Main(String[] args)
    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()

I've also had this stack trace a number of times:

System.OutOfMemoryException was caught
    Message=Exception of type 'System.OutOfMemoryException' was thrown.
    Source=Oracle.DataAccess
    StackTrace:
        at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
        at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src, Boolean bCheck)
        at Oracle.DataAccess.Client.OracleDataReader.Read()
        at Test.Program.Main(String[] args)

I am not allocating any memory myself in this test case, I'm just incrementing an integer, yet I still run out of memory, which tells me that the DbDataReader is allocating memory and probably not deallocating it properly (or in a timely manner). I've tried manually doing garbage collection to see if that helps, but it doesn't. I've also looked into whether the rows it crashes on contain too much data and thus fill up remaining memory, but that doesn't add up either since much larger amounts of data are read from other rows (and discarded) prior to it crashing.

Any ideas/help are greatly appreciated, thank you!

the_cwazy_wabbit
  • 81
  • 5
  • Can't you just return `COUNT(*)` rather than individual rows since you're discarding the result anyway? – orhtej2 Feb 21 '18 at 22:49
  • I'm not normally discarding the rows, normally I'm reading the data, but I kept running out of memory when doing that so I simplified my code to basically what you see above, and I still run out of memory. – the_cwazy_wabbit Feb 21 '18 at 22:52
  • Why don't you use the specific OracleDataReader instead of the base class? How do you declare and initialize the command? – Steve Feb 21 '18 at 22:54
  • It is using the OracleDataReader, the code is written generically because I've developed an entire API to work generically, and part of the API handles databases and connections for a number of database types. The command is an OracleCommand which has its CommandText set to CommandType.Text and has its CommandText set to the query string in this case. It also has the InitialLOBFetchSize set to 100000 and the FetchSize set to 200000. – the_cwazy_wabbit Feb 21 '18 at 23:02

1 Answers1

2

After tinkering around a bit with various settings on the OracleCommand and the OracleDataReader, the problem turned out to be that the FetchSize was initially too high on the OracleDataReader.

Setting the FetchSize on the OracleDataReader after the command is executed resolves the problem and allows the application to run without any memory errors.

the_cwazy_wabbit
  • 81
  • 5