Clarification about Incr Pool Size parameter

[mcortellino]

Hello I am conducting laboratory test and fine tuning on Connection string parameters using ODP .NET 23.7.0.
I have a doubt about Incr Pool Size parameter, the docs says that this is the:

"Number of new connections to be created when all connections in the pool are in use."

So my assumption was that when I create the first connection, the driver should open 5 connections instead of 1.
But what actually happens is that it opens a number of connection between 1 and Incr Pool Size.

Most of the time the driver opens only 2 connections. So this parameter seems to be the Maximum number of connection created when all connections in the pool are in use. Is this correct?

Thanks

[alexkeh]

@mcortellino At startup time, the primary goal is to meet the Min Pool Size setting in the pool. If you want 5 connections initially created and to always be present, set the Min Pool Size to 5.

Incr Pool Size is the floor for the number of connections to be created at each interval ODP.NET checks and sees more connections are needed.

After the pool is initially populated, you should see new connections get created 5 (or more) at a time on an ongoing basis. If you don't observe this, turn on ODP.NET tracing and we can take a look at what is going on with your pool.

[mcortellino]

@alexkeh

I tried with Min Pool Size = 5, but it doesn't seems to work.
I tested it on a windows machine with this simple c# program:

using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Diagnostics;

namespace TestOracleConnection
{
internal class Program
{
static void Main(string[] args)
{
string connString = args[0];
int counter = Convert.ToInt32(args[1]);

        OracleConfiguration.TraceOption = 1;
        OracleConfiguration.TraceLevel = 3; 
        
        for (int i = 0; i < counter; i++) 
        {
            Console.WriteLine($"Start connection {i}...");
            var s = Stopwatch.StartNew();
            var c1 = OpenConnection(connString);
            c1.Close();
            s.Stop();
            Console.WriteLine($"Connection done. Elapsed time: {s.ElapsedMilliseconds} ms");
        }            
    }

    private static OracleConnection OpenConnection(string connString)
    {
        OracleConnection connection = new OracleConnection(connString);        
        connection.Open();
        OracleCommand orclCmdCheck = null;
        orclCmdCheck = connection.CreateCommand();
        orclCmdCheck.CommandText = "select 1 from dual";
        orclCmdCheck.CommandType = CommandType.Text;
        var result = Convert.ToInt32(((Decimal)orclCmdCheck.ExecuteScalar())) > 0;
         return connection;
        
    }
}

}

and the following connection string:

user id=* * *; password= * * ; data source= * * ; Max Pool Size=20; Min Pool Size=5; Decr Pool Size=1; Incr Pool Size=10; Connection Lifetime=300;Connection Timeout=120; Statement Cache Size=10; Statement Cache Purge=false; Self Tuning=false

after 5 successful session connection opened I have on netstat this situation:

[alexkeh]

@mcortellino Can you share the trace when using the above connection string?

We can then see why ODP.NET is not opening 10 new connections for your app after the pool is initially populated.

[mcortellino]

@alexkeh
These are the trace files. Thanks

trace.zip

[alexkeh]

Thanks @mcortellino! We'll review the trace.

[mcortellino]

Hi @alexkeh after further investigations, it comes out that the Min Pool Size parameter doesn't work IF native network encryption is enabled. With NNE disabled it works as expected, anyway for my use case encryption must be ON so I'm wonder to understand if this is the expected behaviour. Thanks

[alexkeh]

@mcortellino .NET background threads populate the pool with the Min Pool Size or Incr Pool Size number of connections. You must wait for the background threads to finish their job before closing the application. Otherwise, you will never see the requisite number of sessions created. From the trace, the app didn't wait for the background thread to finish creating sessions.

Some other notes about Incr Pool Size, at least in its current ODP.NET implementation: it only applies when the total connections is greater than Min Pool Size and all connections are checked out of the pool (i.e., in use).

For example, consider Min Pool Size' = 5 and Incr Pool Size' = 10. There are currently 5 connections in the pool, and all are in use. When the app makes a new connection request, a brand-new connection is created and returned to the app. There are now 6 connections in the pool. In the background, Incr Pool Size (10) number of connections are created. The total pooled connections will be 16.

That's the implementation as of today. It can (and will) change over time. It's been modified based on customer feedback over the years. How quickly and with what priority the pool grows and shrinks is very much an art.

The Min Pool Size behavior shouldn't change based on whether NNE is enabled. Can you share a trace in which NNE is enabled, you connect once, and allow ODP.NET to populate the pool to the level below Min Pool Size? And then do the same with NNE off and let it get to Min Pool Size?

[mcortellino]

Hi @alexkeh thanks for the explanation. It makes much more sense now.
I redone the tests giving more time to the application background test to complete the connection setup and now the results are as expected.
Regarding the NNE (not NNE) issue, I think it was a side effect of the early termination of the application, probably without NNE the connection setup is much more faster and the background thread is able to open the required connections in less time, with NNE is slower so he doesn't have the time to open all the connections before the termination.
Leaving the threads running the result is the same in both cases.

Thank you very much I really appreciate your help.

[alexkeh]

No problem! Happy to help.