Oracle EF Core - Too many opened cursors

[kakone]

Hello,

With Oracle EF Core 7 or 8, I often receive the ORA-01000 error (maximum open cursors exceeded). When we do some updates, a lot of cursors are opened but not closed (cf #180). I did a test program that shows the problem.
In our web application, I don't see how I can close these cursors to avoid the ORA-01000 error. Even if I dispose the DbContext, the cursors stay open. They are only closed when the connection is closed. But, with connection pooling, the connection stay open and the cursors number increases continuously in the v$open_cursor view.
I can increase the open_cursors setting but I don't think it's a good solution. How can we properly close the cursors in Oracle EF Core ?

[alexkeh]

After query execution, when the connection is placed back to the pool, ODP.NET closes the open cursors. You shouldn’t see the continuous increase in the open cursors count unless you also see the connection count stay high.

The DB does have the ability to cache cursors. That's a possibility you have more open DB cursors than expected if the DB keeps caching them. V$open_cursor will show those cached cursors.

What is your DB max open cursor settings?

EF Core uses MaxBatchSize setting to determine the number of batched statements to execute in one round trip. The default MaxBatchSize is 42.

If your max open cursors value is less than MaxBatchSize, then an ORA-01000 error may occur. You can then trying using a MaxBatchSize value lesser than max open cursor value.

[kakone]

What is your DB max open cursor settings?

300

I'm going to do a test program to reproduce the ORA-01000 error.

[plachta11b]

We had the same issue for more than a year. Finally solved by adding a connection lifetime limit Connection Lifetime=180 into ConnectionString.

We tried many other things as lowering batch size, lowering connection pool size, updating all related nuggets, and reverting nuggets but nothing worked. Even 10k cursors were not enough. The bug affects at least EF7 and EF8 but I think it was always an issue since we used EFcore on net5. Our requests consist of a few EF selects using Linq and then updating some values. Finally, we call the SaveChanges method.

See the difference between limited and unlimited connection lifetime:

[alexkeh]

@plachta11b Thanks for proposing the workaround! If you have a reproducible test case we can run that would allow diagnosing the root cause, Oracle could then fix the issue. Thanks!

[alexkeh]

@kakone @plachta11b Also, it would be helpful for Oracle to know which DB version you are using when you encounter this error. Thanks!

[kakone]

I encounter the error with Oracle 19c and Oracle 21c.

[kakone]

I'm trying to reproduce the error in a test program but I didn't succeed in for the moment
In my business app, I can reproduce it. I get the ORA-01000 error when I insert of lot of data into several tables (100000 rows), and then I do some updates.

[jaredtait]

It looks like we are seeing this issue as well with a cursor batch size of 42 and max cursor of 300. Thanks @plachta11b for the work around.

@alexkeh any recommendations for using the workaround like setting the connection life time in conjunction with a different batch size or higher max cursor limit? Or perhaps setting the max cursors to a multiple of the batch size?

We are using Oracle.EntityFrameworkCore 7.21.12 and Oracle.ManagedDataAccess.Core 3.21.120

[alexkeh]

@jaredtait We're not sure about the root cause. Raising the max cursor size will treat the symptoms. Just logically, if you increase your DB max cursor level, it will at least give you more time before hitting an ORA-01000. If it's high enough you may never hit an ORA-01000.

[alexkeh]

I've created bug 36223397 to track this issue.

[plachta11b]

@alexkeh It is Oracle 19

[alexkeh]

It looks like there are two possible cursor leaks that can occur depending on the situation. One of the leaks has been resolved. We have a possible fix for the second leak and are validating it's working as expected.

[lwestfall]

@alexkeh now that this is closed, can we expect a Nuget release in the near future? I get this exception occasionally too, hoping these fixes will solve it.

[alexkeh]

@lwestfall Yes. There will be an ODP.NET 21.14 NuGet package delivered within the next couple of business days.