Stale Connections in UCP

5.4 Stale Connections in UCP

Stale connections are connections that remain either available or borrowed, but are no longer being used. Stale connections that remain borrowed may affect connection availability.

In addition, stale connections may impact system resources that are used to maintain unused connections for extended periods of time. The pool properties discussed in this section are used to control stale connections.

This section describes the following topics:

Note:

It is good practice to close all connections that are no longer required by an application. Closing connections helps minimize the number of stale connections that remain borrowed.

5.4.1 What is Connection Reuse?

The connection reuse feature allows connections to be gracefully closed and removed from a connection pool after a specific amount of time or after the connection has been used a specific number of times. This feature saves system resources that are otherwise wasted on maintaining unusable connections.

5.4.1.1 Setting the Maximum Connection Reuse Time

The maximum connection reuse time allows connections to be gracefully closed and removed from the pool after a connection has been in use for a specific amount of time. The timer for this property starts when a connection is physically created. Borrowed connections are closed only after they are returned to the pool and the reuse time is exceeded.

This feature is typically used when a firewall exists between the pool tier and the database tier and is setup to block connections based on time restrictions. The blocked connections remain in the pool even though they are unusable. In such scenarios, the connection reuse time is set to a smaller value than the firewall timeout policy.

Note:

The maximum connection reuse time is different from the time-to-live connection timeout. The main difference between the maximum connection reuse time and the time-to-live is that time-to-live can cancel a connection, but maximum connection reuse time never does that. The maximum connection reuse time handler gets applied only in the following cases:

Prior to a connection borrow
Immediately after a connection is returned back to a pool
On periodic basis, with every timeoutCheckInterval

The maximum connection reuse time value is represented in seconds. The default value is 0, which indicates that this feature is disabled. The following example demonstrates configuring a maximum connection reuse time:

pds.setMaxConnectionReuseTime(300);

Starting from Oracle Database Release 23ai, you can use the new system property oracle.ucp.timersAffectAllConnections to change the behavior of the maximum connection reuse time processing. The default value of this property is FALSE, which means that during periodical appropriate time processing, a pool is scanned down to a minimum pool size and the pool never closes connections below the minimum pool size. If the SYSTEM_PROPERTY_TIMERS_AFFECT_ALL_CONNECTIONS system property is set to TRUE, then the periodic poll checks all available connection for the maximum connection reuse time criteria, so the pool size may go below the minimum pool size, replacing the applicable connections.

Related Topics

Setting the Time-To-Live Connection Timeout

5.4.1.2 Setting the Maximum Connection Reuse Count

The maximum connection reuse count allows connections to be gracefully closed and removed from the connection pool after a connection has been borrowed a specific number of times. This property is typically used to periodically recycle connections in order to eliminate issues such as memory leaks.

A value of 0 indicates that this feature is disabled. The default value is 0. The following example demonstrates configuring a maximum connection reuse count:

pds.setMaxConnectionReuseCount(100);

5.4.2 Setting the Connection Validation Timeout

The connection validation timeout specifies the duration within which a borrowed connection from the pool is validated. This is the maximum time for a connection validation operation. If the validation is not completed during this period, then the connection is treated as invalid.

The connection validation timeout value represents seconds. The default value is set to 15. The following example demonstrates configuring a connection validation timeout:

pd.setConnectionValidationTimeout(55);

5.4.3 Setting the Abandon Connection Timeout

The abandoned connection timeout (ACT) enables borrowed connections to be reclaimed back into the connection pool after a connection has not been used for a specific amount of time. Abandonment is determined by monitoring calls to the database.

The abandoned connection timeout feature helps maximize connection reuse and conserves system resources that are otherwise lost on maintaining borrowed connections that are no longer in use.

Note:

Before reclaiming connections for reuse, UCP either cancels or rolls back the connections that have local transactions pending.

The ACT value represents seconds. A value of 0 indicates that the feature is disabled. The default value is set to 0. The following example demonstrates configuring an abandoned connection timeout:

pds.setAbandonedConnectionTimeout(10);

Every connection is reaped after a specific period of time. Either it is reaped when ACT expires, or, if it is immune from ACT, then it is reaped after the immunity expires. If you set ACT on a pool, then the following connection reaping policies apply:

