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();
        }
    }
}

Solution 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.

Solution 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.

Solution 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

Solution 4

If you absolutely have to fix this, and are willing to get down & dirty with performance counters, this blog post might be of help. At the very least it might help narrow down a discrepency between how many connections Oracle is reporting vs. how many pooled & non-pooled connections .NET claims to have.

http://blog.ilab8.com/2011/09/02/odp-net-pooling-and-connection-request-timed-out/

These counters seem like they would be particularly useful:

NumberOfActiveConnectionPools
NumberOfActiveConnections
NumberOfFreeConnections
NumberOfInactiveConnectionPools
NumberOfNonPooledConnections
NumberOfPooledConnections
NumberOfReclaimedConnections
NumberOfStasisConnections

View more solutions

Comments

[jprincipe]

Thank you for the information about v$process. Unfortunately this just gave me a breakdown of the same numbers I identified in the v$session table. I am seeing a total of 20 connections established now, but have seen it get up to 25. The current breakdown from the v$process table is 14 connections from the connection user with the status of INACTIVE, module of w3wp.exe and osuser of NETWORK?SERVICE. There are another 6 connections for that user that are INACTIVE with the OSUSER of NETWORK?SERVICE, but a module of (null).

[Tom Halladay]

Do you see any from DBMS_SCHEDULER? Can you post a masked version of your results?

[jprincipe]

I don't see anything related to DBMS_SCHEDULER for the DB User in question. All connections are coming from w3wp.exe or (null).

[Tom Halladay]

Do you have a separate local & dev environment, and possibly didn't copy the connection string over 100% correctly?

[jprincipe]

I have tested this in multiple environments, both local and to deployed to different IIS instances. All of them show the same behavior of connections climbing way past the Max Connections allowed.

[RolandoCC]

Also, be aware that Connection Pools are per Worker Process per App Domain, so, Max pool Size of 5 * 5 Worker process * 2 Domains each = 50 connections.