Leaking db sessions

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
0bo0
Posts: 3
Joined: Tue 10 Sep 2019 10:05

Leaking db sessions

Post by 0bo0 » Tue 10 Sep 2019 12:21

Hello,
I've recently noticed that when Pooling is enabled then oracle sessions are not removed even after application finishes.
Tested on .net core 2.2 console apps with Devart.Data.Oracle, I tried version 9.7.805 and the previous one. Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit.

Code: Select all

        public static void Main(string[] args)
        {
            using (var conn = new Devart.Data.Oracle.OracleConnection("User Id=xxx;Password=xxx;Server=xxx;Port=1521;Service Name=xxx;Direct=true;License Key=xxxxx;"))
            {
                conn.Open();
                conn.Close();
            }
            Devart.Data.Oracle.OracleConnection.ClearAllPools();
        }
Here are details of two sessions that remained in v$session after two executions of app shown above, captured at ~13:01:
Image
Image
Image
Image
Image
Image
Image
Image

Prev sql from these sessions:

Code: Select all

update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
Sometimes sessions disappear after longer period of time but I don't know what it depends on.
If I set Pooling=false in connection string they disappear immediately as expected right after conn.Close() is executed.
With Pooling enabled I would expect that all connections and sessions are gone when app quits.

Any idea?

Shalex
Site Admin
Posts: 9122
Joined: Thu 14 Aug 2008 12:44

Re: Leaking db sessions

Post by Shalex » Wed 11 Sep 2019 10:02

Pool cleaning is delayed. To start it immediately, use OracleConnection.ClearAllPools(forced: true). Does this help?

0bo0
Posts: 3
Joined: Tue 10 Sep 2019 10:05

Re: Leaking db sessions

Post by 0bo0 » Wed 11 Sep 2019 11:57

Thank you.
"forced:true" solves the issue. Not sure though why I need to call ClearAllPools explicitly. Shouldn't automatic clean up on exit be a default behavior?

Shalex
Site Admin
Posts: 9122
Joined: Thu 14 Aug 2008 12:44

Re: Leaking db sessions

Post by Shalex » Thu 12 Sep 2019 17:27

Please refer to viewtopic.php?t=37173.

0bo0
Posts: 3
Joined: Tue 10 Sep 2019 10:05

Re: Leaking db sessions

Post by 0bo0 » Fri 13 Sep 2019 08:08

Ok, but this is a completely different scenario. We are not talking about clearing pool while app continues to run. Process is gone, I had closed and disposed all connections correctly and still the provider causes a leak of unmanaged resources on db server. Did some experiments:
- oracle managed data access @ .net 4.7.2 - no leak
- devart data oracle @ .net 4.7.2 - no leak
- oracle managed data access @ .net core - leaked sessions
- devart data oracle @ .net core - leaked sessions
If equivalent of the following code was present in the providers it would save some headaches:

Code: Select all

AppDomain.CurrentDomain.ProcessExit += (s, e) =>
  OracleConnection.ClearAllPools(true);

Shalex
Site Admin
Posts: 9122
Joined: Thu 14 Aug 2008 12:44

Re: Leaking db sessions

Post by Shalex » Sat 14 Sep 2019 18:32

We will investigate the question and notify you about the result.

Shalex
Site Admin
Posts: 9122
Joined: Thu 14 Aug 2008 12:44

Re: Leaking db sessions

Post by Shalex » Mon 02 Mar 2020 14:04

The behavior is changed in v9.11.951: pool is automatically cleared when AppDomain is being closed. Refer to viewtopic.php?f=1&t=39884.

Post Reply