If a statement is executed without calling the Statement.setQueryTimeout method on that statement, then the connection is reaped if ACT is exceeded, even though the connection is waiting for the server to respond to the query.
If a statement is executed with calling the Statement.setQueryTimeout method, then the connection is reaped after the query timeout and ACT have expired. The connection is not reaped while waiting on the query timeout. The expiration of the query timeout is an event that resets the ACT timer. If the ACT expires while waiting for the cancel action that occurs at the expiration of the query time out, then the connection is reaped.
The default query timeout in the UCP is set to zero (0) for an appropriate pool data source, using the PoolDataSource.setQueryTimeout method, if the ACT is set to 0. If the ACT is greater than zero (0), then the default query timeout is set to 60 seconds.
If a connection has two statements: s1 with a query timeout and s2 without a query timeout, then ACT does not reap the connection while s1 waits for the query timeout, but reaps the connection if s2 hangs.

Note that the two statements execute sequentially based on JDBC requirement.

5.4.4 Setting the Time-To-Live Connection Timeout

The time-to-live connection timeout enables borrowed connections to remain borrowed for a specific amount of time before the connection is reclaimed by the pool. This timeout feature helps maximize connection reuse and helps conserve systems resources that are otherwise lost on maintaining connections longer than their expected usage.

Note:

UCP either cancels or rolls back connections that have local transactions pending before reclaiming connections for reuse.

The time-to-live connection timeout value represents seconds. A value of 0 indicates that the feature is disabled. The default value is set to 0. The following example demonstrates configuring a time-to-live connection timeout:

pds.setTimeToLiveConnectionTimeout(18000)

5.4.5 Setting the Connection Wait Timeout

The connection wait timeout specifies how long an application request waits to obtain a connection if there are no longer any connections in the pool. A connection pool runs out of connections if all connections in the pool are being used (borrowed) and if the pool size has reached it's maximum connection capacity as specified by the maximum pool size property. The request receives an SQL exception if the timeout value is reached. The application can then retry getting a connection. This timeout feature improves overall application usability by minimizing the amount of time an application is blocked and provides the ability to implement a graceful recovery.

The connection wait timeout value represents seconds. A value of 0 indicates that the feature is disabled. The default value is set to 3 seconds. The following example demonstrates configuring a connection wait timeout:

pds.setConnectionWaitTimeout(10);

5.4.6 Setting the Inactive Connection Timeout

The inactive connection timeout specifies how long an available connection can remain idle before it is closed and removed from the pool.

This timeout property is only applicable to available connections and does not affect borrowed connections. This property helps conserve resources that are otherwise lost on maintaining connections that are no longer being used. The inactive connection timeout (together with the maximum pool size) enables a connection pool to grow and shrink as application load changes.

The inactive connection timeout value is represented in seconds. A value of 0 indicates that the feature is disabled. The default value is set to 0. The following example demonstrates configuring an inactive connection timeout:

pds.setInactiveConnectionTimeout(60);

Starting from Oracle Database Release 23ai, you can use the new system property oracle.ucp.timersAffectAllConnections to change the behavior of the Inactive Connection Timeout and Maximum Connection Reuse Time properties. If you set this system property to TRUE, then the periodic poll checks all the available connections for the maximum connection reuse time and inactive connection timeout criteria, and closes all the connections that satisfy the criteria. This may make the pool size go below the minimum pool size, resulting in the creation of new connections by the pool to maintain the minimum pool size limit.

5.4.7 Setting the Query Timeout

In Oracle Database 12c Release 2 (12.2.0.1), UCP introduced the queryTimeout property. This property specifies the number of seconds UCP waits for a Statement object to execute. If the limit is exceeded, then a DatabaseException is thrown. Use the setQueryTimeout method for setting this property in the following way:

...
PoolDataSourceImpl pds = new PoolDataSourceImpl();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL(<url>);
pds.setUser("scott");
pds.setPassword(<password>);
pds.setConnectionPoolName("my_pool");
pds.setQueryTimeout(60); // 60 seconds to wait on query
...

5.4.8 Setting the Timeout Check Interval

The timeout check interval property controls how frequently the timeout properties (abandoned connection timeout, time-to-live connection timeout, and inactive connection timeout) are enforced. Connections that have timed-out are reclaimed when the timeout check cycle runs. This means that a connection may not actually be reclaimed by the pool at the moment that the connection times-out. The lag time between the connection timeout and actually reclaiming the connection may be considerable depending on the size of the timeout check interval.

The timeout check interval property represents seconds. The default value is set to 30. The following example demonstrates configuring a property check interval:

pds.setTimeoutCheckInterval(60);