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