ODP.NET连接池参数 [英] ODP.NET Connection Pooling Parameters

问题描述

我想配置连接池使用ODP.NET版本2.111.6.20我的.NET应用程序。该数据库是Oracle 11.1。

我是用我的.NET 2.0的应用程序下面的连接字符串：

数据源= PROD;用户ID = FAKE_USER;密码= FAKE_PASS;池= TRUE;闵池大小= 2;最大池大小= 5;连接超时= 30;

据连接池应该初始化与2个连接和，并根据需要增加多达5个连接的文档。它不应该获得比5的连接高。

我所看到的是连接正在成长2的时间和成长为10个连接。我监控Oracle数据库的连接，通过查询V $会话表，所以我知道的连接是由特定的应用程序从我的应用程序发起。

如果有人能帮助我确定哪些可能发生在连接池这个应用程序可能被允许超过连接的最大数目我会AP preciate吧。

里面

样品C＃code

这里是code使得对数据库的调用示例：

 常量字符串的connectionString =数据源= PROD;用户ID = FAKE_USER;密码= FAKE_PASS;池= TRUE;闵池大小= 5;最大池大小= 5;连接超时= 30;;

使用（为OracleConnection连接=新的OracleConnection（的connectionString））{
    connection.Open（）;

    使用（的OracleCommand命令=新的OracleCommand（ALTER SESSION SET TIME_ZONE ='UTC'，连接））{
        command.ExecuteScalar（）;
    }

    使用（OracleTransaction事务= connection.BeginTransaction（））{
        常量字符串procSql = @BEGIN P_SERVICES.UPDATE_VERSION（：ID，：版本：installDate）; END;;
        使用（的OracleCommand命令=新的OracleCommand（procSql，连接））{
            command.Parameters.Add（新的OracleParameter（ID，OracleDbType.Varchar2）{值= ID}）;
            command.Parameters.Add（新的OracleParameter（版本，OracleDbType.Varchar2）{值=版本}）;
            command.Parameters.Add（新的OracleParameter（installDate，OracleDbType.TimeStamp）{值= dateUpdated}）;

            尝试 {
                command.ExecuteNonQuery（）;
            }赶上（OracleException OE）{
                如果（Log.IsErrorEnabled）{
                    Log.ErrorFormat（更新错误：{0}，oe.Message）;
                }

                扔;
            }

            器transaction.commit（）;
        }
    }
}
 

解决方案

我发现，出现在数据库中的最大连接数越来越多过去在连接字符串中的连接池设置允许数量的原因。

应用程序池在IIS中被配置为拥有工作进程的最大数量设定为比1的默认不同的我所发现的是，出现在数据库连接可以长到最大池大小的数量*工作进程数。

所以，如果我有5个和5个工作进程最大池大小，然后连接允许的总数是25这样看来，每个工作进程都有它未在其他工作进程共享一个连接池的自己的实例。

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

解决方案

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.

这篇关于ODP.NET连接池参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！