Oracle.ManagedDataAccess 12.2.1100 OracleDataAdapter using 127 connections

user12141239

Hi,

Before posting, I searched for around an hour or more to see if anyone else ran into a similar issue, but was unable to find a related problem online.

I have a legacy application that I upgraded to use Oracle.ManagedDataAccess 12.2.1100 from the older ODP .NET

Locally, I am reproducing an issue.  When filling a DataTable, I end up with 127 connections.  If I pause the application during the Fill, I get a consistent 127 connections held for my user/module in v$sessions that last until I allow the Fill to finish by resuming.

This spike is causing headaches with the DBA as it uses up the max connection pool.

To troubleshoot, I checked my connection string to see if it was incorrect. During debugging, I tried this:

"Data Source=mytnsentryhere;User ID=myusernamehere;Password=mypasswordhere;Pooling=true;Min Pool Size=1;Max Pool Size=2;Incr Pool Size=1; Decr Pool Size=1;Persist Security Info=true"

Next, I tried checking my package function to see if that's the problem. It looks like this:

create or replace package mypackage is

  type dataset is ref cursor;
function get_data(n_id in number)return mypackage.dataset;

end mypackage;

create or replace package body mypackage is

function get_data(n_id in number) return mypackage.dataset is

  ds mypackage.dataset;

begin

  open ds for

    select f.*
from mytable f
where f.id = n_id;

return ds;

end;

end mypackage;

My .NET code looks like this:

               var cmd = new OracleCommand("mypackage.get_data", myconnectionObject)

                {

                    CommandType = CommandType.StoredProcedure

                };

                cmd.Parameters.Add("return_value", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

                cmd.Parameters.Add("n_id", OracleDbType.Int64, sampleSet_ID, ParameterDirection.Input);

                var oracleDataAdapter = new OracleDataAdapter(cmd);

                var dataTable = new DataTable();

                oracleDataAdapter.Fill(dataTable); // <-- this line causes 127 v$session entries

I verified this issue using a SQL Developer session running a query that looks like this w/ this result:

To verify this was not a recent issue, I tried two other nuget package versions. One less than this version and the oldest available version on nuget. To be sure I got the new packages, I deleted my bin folder, after upgrading, then rebuilt the project. Neither solved the issue. I did not try reverting to ODP .NET, but I don't consider that a desirable solution.

Any thoughts on what might be going on?

Thanks,

John Goodwin

Thiyagu - Oracle

HI John

Can you  please try with validate connection= true in connection string

Pleas refer the connection string properties to effectively use it

Using Connection Pooling With Oracle Data Provider for .Net (Doc ID 240997.1)

Thanks

Thiyagu

Alex Keh-Oracle

Just to be clear, there are 127 sessions created. Sessions are not necessarily equal to connections. You can have more sessions created than actual connections. That must be the case as you have limited the maximum pool size to 2.

ODP.NET does not have direct control over how many sessions get created, but I do agree that 127 are a lot and switching to managed ODP.NET shouldn't cause this behavior.

Which version of unmanaged ODP.NET did you use before and how many sessions did you see used then? I assume you were running it against the same DB instance with the same server settings. If you use the unmanaged ODP.NET 12.2 version, do you see this same behavior?

I'm trying to isolate the cause as I have little insight to how your DBA has set up the DB server. I just want to eliminate external factors to ensure it's an ODP.NET issue.

user12141239

Hi Alex,

REVISED:

To ensure the issue was *JUST* the code, I have two snapshots. Before/after the ManagedDataAccess upgrade. When I performed the old msg below, I did not get the issue, but then I reverted back to double check if it's gone across the board and it is indeed non-reproducible on both.

Is there something on the server I can check for why this would happen?  The query in the procedure, while obtuse, is unremarkable otherwise with no use of custom functions or views.

If it helps, I also observed that every session has the same SQL ID when the problem was happening.

Also, our DBA reported seeing this when another user ran a select query in SQL Developer as a one-off.

John Goodwin

----

OLD MSG BELOW:

Reverting the codebase to the older ODP, did in fact remove the reproducibility.

Steps performed:

1. Rolled back to code using Oracle.DataAccess.dll version 2.111.7.0 per the right click file properties
2. Installed Oracle 64bit ODAC 11.2 Release 6 (11.2.0.4.0) XCopy for Windows x64 from: 64-bit Oracle Data Access Components (ODAC) for Windows
   1. Unzipped
   2. Ran install.bat all c:\oracle 11g
   3. Prefixed system path environment variable with:  c:\oracle;c:\oracle\bin
3. Clean/rebuild the application
4. Run code to the line mentioned:
   1. oracleDataAdapter.Fill(dataTable)
5. The symptom is now gone and I am unable to reproduce this issue.

I did not mention this before, but only some calls seem to be doing this. We have other similar (to my eye) looking code, but it does not cause this issue.

John Goodwin