ODP.NET Connection Pooling Parameters

Question

I am trying to configure connection pooling for my .NET application using ODP.NET version 2.111.6.20. The database is Oracle 11.1.

I am using the following connection string in my .NET 2.0 application:

Data Source=prod; User Id=FAKE_USER; Password=FAKE_PASS; Pooling=true; Min Pool Size=2; Max Pool Size=5; Connection Timeout=30;"

According to the documentation the connection pool should initialize with 2 connections and and increment up to 5 connections as needed. It should never get higher than 5 connections.

What I am seeing is the the connections are growing 2 at a time and growing up to 10 connections. I am monitoring the connections in the Oracle database by querying the v$session table so I know the connections are from that specific application originating from my application.

If anyone can help me identify what might be happening in the connection pool inside this application that might be allowing for more than the Max number of connections I would appreciate it.

Sample C# Code

Here is a sample of the code making the calls to the database:

const string connectionString = "Data Source=prod; User Id=FAKE_USER; Password=FAKE_PASS; Pooling=true; Min Pool Size=5; Max Pool Size=5; Connection Timeout=30;";

using (OracleConnection connection = new OracleConnection(connectionString)) {
    connection.Open();

    using (OracleCommand command = new OracleCommand("ALTER SESSION SET TIME_ZONE='UTC'", connection)) {
        command.ExecuteScalar();
    }

    using (OracleTransaction transaction = connection.BeginTransaction()) {
        const string procSql = @"BEGIN P_SERVICES.UPDATE_VERSION(:id, :version, :installDate); END;";
        using (OracleCommand command = new OracleCommand(procSql, connection)) {
            command.Parameters.Add(new OracleParameter("id", OracleDbType.Varchar2) { Value = id });
            command.Parameters.Add(new OracleParameter("version", OracleDbType.Varchar2) { Value = version });
            command.Parameters.Add(new OracleParameter("installDate", OracleDbType.TimeStamp) { Value = dateUpdated });

            try {
                command.ExecuteNonQuery();
            } catch (OracleException oe) {
                if (Log.IsErrorEnabled) {
                    Log.ErrorFormat("Update Error: {0}", oe.Message);
                }

                throw;
            }

            transaction.Commit();
        }
    }
}

Answer 1

I have found the reason that the Maximum connections seen in the database is increasing past the number allowed in the connection pool settings in the connection string.

The Application Pool in IIS was configured to have "Maximum number of worker processes" set different than the default of 1. What I have found is that the number of connections seen in the database can grow up to the Max Pool Size * Number of Worker Processes.

So if I have Max Pool Size of 5 and 5 Worker Processes then the total number of connections allowed is 25. So it seems that each Worker Process has it's own instance of a connection pool that is not shared across other worker processes.