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

    Forum Stats

  • 3,715,998 Users
  • 2,242,925 Discussions
  • 7,845,726 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

SergeyKotelnikov
SergeyKotelnikov Member Posts: 6
edited June 2019 in ODP.NET

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

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited May 2019

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited May 2019

    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
    SergeyKotelnikov Member Posts: 6
    edited May 2019

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited May 2019

    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
    edited June 2019

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

  • SergeyKotelnikov
    SergeyKotelnikov Member Posts: 6
    edited June 2019

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

Sign In or Register to comment.