OutOfMemory on Select

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

OutOfMemory on Select

Post by ph.waeber » Thu 09 Mar 2017 10:55

Hi,

I've been assigned a task to solve an OutOfMemoryException occuring in our application. As I don't see a plausible reason I hope you can point me in the right direction.

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'.

Thanks in advance for your help, Philippe

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OutOfMemory on Select

Post by Shalex » Wed 15 Mar 2017 20:19

In case of plain ADO.NET, setting OracleCommand.FetchSize to 1 may reduce consuming memory (one record is read at a time): viewtopic.php?f=1&t=33653&start=15#p117025.

We are working on a special build for you which will allow to set FetchSize for Entity Framework functionality to check if it solves the issue you have encountered.

ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

Re: OutOfMemory on Select

Post by ph.waeber » Thu 16 Mar 2017 07:16

Hi Shalex and thanks for coming back to me.

After having re-posted the same question on StackOverflow I've gained some additional insight.

First, the response to the question asked in this thread:
- We are using the OCI. Tested with 11g and 12c
- We use EF6

According to this link the default Oracle fetch size is 100. When setting the value of 50 in the connection string, nothing changes and we get the described exception. If setting 1000, I'm not able to reproduce the issue anymore.
As we can't explain this behavior, we tend to think there is an issue in dotConnect. Thanks for investigating!

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OutOfMemory on Select

Post by Shalex » Thu 16 Mar 2017 09:14

Thank you for the additional information.

Have you tried setting OracleCommand.FetchSize to 1? Notify us about the result.

ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

Re: OutOfMemory on Select

Post by ph.waeber » Thu 16 Mar 2017 12:50

I can't set OracleCommand.FetchSize as it's instantiated through EF.
If setting FetchSize=1 in the connsection string I can still reproduce the issue.

ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

Re: OutOfMemory on Select

Post by ph.waeber » Tue 21 Mar 2017 09:44

Hi, have you got an update on this issue?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OutOfMemory on Select

Post by Shalex » Tue 21 Mar 2017 17:50

The FetchSize connection string parameter is not supported in the current build of dotConnect for Oracle. Looks like your context uses a different connection string (without "FetchSize=1"). Please check your connection string used in runtime by executing the code in the debug mode (or via dbMonitor).

We are working on a special build for you which will allow to set FetchSize via connection string.

ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

Re: OutOfMemory on Select

Post by ph.waeber » Wed 22 Mar 2017 10:21

You are correct, the FetchSize parameter was not being used for the queries done through the entity framework. I got confused by the two connection strings shown below, which have been configured for the OracleClient and EntityClient providers:

Code: Select all

  <add name="Standard"
       connectionString="DATA SOURCE=(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=x.x.x.x) (PORT=xxxx))  (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=xxx))); PASSWORD=xxx; Unicode=True; PERSIST SECURITY INFO=True; USER ID=xxx; Pooling=true; Connection Lifetime=180; Connection Timeout=30; Min Pool Size=2; Max Pool Size=10; FetchSize=1000"
       providerName="System.Data.OracleClient" />
  <add name="Entities"       connectionString="metadata=res://*/Main.MainModel.csdl|res://*/Main.MainModel.ssdl|res://*/Main.MainModel.msl; provider=Devart.Data.Oracle; provider connection string='User Id=xxx; Password=xxx; Server="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.x)(PORT=xxxx)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=xxx)))";Unicode=True;Sid=xxx;Persist Security Info=True;Pooling=true;Connection Lifetime=180;Connection Timeout=30;Min Pool Size=5;Max Pool Size=15'"
       providerName="System.Data.EntityClient" />
When checking the used connection string in DbMonitor, it shows the second one without FetchSize.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OutOfMemory on Select

Post by Shalex » Wed 22 Mar 2017 16:37

The Default Fetch Size connection string parameter, which specifies the number of rows that will be transferred across the network at a time, is added. We have just sent the corresponding internal build of dotConnect for Oracle to your email.

ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

Re: OutOfMemory on Select

Post by ph.waeber » Thu 23 Mar 2017 09:16

