Open cursors leak in ODP.NET driver

[heku]

1. Start a database instance by

   docker pull container-registry.oracle.com/database/free:latest-lite
   docker run -d -p 1521:1521 -e ORACLE_PWD = 123456 --name oracle-db container-registry.oracle.com/database/free:latest-lite

2. Create a simple console app

   
   <Project Sdk="Microsoft.NET.Sdk">
   
   <PropertyGroup>
   <OutputType>Exe</OutputType>
   <TargetFramework>net8.0</TargetFramework>
   <ImplicitUsings>enable</ImplicitUsings>
   </PropertyGroup>
   
   <ItemGroup>
   <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="23.6.1" />
   </ItemGroup>

Program.cs
```cs
using Oracle.ManagedDataAccess.Client;

namespace OdpNetCursorLeakDemo;

internal class Program
{
    static void Main(string[] args)
    {
        TestOracle();
        Console.WriteLine("Hello, World!");
        Console.ReadKey();
    }

    static void TestOracle()
    {
        // see Connecting from Outside the Container in https://container-registry.oracle.com/ords/f?p=113:4:100732722818729:::4:P4_REPOSITORY,AI_REPOSITORY,AI_REPOSITORY_NAME,P4_REPOSITORY_NAME,P4_EULA_ID,P4_BUSINESS_AREA_ID:1863,1863,Oracle%20Database%20Free,Oracle%20Database%20Free,1,0&cs=3s3lHY-E4ddXQQ6egsNiXuqO5-VCW53KH26hn5ZTc96ERaK4YqZvXuWgcAQepQb1cBWRA94y7Y-v-8CY63SjLLQ

        var conn = new OracleConnection("Data Source=localhost:1521/FREEPDB1;User ID=PDBADMIN;PASSWORD=123456");
        var cmd = new OracleCommand("select 123 from dual", conn);

        conn.Open();

        Console.WriteLine(cmd.ExecuteScalar());

        cmd.Dispose();
        conn.Close();
        conn.Dispose();
    }
}

3. Run the console app (do not close the window), then query opened cursors by

   select * from v$open_cursor where user_name = 'PDBADMIN' and cursor_type not like '%CACHED';

   

I expect there is not opened cursors for current connection, as it's closed and disposed.

[alexkeh]

@heku I can reproduce this open cursor behavior. However, the DB does keep recent cursors open even after ODP.NET closes the connection. ODP.NET can inform the DB server when cursors are no longer needed, but it cannot force the DB to remove those cursors.

Are you running out of cursors or seeing a cursor leak under a heavy load? If the DB is not releasing cursors under a heavy load, then we should look at that behavior.

[heku]

@alexkeh yes, due to this behavior, dba reported our app's open cursors count come up to limitation. Better to provide a way to release them (except disabling the connection pool)

[alexkeh]

@heku Some possible reasons the cursors are not being released are ODP.NET statement caching and metadata pooling, which are on by default. These can hold cursors open after connections close since it's common for pooled users to execute the same statement again. Having the statement parse and metadata cached speeds up subsequent executions. This is why you can see cursors still open for a single or few statements.

When we observe the DB hitting the open cursor count limit, this usually means the cursors are not being closed explicitly or there's an Oracle bug. The bug could be in either the client or DB layer. I recommend creating an Oracle Support service request. Cursor leaks are difficult to diagnose. It's very hard to create a reproducible test case to diagnose unless it's forgetting a Close/Dispose somewhere. It sounds like you've already checked for that.

[heku]

@alexkeh tks for the explanation.