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

Question

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.

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

• MinLimit set to 0
• MaxLimit 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

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.

Answer 1

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.

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.

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.

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.

Answer 2

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.

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.

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

From Oracle about initial-limit

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.

Answer 3

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

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
• Performance and stabilization enhancements
• Improved diagnostics and statistics or metrics

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