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.
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 theOracleCommandourselves; 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.