Thanks, I've downloaded and integrated the version you've sent.
I'm having a hard time figuring out how I'm supposed to set the FetchSize now. If I include it in the 'Entities' connection string shown above I get the same exception tellign the FetchSize parameter is unknown, when instantiating a new EntityConnection(connectionString) and I can't find another way to specify it.

After installing dotConnect I haven't been able to find Devart.Data.Oracle.Entity.EF6 in the installation folder. I'm still using 9.1.67.0 we had included before. Is this ok?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OutOfMemory on Select

Post by Shalex » Fri 24 Mar 2017 14:27

ph.waeber wrote:After installing dotConnect I haven't been able to find Devart.Data.Oracle.Entity.EF6 in the installation folder. I'm still using 9.1.67.0 we had included before. Is this ok?
This is not ok. You should use assemblies from the new (9.3.233) installation. Please run Tools > Oracle > Upgrade Wizard to update references, *.config, and licenses.licx automatically. If Upgrade Wizard fails for some reason, please update your project manually.

The installation places assemblies to:
"C:\Program Files (x86)\Devart\dotConnect\Oracle\Devart.Data.dll"
"C:\Program Files (x86)\Devart\dotConnect\Oracle\Devart.Data.Oracle.dll"
"C:\Program Files (x86)\Devart\dotConnect\Oracle\Entity\EF6\Devart.Data.Oracle.Entity.EF6.dll"

To make sure that runtime uses the new Devart.* assemblies, please set a break point and run your code in the debug mode, then navigate to Debug > Windows > Modules, the only Devart.* assemblies loaded in the process of your application should be:
Devart.Data.dll v5.0.1657.0
Devart.Data.Oracle.dll v9.3.233.0
Devart.Data.Oracle.Entity.EF6.dll v9.3.233.0

ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

Re: OutOfMemory on Select

Post by ph.waeber » Mon 27 Mar 2017 06:29

After installing dotConnect a second time, the Enitity folder was included and I'm now using all the DLLs with the versions you've posted above.
Can you please post an example how to specify FetchSize now? (When specifying it in the connection string I still get the same exception telling the FetchSize parameter is unknown and I couldn't find a new property in OracleEntityProviderConfig)

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OutOfMemory on Select

Post by Shalex » Mon 27 Mar 2017 10:35

Please add "Default Fetch Size=1;" to your connection string. Notify us about the result.

ph.waeber
Posts: 9
Joined: Thu 09 Mar 2017 09:05

Re: OutOfMemory on Select

Post by ph.waeber » Tue 28 Mar 2017 12:55

I've tried with 1, but after 10min the application was still initializing (and loading data from the DB) and I've aborted the test. With 100 the application ran and I couldn't reproduce the OutOfMemory issue. I didn't perform additional tests.

- Could we integrate the version you gave into our next release due next week? Meaning, is the fix you've done based on the last stable release or on the current development branch which might contain bugs?
- Can you propose a way to fine-tune the default fetch size to set in order to have best speeds without running into the memory issue? Otherwise we'll have to go with trial and error.
- What is the default 'Default Fetch Size'?

Thanks, Philippe

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OutOfMemory on Select

Post by Shalex » Wed 29 Mar 2017 10:26

ph.waeber wrote:Could we integrate the version you gave into our next release due next week? Meaning, is the fix you've done based on the last stable release or on the current development branch which might contain bugs?
The version with the fix is built on the last stable release. Also the Default Fetch Size connection string parameter will be available in the next public versions of dotConnect for Oracle.
ph.waeber wrote:Can you propose a way to fine-tune the default fetch size to set in order to have best speeds without running into the memory issue? Otherwise we'll have to go with trial and error.
The "best" value depends on the amount of records usually retrieved by your queries. If this amount is always greater than N, please set N in Default Fetch Size.
It's quite difficult to find out N if there are ORM calls which read only one record (.First() / .FirstOfDefault() / .Single() / .SingleOrDefault() ).
Internal logic creates a buffer for 20 records and increases it if the amount of records retrieved by reader is greater.
Recommendation:
* in most cases Default Fetch Size should be 20 or higer
* if memory is limited and performance loses are not critical, set Default Fetch Size=1
ph.waeber wrote:What is the default 'Default Fetch Size'?
The default value is 0 which means that actual value of this parameter will be determined automatically and it will be changed dynamically when executing read operations.

Post Reply