About Harvesting Connections in UCP

The connection harvesting feature allows a specified number of borrowed connections to be reclaimed when the connection pool reaches a specified number of available connections. This section describes the following concepts:

• Overview of Harvesting Connections in UCP

• Setting a Connection to Harvestable

• Setting the Harvest Trigger Count

• Setting the Harvest Maximum Count

Overview of Harvesting Connections in UCP

This feature helps ensure that a certain number of connections are always available in the pool and helps maximize performance. The feature is particularly useful if an application caches connection handles. Caching is typically performed for performance reasons because it minimizes re-initialization of state necessary for connections to participate in a transaction.

For example, a connection is borrowed from the pool, initialized with necessary session state, and then held in a context object. Holding connections in this manner may cause the connection pool to run out of available connections. The connection harvest feature reclaims the borrowed connections, if appropriate, and allows the connections to be reused.

Connection harvesting is controlled using the HarvestableConnection interface and configured or enabled using two pool properties: Connection Harvest Trigger Count and Connection Harvest Maximum Count. The interface and properties are used together when implementing the connection harvest feature.

Setting a Connection to Harvestable

The setConnectionHarvestable(boolean) method of the oracle.ucp.jdbc.HarvestableConnection interface controls whether or not a connection will be harvested. This method is used as a locking mechanism when connection harvesting is enabled. For example, the method is set to false on a connection when the connection is being used within a transaction and must not be harvested. After the transaction completes, the method is set to true on the connection and the connection can be harvested if required.

Note:

All connections are harvestable, by default, when the connection harvest feature is enabled. If the feature is enabled, the setConnectionHarvestable method should always be used to explicitly control whether a connection is harvestable.

The following example demonstrates using the setConnectionHarvestable method to indicate that a connection is not harvestable when the connection harvest feature attempts to harvest connections:

Connection conn = pds.getConnection();

((HarvestableConnection) conn).setConnectionHarvestable(false);

Setting the Harvest Trigger Count

The connection harvest trigger count specifies the available connection threshold that triggers connection harvesting. For example, if the connection harvest trigger count is set to 10, then connection harvesting is triggered when the number of available connections in the pool drops to 10.

A value of Integer.MAX_VALUE (2147483647 by default) indicates that connection harvesting is disabled. The default value is Integer.MAX_VALUE.

The following example demonstrates enabling connection harvesting by configuring a connection harvest trigger count.

pds.setConnectionHarvestTriggerCount(2);

Setting the Harvest Maximum Count

The connection harvest maximum count property specifies how many borrowed connections should be returned to the pool once the harvest trigger count has been reached. The number of connections actually harvested may be anywhere from 0 to the connection harvest maximum count value. Least recently used connections are harvested first which allows very active user sessions to keep their connections the most.

The harvest maximum count value can range from 0 to the maximum connection property value. The default value is 1. An SQLException is thrown if an out-of-range value is specified.

The following example demonstrates configuring a connection harvest maximum count.

pds.setConnectionHarvestMaxCount(5);

Note:

• If connection harvesting and abandoned connection timeout features are enabled at the same time, then the timeout processing does not reclaim the connections that are designated as nonharvestable.

• If connection harvesting and time-to-live connection timeout features are enabled at the same time, then the timeout processing reclaims the connections that are designated as nonharvestable.