oracle 设置和更新连接池 (OracleConnectionPoolDataSource) 属性以获得最佳性能

Setting and updating connection pool (OracleConnectionPoolDataSource) properties for best performance

提问by prof401

In a Java web application I am working on, we are using OracleConnectionPoolDataSource for a database connection pool functionality. Each getConnection call includes the user's Oracle ID and password. So each user in a sense ends up with their own database connection pool.

在我正在处理的 Java Web 应用程序中，我们将 OracleConnectionPoolDataSource 用于数据库连接池功能。每个 getConnection 调用都包括用户的 Oracle ID 和密码。所以从某种意义上说，每个用户最终都有自己的数据库连接池。

Currently we are using the default values for most properties. This includes

目前，我们对大多数属性使用默认值。这包括

• MinLimitset to 0
• MaxLimitset to Integer.MAX_VALUE
• MaxStatementsLimitset to 0
• InactivityTimeoutset to 0
• TimeToLiveTimeout set to 0
• AbandonedConnectionTimeout set to 0
• PropertyCheckInterval set to 900
• ConnectionWaitTimeout set to 0

• MinLimit设置为 0
• MaxLimit设置为 Integer.MAX_VALUE
• MaxStatementsLimit设置为 0
• 不活动超时设置为 0
• TimeToLiveTimeout设置为 0
• AbandonedConnectionTimeout设置为 0
• PropertyCheckInterval设置为 900
• 连接等待超时设置为 0

More info about these properties can be found at Connection Cache Properties.

有关这些属性的更多信息可以在连接缓存属性中找到。

We currently do not have any glaring database connection problems, but think that the performance could be better. My question is does somebody have good advice or a good resource on what we should consider when adjusting these values.

我们目前没有任何明显的数据库连接问题，但认为性能可以更好。我的问题是，是否有人对我们在调整这些值时应该考虑的问题有好的建议或好的资源。

采纳答案by Vineet Reynolds

The Oracle Application Server Performance Guide for 10g Release 3 (10.1.3.1) provides definitive information on how to optimize the connection pool parameters.

适用于 10g 第 3 版 (10.1.3.1) 的 Oracle 应用服务器性能指南提供了有关如何优化连接池参数的权威信息。

The information is useful for almost all scenarios involving an application using a connection pool for managing connections to an Oracle database, not withstanding the application server in use.

该信息对于涉及使用连接池管理与 Oracle 数据库的连接的应用程序的几乎所有场景都很有用，尽管应用程序服务器正在使用中。

For instance, it is always a good practice to set a value for the minimum pool size. As far as the maximum pool size is concerned, the value should not be overtly high as that could load the listener especially if the application has the tendency to not close connections resulting in a leakage.

例如，为最小池大小设置一个值始终是一个好习惯。就最大池大小而言，该值不应过高，因为这可能会加载侦听器，尤其是在应用程序倾向于不关闭连接导致泄漏的情况下。

It is preferable to set a reasonable value for the statement cache, as this allows for prepared statements to be cached, allowing for improved performance.

最好为语句缓存设置一个合理的值，因为这允许缓存准备好的语句，从而提高性能。

Timeouts should also be chosen with the environment in mind. For instance, the connection wait timeout should not be zero in most circumstances, for this could cause SQLExceptions when physical connections cannot be initialized in the pool within a sufficient interval. The inactivity timeout should be large enough so that connections will be disposed off only after a sufficient duration of inactivity; too low a value would result in physical connections being created and dropped far too frequently.

还应考虑到环境来选择超时。例如，在大多数情况下，连接等待超时不应为零，因为在足够的时间间隔内无法在池中初始化物理连接时，这可能会导致 SQLExceptions。不活动超时应该足够大，以便只有在足够长的不活动时间后才会释放连接；太低的值会导致物理连接的创建和断开过于频繁。

EDIT: The guidance given in the Performance Guide applies to the oracle.jdbc.pool.OracleDataSource class, which is what the Oracle 10g Application Server uses for managed datasources to an Oracle database. Most of it will certainly carry over to the OracleConnectionPoolDataSource.

编辑：性能指南中给出的指导适用于 oracle.jdbc.pool.OracleDataSource 类，这是 Oracle 10g 应用服务器用于管理到 Oracle 数据库的数据源的内容。其中大部分肯定会转移到 OracleConnectionPoolDataSource。

回答by yawn

Have you considered using the new Oracle UCP? Quote from the 11g feature list(emphasis mine):

您是否考虑过使用新的 Oracle UCP？引自11g 功能列表（重点是我的）：

1.4.1.29 Universal Connection Pool (UCP) for JDBC

Universal Connection Pool for JDBC supersedes Implicit Connection Cache and provides the following functions:

• Connection labeling, connection harvesting, logging, and statistics
• Performanceand stabilization enhancements
• Improved diagnostics and statistics or metrics

UCP for JDBC provides advanced connection pooling functions, improved performance, and better diagnosability of connection issues.

1.4.1.29 JDBC 的通用连接池 (UCP)

JDBC 通用连接池取代隐式连接缓存并提供以下功能：

• 连接标记、连接收集、日志记录和统计
• 性能和稳定性增强
• 改进的诊断和统计或指标

UCP for JDBC 提供了高级连接池功能、改进的性能和更好的连接问题诊断能力。

回答by Tom Neyland

My first piece of advice: Profile.

我的第一条建议：个人资料。

My second piece of advice: Profile.

我的第二条建议：个人资料。

What is slowing your application down, which method calls are causing your application performance to suffer?

是什么让您的应用程序变慢了，哪些方法调用会导致您的应用程序性能下降？

Are you constantly waiting on creating new connections? Then set your MinLimit to higher than 0, same with 'initial-limit' so that you have a few available to start. If you want your MaxLimit to be infinity, set it to '0', a setting of 0 indicates no limit.

您是否一直在等待创建新连接？然后将您的 MinLimit 设置为高于 0，与 'initial-limit' 相同，以便您可以开始使用一些。如果您希望 MaxLimit 为无穷大，请将其设置为“0”，设置为 0 表示没有限制。

Are you creating new connections when really you should use an existing but inactive connection? Set your InactivityTimeout to something besides 0. Same goes for AbandonedConnectionTimeout.

当您真的应该使用现有但不活动的连接时，您是否正在创建新连接？将您的 InactivityTimeout 设置为 0 以外的值。 AbandonedConnectionTimeout 也是如此。

Note, the first thing I would tinker with would be the 'initial-limit'-

请注意，我要修补的第一件事是“初始限制”-

From Oracle about initial-limit

从 Oracle 关于初始限制

This sets the size of the connection cache when the cache is initially created or reinitialized. When this property is set to a value greater than 0, that many connections are pre-created and are ready for use. This property is typically used to reduce the "ramp up" time in priming the cache to its optimal size.

这将在最初创建或重新初始化缓存时设置连接缓存的大小。当此属性设置为大于 0 的值时，许多连接已预先创建并可供使用。此属性通常用于减少将缓存启动到其最佳大小的“加速”时间。