C# ODP.NET 连接池参数

ODP.NET Connection Pooling Parameters

提问by jprincipe

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.

我正在尝试使用 ODP.NET 版本 2.111.6.20 为我的 .NET 应用程序配置连接池。数据库是 Oracle 11.1。

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

我在 .NET 2.0 应用程序中使用以下连接字符串：

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

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.

根据文档，连接池应初始化为 2 个连接，并根据需要最多增加 5 个连接。它不应该超过 5 个连接。

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.

我所看到的是连接一次增长 2 个，并且增长到 10 个连接。我通过查询 v$session 表来监视 Oracle 数据库中的连接，因此我知道连接来自源自我的应用程序的特定应用程序。

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

示例 C# 代码

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

采纳答案by jprincipe

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.

IIS 中的应用程序池配置为“最大工作进程数”设置不同于默认值 1。我发现数据库中的连接数可以增长到Max Pool Size * Number of Worker Processes.

So if I have Max Pool Sizeof 5 and 5 Worker Processesthen 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.

因此，如果我的最大池大小为 5 和 5 个工作进程，则允许的连接总数为 25。因此，似乎每个工作进程都有自己的连接池实例，该实例未在其他工作进程之间共享。

回答by Mohsen Heydari

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 nullwill show current seesions (not connections)
To see the connections you may use

根据Tom kyte 的说法：
连接是您和数据库之间的物理电路。
连接可能是多种类型之一——最流行的开始专用服务器和共享服务器。
零个、一个或多个会话可以通过给定的数据库连接建立 会话
将使用进程来执行语句。
有时 CONNECTION->SESSION->PROCESS 之间存在一对一的关系（例如：普通的专用服务器连接）。
有时从连接到会话是一对多的。
进程不必专用于特定的连接或会话，但是，例如，在使用共享服务器 (MTS) 时，您的 SESSION 将从进程池中获取进程以执行语句。当调用结束时，该进程被释放回进程池。
所以运行
select username from v$session where username is not null将显示当前的seesions（不是连接）
要查看您可能使用的连接

select username, program from v$process;

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

可以在此处找到一本关于 JDBC 和 Session VS Connection 的有用书籍

回答by Tom Halladay

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:

我用 2 个页面运行了这个，这些页面做了一堆数据库检索。以下是我的不同结果：

Max Pool Size=5

最大池大小=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

最大池大小=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?

当我限制池大小时，我只看到空模块有 1 个连接，网络服务器有 1 个连接，但随后在 DBMS_SCHEDULER 下弹出了连接，这向我表明其余的检索正在挂起？

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

我认为这证明 Max Pool Size 有效，但我不确定。

回答by Tom Halladay

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.

如果您绝对必须解决这个问题，并且愿意使用性能计数器来解决问题，那么这篇博文可能会有所帮助。至少，它可能有助于缩小 Oracle 报告的连接数与 .NET 声称拥有的池化和非池化连接数之间的差异。

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

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