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.

Answer 2

You can use this query to monitor your connection counts & statuses. Using this query, I was able to confirm that the connection string settings are working, explanation below.

select   COUNT(*) AS Connections
        ,s.username
        ,s.status
        ,s.module
        ,s.osuser
from    V$process p
join    V$session s on s.paddr = p.addr
where  NOT s.UserName IS NULL
group by s.username
        ,s.status
        ,s.module
        ,s.osuser

I ran this with 2 pages that did a bunch of database retrievals. Here are my differing results:

Max Pool Size=5

I saw fluctuations in the count under the empty module with same username as the webserver. I'm not sure why they showed up under that bucket as well as the webserver.

Max Pool Size=1

When I restricted the pool size, I only ever saw 1 connection for the empty module, and 1 connection for the webserver, but then connections popped up under DBMS_SCHEDULER, which indicates to me that the rest of the retreivals were pending?

I think this proves that the Max Pool Size is working, but I'm not certain.

Answer 3

According to Tom kyte:
A connection is a physical circuit between you and the database.
A connection might be one of many types -- most popular begin DEDICATED server and SHARED server.
Zero, one or more sessions may be established over a given connection to the database
A process will be used by a session to execute statements.
Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal dedicated server connection).
Sometimes there is a one to many from connection to sessions.
A process does not have to be dedicated to a specific connection or session however, for example when using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement. When the call is over, that process is released back to the pool of processes.
So running
select username from v$session where username is not null will show current seesions (not connections)
To see the connections you may use

select username, program from v$process;

A useful book about JDBC and Session VS Connection could be found here