1

I'm currently analyzing an OutOfMemoryException occuring in our application when doing a SQL select.

Environment:
- x86 .NET 4.6.1 application (No possibility to use it as x64)
- Devart.Data 5.0.1491.0
- Devart.Data.Oracle 9.1.67.0
- Entity Framework 6.0.0.0
- Oracle 12c

What happens?
A specific query accessing multiple tables containing a lot of data throws this exception. This only happens after having used the application for a while, first it works fine. Once the exception occurs this query always fails; others work though.

The exception originates from:
Exception of type 'System.OutOfMemoryException' was thrown. Stack Trace: 

at Devart.Data.Oracle.OracleDataReader.a() 
at Devart.Data.Oracle.OracleDataReader.Read()
[...]

When doing:

context.Database.SqlQuery<T>(query, allParameters.ToArray()).ToList()

context: is System.Data.Entity.DbContext
query: Is the SQL query (string) we compute ourselves
parameters: contains 1 parameter specifying the max number of results to retrun

Analysis:
When profiling the application with dotMemory, there isn't a big difference between the moment the query works and when it doesn't.
Working: 507MB total, 76MB used by .NET
Not working: 535MB, 104MB used by .NET
We are far away from the 2GB available for a .NET process.

When performing the same query with the 'Oracle SQL Developer' the query always succeeds in ~30s

When using DbMonitor we can see a delay of ~25s between the query and the rollback (done due to exception). Both query and rollback have an Error 'Completed successfully'.

Does someone know a reason or even a possible fix for this issue? Could DevArt falsely throw this exception because it e.g. reached a timeout? Is there an internal cache which is detached from the .NET process which gets filled after a while?

I've first posted this question on the DevArt forums but didn't get an answer.

Thanks in advance for your help.

Philippe
  • 1,949
  • 4
  • 31
  • 57
  • Did you check for heap fragmentation? https://www.jetbrains.com/help/dotmemory/2016.3/Heap_Fragmentation.html – Chris Mar 13 '17 at 07:58

1 Answers1

1

As suggested here as first option, try setting the FetchSize property of your OracleCommand explicitly to a reasonable value (like 100).

I had found this post before but I haven't been able to set the FetchSize (even after investigating for 2h now). We don't instantiate the OracleCommand ourselves; it is being done when executing the query.

Use the connection string to specify the value and start with a very low one to ascertain if that's the cause.

  • I had found this post before but I haven't been able to set the FetchSize (even after investigating for 2h now). We don't instantiate the ´OracleCommand´ ourselves; it is being done when executing the query. I expected to find the default property in here https://www.devart.com/dotconnect/oracle/docs/EFProviderConfiguration.html but didn't. Do you know how to configure it? – Philippe Mar 13 '17 at 10:33
  • Can't you specify it in the [connection string](https://www.connectionstrings.com/oracle-provider-for-ole-db-oraoledb/controling-the-fetchsize/)? –  Mar 13 '17 at 11:02
  • You're right with the connection string, thanks a lot! First tests show that increasing the value to 1000 solves the issue (I haven't been able to reproduce it for now); decreasing it to 50 still throws the exception (default is 100). I now have to investigate further to understand the impact (as at first glance it doesn't make sense to me why increasing the fetch size reduces the used memory) and profile to see how execution times get impacted. – Philippe Mar 13 '17 at 12:38