Tuning and updating connection pool properties (OracleConnectionPoolDataSource) for better performance

Question

In the Java web application I'm working on, we use OracleConnectionPoolDataSource to enable a database connection pool. Each getConnection call includes a user ID and user password. Thus, in a certain sense, each user gets his own pool of connections to the database.

We are currently using default values ​​for most properties. This includes

• MinLimit is set to 0
• MaxLimit is set to Integer.MAX_VALUE
• MaxStatementsLimit set to 0
• InactivityTimeout set to 0
• TimeToLiveTimeout set to 0
• AbandonedConnectionTimeout set to 0
• PropertyCheckInterval set to 900
• ConnectionWaitTimeout set to 0

You can learn more about these properties in Connection Cache Properties .

We currently do not have any major problems connecting to the database, but I think that performance could be better. My question is: does anyone have good advice or a good resource that we should consider when setting these values.

Answer 1

The Oracle Application Server performance guide for version 10g Release 3 (10.1.3.1) provides final information on how to optimize connection pool settings .

The information is useful for almost all scenarios associated with an application that uses a connection pool to manage connections to an Oracle database, rather than using the application server used.

For example, it is always recommended that you set a value for the minimum pool size. As for the maximum pool size, the value should not be clearly high, as this may load the listener, especially if the application tends not to close the connections, which leads to a leak.

It is preferable to set a reasonable value for the statement cache, since this allows you to cache prepared statements, which can improve performance.

Timeouts should also be environmentally friendly. For example, the connection wait timeout should not be zero in most cases, as this can cause SQLExceptions when physical connections cannot be initialized in the pool for a sufficient interval. The inactivity timeout should be large enough so that the connections are removed only after a sufficient length of inactivity too low a value will cause physical connections to be created and dropped too often.

EDIT: The guide provided in the Performance Guide belongs to the oracle.jdbc.pool.OracleDataSource class, which uses the Oracle 10g application server for managed data sources for an Oracle database. Most of them are, of course, ported to the OracleConnectionPoolData source.

Answer 2

Have you considered using the new Oracle UCP ? Quote from the list of 11g functions (my attention):

1.4.1.29 Universal Connection Pool (UCP) for JDBC

The universal connection pool for JDBC replaces the implicit connection cache and provides the following functions:

• Connection labeling, data collection, registration and connection statistics
• Performance and stabilization improvements
• Improved diagnostics and statistics or indicators

UCP for JDBC provides advanced pooling features, improved performance, and better diagnosis of connectivity issues.

Answer 3

My first tip: Profile.

My second tip: Profile.

What slows down your application, what method calls cause your application to degrade?

Are you constantly waiting for new connections? Then set MinLimit above 0, the same with the "initial limit" so that you have several available to run. If you want your MaxLimit to be infinite, set it to "0", a value of 0 means no restrictions.

Do you create new connections when you really use an existing but inactive connection? Set InactivityTimeout to 0. The same goes for AbandonedConnectionTimeout.

Please note that the first thing I would like to use would be an "initial limit" -

From Oracle on the starting limit

This sets the size of the cache connection when the cache is initially created or reinitialized. When this property has a value greater than 0, that many connections are pre-created and ready to use. This property is commonly used to reduce the rise time in the soil cache to an optimal size.