UCP Best Practices

Universal Connection Pool has an extensive collection of tools and APIs to analyze connection leaks and tune up pool properties for optimizing its operation. This section describes these tools and APIs.

The All connections in the Universal Connection Pool are in use exception indicates the shortage of connections in the pool at a given time, which means that the pool is unable to meet the connection borrowing requests of an application. This can happen due to the following reasons:

• An application borrows connections and holds them for a long time without usage, never returning them to the pool. Connection leakage can also happen when an application borrows connections, holds them without usage, and finally returns them to the pool after a very long time. These are the classical connection leakage use cases. You must eliminate non-productive connection borrowings that last for long or infinite periods of time.
• The pool has insufficient capacity for processing the whole flow of connection borrowing requests. In this case, the connection supply of the pool is not enough to perform the expected job and this results in the exception. You must increase the pool capacity in such a case.

Following is a list of useful tools and APIs that you must be aware of prior to debugging and tuning up UCP:

• Abandoned Connection Timeout (ACT): This API enables setting up a timeout for a connection that is borrowed but unused.

  See Also:

  Setting the Abandon Connection Timeout
• Time-To-Live Connection Timeout (TTL): This API too enables setting up a timeout for a connection that is borrowed but unused. However, it also furnishes information about the borrowed connections that are busy with associated on-going processes. It also enables to reclaim these busy connections back to a pool and restores the capacity of the pool, in case of very long processes.

  See Also:

  Setting the Time-To-Live Connection Timeout
• Connection Harvesting Mechanism: This is a special API that enables UCP to always keep certain number of connections available for borrowing and in turn, helps in avoiding the All connections in the Universal Connection Pool are in use exception.

  See Also:

  About Harvesting Connections in UCP
• Connection Wait Timeout (CWT): This is an important property when you try to tune up UCP to avoid the All connections in the Universal Connection Pool are in use exception. When an application attempts to borrow a connection out of a pool and there are no available connections at that time, UCP waits for an available connection to appear for the amount of time that is equal to the value of CWT. By default, CWT is set for 3 seconds. In many applications, you can increase this timeout to enable a pool to wait for longer for an available connection to appear, without getting the All connections in the Universal Connection Pool are in use exception.

  See Also:

  Setting the Connection Wait Timeout
• Maximum Pool Size (MaxPoolSize): This property affects the pool capacity and helps to avoid the All connections in the Universal Connection Pool are in use exception. Oracle recommends to have a small pool size, typically a small number multiplied by the number of cores on a database server. It is better to increase the CWT than making MaxPoolSize very high.

  See Also:

  Setting the Maximum Pool Size
• Inactive Connection Timeout (ICT) in combination with MaxPoolSize: ICT is the timeout property that enables UCP to automatically close available connections that did not have a chance to be borrowed for a particular amount of time, which is specified by the value of ICT. This way, the UCP can avoid connections if the working set of the pool is too big to perform a given throughput. For the pool to auto-tune the required number of connections in the working set of the pool, you can set the MaxPoolSize parameter to a big value and set the ICT accordingly.

  Note:

  Setting the Inactive Connection Timeout
• Pool Size Auto Tuner: This tool enables UCP to automatically tune up pool size for better throughput.
• Pool Statistical Metrics: This is a set of statistics that helps to determine the activities and statistics of a pool, for example, the number of available connections, the number of borrowed connections, and the Average Connection Wait Time (ACWT). ACWT can find a proper value of CWT property for pool tuning. If ACWT is big, then it indicates that the UCP is close to over-using its capacity.

  See Also:

  Pool Statistics
• Pool Logging: UCP has an extensive and flexible logging system. Logging enables you to determine events related to connection opening, connection closing, connection borrowing, and wait time of return and borrow requests.

  See Also:

  Overview of Logging in UCP