C# EntityFramework. ORA-01000: maximum open cursors exceeded

SergeyKotelnikov

Hi all.

We caught ORA-01000: maximum open cursors exceeded.

1. We use C# EF Oracle.EntityFrameworkCore 2.18.0-beta3

2. We use DbContextPool:

...

.AddDbContextPool<ClientsDbContext>(dbConfig => dbConfig.UseOracle(

                    _configuration.GetConnectionString("ClientsDb"),

                    options => options.EnableOracleRetryOnKnownFailure(3, TimeSpan.FromSeconds(3))))

...

3. We Inject our DbContext as scoped object. So its disposed correctly each request - we checked it.

4. We use common EF operations - add, update etc, nothing special

5. The exception appears after about 2 weeks continuous service job. After the restart our service works fine. And after another 2 weeks the same exception appears again

Our suspicions - may be connections are not closed correctly inside the library? Can you recommend something to avoid this problem.

Thanks in advance

Alex Keh-Oracle

I would recommend opening up a service request with Oracle Support. This issue won't be straightforward to diagnose because it is requires a long time to reproduce and is likely a result of a repeated pattern of interaction between the app and DB.

If Oracle Support has any questions about getting help on the Oracle EF Core side, tell them to get in contact with me. They'll be able to find me internally.

Alex Keh-Oracle

One suggestion for a workaround is to set a Connection Lifetime in your connection string. This would ensure that if your connections are leaking cursors, they will get cleaned up every time your connection gets checked back into the pool. This assumes you aren't holding onto the DbContext the entire 2 weeks. If so, the lifetime setting won't work because it's only checked when the connection is checked back into the pool.

How long are your DbContexts checked out?

ODP.NET tries to match up its max cursors allowed with the max the DB allows. It's possible this max could be exceeded if stored procedures are opening enough cursors when combined with ODP.NET's cursors. Are you opening up a lot of cursors in stored procedures, enough that when combined with ODP.NET exceed your max cursor value?

SergeyKotelnikov

Thank you for your answer.

We use DbContext as scoped per http request for fast atomic operations - get, update, add entities. No cursors, no stored procedures. So DbContext lifetime is very short (from constructor to Dispose).

Alex Keh-Oracle

Then, Connect Lifetime should be a viable workaround to try since DbContext lifetime is short.

To diagnose the problem, Oracle Support will need to look at some of your Oracle AWR reports and/or a test case to diagnose what is exactly leaking and why.

463a1433-6c09-43ac-8c2f-650ac344b332

You can set the default MaxBatchSize on your context. Lower it!

SergeyKotelnikov

Thanks. Can you explain how it connect with the problem? And what value you recomend to set in MaxBatchSize?