Forum Stats

  • 3,727,090 Users
  • 2,245,319 Discussions
  • 7,852,584 Comments

Discussions

Too many open cursors with upgrading ODP.Net from 2.19.110 to 3.21.1

user5543674
user5543674 Member Posts: 9 Green Ribbon
edited April 13 in ODP.NET

Hi,

I recently upgraded an application from ODP.Net 2.19.110 to 3.21.1 and faced the issue, that there are too many open cursors with the new ODP.Net version. When I execute the below sample code, with version 2.19.110, the total number of opened cursors stays stable around 30-32, while with the new version, it seems there is no limit, it went behind 1000 opened cursors.

Is there some adjustment needed in the ODP.Net configuration? I think this is related to statement cache, but I cannot find any hint in the release documentation, that the statement cache behavior has been changed.

Code to reproduce

using Dapper;
using Oracle.ManagedDataAccess.Client;

var i = 0; 
while (true)
{
  i++;
  using (var conn = new OracleConnection { ConnectionString = "Data Source=TestDb;User Id=test;Password=test;Connection Timeout=10;enlist=false;Pooling=true;Min Pool Size=2;Max Pool Size=2;Promotable Transaction=local" })
  {
    conn.Open();
    var res = conn.QueryFirstOrDefault<string>("select SYS_CONTEXT('USERENV','SID') sid, " + i + " xxx from dual");
    Console.WriteLine($"Dapper Query {i}. sid: {res}");
  }
} 


SQL to verify current opened cursors

select MAX(a.value)
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
and a.sid = :sid

Best Answer

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,798 Employee
    Accepted Answer

    This appears to be the same as Bug 30801402, which has been fixed. The bug fix was merged into our code line in February, which missed the 21.1 release, but did make it into the 19.11 release a month later. The next 21c release will have the bug fix included.

Answers

  • user5543674
    user5543674 Member Posts: 9 Green Ribbon

    I debugged a bit and found out, that the below needs to be done to get back the previous behavior:

    OracleConfiguration.MaxStatementCacheSize = 30
    

    Without that setting, the OraclePoolManager.m_recommendedSCS is set to 2028, so the StatementCache gets 2028. In my opinion, thats way too big. In addition, I found MaxStatementCacheSize.DEFAULT_MAX_STATEMENT_CACHE_SIZE = 200, but that is not used...

    I think it would be good to change the 2028 to default to 200, too.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,798 Employee
    Accepted Answer

    This appears to be the same as Bug 30801402, which has been fixed. The bug fix was merged into our code line in February, which missed the 21.1 release, but did make it into the 19.11 release a month later. The next 21c release will have the bug fix included.

  • user5543674
    user5543674 Member Posts: 9 Green Ribbon

    Thanks Alex for looking into that. I finally have 2 questions:


    - Are the bugs public available? I cannot find details for 30801402 on this site....

    - Do you already know the nuget Release date for next 21c?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,798 Employee

    The bug is publicly available via the My Oracle Support portal. If your organization has an Oracle Support account, then it should be able to access the bug details.

    We're hoping to have the next ODP.NET 21c version released in May. Exactly when in May depends when on the release's stability.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,798 Employee

    I was mistaken about Bug 30801402. I got the dates wrong. It had been fixed about a year ago, which means the fix should be available in latest ODP.NET and Oracle EF Core 21c.

    After discussing this issue with the dev team, this behavior could be a result of a change we made in ODP.NET 21c statement caching.

    When Self Tuning is true (default) and the MaxStatementCacheSize is not set locally, then ODP.NET will use the DB's max open cursor limit as its maximum number of statements that can be cached per connection. That is why setting MaxStatementCacheSize to lower the cursor limit prevents the error.

    To resolve the issue for now, you can keep the MaxStatementCacheSize setting or modify the DB's maximum open cursor limit. To determine the DB's max_open_cursor limit, execute this SQL:

    select p.value as max_open_cur

       from v$sesstat a, v$statname b, v$parameter p

       where a.statistic# = b.statistic#

       and b.name = 'opened cursors current'

       and p.name= 'open_cursors'

       group by p.value;

Sign In or Register to comment.