UCP Performance Properties
UCP properties are used to control the connection pool size, handle stale connections, and
balance quick response times. The optimal settings for the pool properties depend on the
application and hardware resources. In many cases, it is necessary to try different settings to find
an optimal balance. Some of the performance properties are mentioned here.
MaxPoolSize specifies the maximum number of connections that a pool maintains to ensure it
does not exhaust system resources. The recommendation is to set it to a value based on the
number of connections expected from the application and tune it as required. The value can also
be set as suggested by the following formula.
MaxPoolSize = (rdbms-cores * n) / sum (pools-per-mid-tier)
where n is an integer with a typical recommended value of 9 or 10
Example:
Consider a single node database server with 4 cores per node and 5 mid-tiers each running a
single JVM with one pool each.
MaxPoolSize = (4 * 10) / (5 * 1) = (4 * 10) / 5 = 40 / 5 = 8
so MaxPoolSize should be 8 for each mid-tier, as a first approximation.
Applications that require an even number of connections per mid-tier can use the above formula.
However, in scenarios where connections required per mid-tier varies, we suggest to compute the
optimal connection workload that the database can sustain then split these connections among
mid-tiers. In the example above, 40 is the total number of connections that can be split among 5
mid-tiers based on the connection requirement.
MinPoolSize specifies the minimum number of available connections that a pool maintains. Set
this value to the minimum number of connections required by your application at any given time
and tune it as required. MinPoolSize should be less than or equal to MaxPoolSize. The default
value is 0.
InitialPoolSize specifies the number of connections that are created when the pool is created or re-
initialized. It should be closer to MinPoolSize, which will let the connection pool start faster. The
default value is 0 which means that no connections are pre-created.
In applications where the number of concurrent active connections are known and memory is not a
concern then set MaxPoolSize, InitialPoolSize, and MinPoolSize to the same value.
MaxStatements specifies the size of the SQL statement for each connection. Statement caching
lets cursors be re-executed without reparsing the statement, eliminating repetitive statement
parsing thereby improving the performance and scalability. By default, statement caching is
disabled.
MaxStatements should be set to the number of frequently used SQL statements by the application.
So, use a number that is neither too high nor too small.
TimeToLiveConnectionTimeout allows a borrowed connection to remain borrowed only for a pre-
determined period of time. When this period expires, the connection is reclaimed back into the
pool. It is enforced even if the connection is in use. This timeout helps maximize connection
reuse and helps conserve system resources. It should be set sufficiently high, based on the
application profile.
6