CODEWITHSUNDEEP
c#.netoracle-databaseentity-framework-6devart
Philippe
Philippe

Reputation: 2029

OutOfMemoryException with SQL Select

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.

Upvotes: 1

Views: 2210

Answers (1)

user6996876
user6996876

Reputation:

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.

Upvotes: 1

Related Questions