The following sections are included in this chapter:
Universal Connection Pool (UCP) for JDBC provides a set of runtime statistics for the connection pool. These statistics can be divided into the following two categories:
Noncumulative
These statistics apply only to the current running connection pool instance.
Cumulative
These statistics are collected across multiple pool start/stop cycles.
The oracle.ucp.UniversalConnectionPoolStatistics interface provides methods that are used to query the connection pool statistics. The methods of this interface can be called from a pool-enabled data source and pool-enabled XA data source, using the oracle.ucp.jdbc.PoolDataSource.getStatistics method. For example:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
...
...
int totalConnsCount = pds.getStatistics().getTotalConnectionsCount();
System.out.println("The total connetion count in the pool is "+ totalConnsCount +".");
The oracle.ucp.jdbc.PoolDataSource.getStatistics method can also be called by itself to return all connection pool statistics as a String.
UCP supports all the pool statistics to be in the form of Dynamic Monitoring Service (DMS) metrics. You must include the dms.jar file in the classpath of the application to collect and utilize these DMS metrics.
UCP supports DMS metrics collection in both the pool manager interface and the pool manager MBean. You can use the UnversalConnectionPoolManager.startMetricsCollection method to start collecting DMS metrics for the specified connection pool instance, and use the UnversalConnectionPoolManager.stopMetricsCollection method to stop DMS metrics collection. The metrics update interval can be specified using the UnversalConnectionPoolManager.setMetricUpdateInterval method. The pool manager MBean exports similar operations.
UCP for JDBC provides a set of Oracle RAC run-time statistics that are used to determine how well a connection pool is utilizing Oracle RAC features and are also used to help determine whether the connection pool has been configured properly to use the Oracle RAC features. The statistics report FCF processing information, run-time connection load balance success/failure rate, and affinity context success/failure rate.
The OracleJDBCConnectionPoolStatistics interface that is located in the oracle.ucp.jdbc.oracle package provides methods that are used to query the connection pool for Oracle RAC statistics. The methods of this interface can be called from a pool-enabled and pool-enabled XA data source using the data source's getStatistics method. For example:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
...
Long rclbS = ((OracleJDBCConnectionPoolStatistics)pds.getStatistics()).
getSuccessfulRCLBBasedBorrowCount();
System.out.println("The RCLB success rate is "+rclbS+".");
The data source's getStatistics method can also be called by itself and returns all connection pool statistics as a String and includes the Oracle RAC statistics.
The getFCFProcessingInfo method provides information on recent Fast Connection Failover (FCF) attempts in the form of a String. The FCF information is typically used to help diagnose FCF problems. The information includes the outcome of each FCF attempt (successful or failed), the relevant Oracle RAC instances, the number of connections that were cleaned up, the exception that triggered the FCF attempt failure, and more. The following example demonstrates using the getFCFProcessingInfo method:
Sting fcfInfo = ((OracleJDBCConnectionPoolStatistics)pds.getStatistics()).
getFCFProcessingInfo();
System.out.println("The FCF information: "+fcfInfo+".");
Following is a sample output string from the getFCFProcessingInfo() method:
Oct 28, 2008 12:34:02 SUCCESS <Reason:planned> <Type:SERVICE_UP> \
<Service:"svvc1"> <Instance:"inst1"> <Db:"db1"> \
Connections:(Available=6 Affected=2 FailedToProcess=0 MarkedDown=2 Closed=2) \
(Borrowed=6 Affected=2 FailedToProcess=0 MarkedDown=2 MarkedDeferredClose=0 Closed=2) \
TornDown=2 MarkedToClose=2 Cardinality=2
...
Oct 28, 2008 12:09:52 SUCCESS <Reason:unplanned> <Type:SERVICE_DOWN> \
<Service:"svc1"> <Instance:"inst1"> <Db:"db1"> \
Connections:(Available=6 Affected=2 FailedToProcess=0 MarkedDown=2 Closed=2) \
(Borrowed=6 Affected=2 FailedToProcess=0 MarkedDown=2 MarkedDeferredClose=0 Closed=2)
...
Oct 28, 2008 11:14:53 FAILURE <Type:HOST_DOWN> <Host:"host1"> \
Connections:(Available=6 Affected=4 FailedToProcess=0 MarkedDown=4 Closed=4) \
(Borrowed=6 Affected=4 FailedToProcess=0 MarkedDown=4 MarkedDeferredClose=0 Closed=4)
If you enable logging, then the preceding information will also be available in the UCP logs and you will be able to verify the FCF outcome.
The run-time connection load balance statistics are used to determine if a connection pool is effectively utilizing the Oracle RAC database's run-time connection load balancing feature. The statistics report how many requests successfully used the run-time connection load balancing algorithms and how many requests failed to use the algorithms. The getSuccessfulRCLBBasedBorrowCount method and the getFailedRCLBBasedBorrowCount method, respectively, are used to get the statistics. The following example demonstrates using the getFailedRCLBBasedBorrowCount method:
Long rclbF = ((OracleJDBCConnectionPoolStatistics)pds.getStatistics()).
getFailedRCLBBasedBorrowCount();
System.out.println("The RCLB failure rate is: "+rclbF+".");
A high failure rate may indicate that the RAC Load Balancing Advisory or connection pool is not configured properly.
The connection affinity statistics are used to determine if a connection pools is effectively utilizing connection affinity. The statistics report the number of borrow requests that succeeded in matching the affinity context and how many requests failed to match the affinity context. The getSuccessfulAffinityBasedBorrowCount method and the getFailedAffinityBasedBorrowCount method, respectively, are used to get the statistics. The following example demonstrates using the getFailedAffinityBasedBorrowCount method:
Long affF = ((OracleJDBCConnectionPoolStatistics)pds.getStatistics()).
getFailedAffinityBasedBorrowCount();
System.out.println("The connection affinity failure rate is: "+affF+".");
UCP for JDBC leverages the JDK logging facility (java.util.logging). Logging is not enabled by default and must be configured in order to print log messages. Logging can be configured using a log configuration file as well as through API-level configuration.
|
Note: The default log level isnull. This ensures that a parent logger's log level is used by default. |
Logging can be configured using a properties file. The location of the properties file must be set as a Java property for the logging configuration file property. For example:
java -Djava.util.logging.config.file=log.properties
The logging properties file defines the handler to use for writing logs, the formatter to use for formatting logs, a default log level, as well as log levels for specific packages or classes. For example:
handlers = java.util.logging.ConsoleHandler java.util.logging.ConsoleHandler.level = ALL java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter oracle.ucp.level = FINEST oracle.ucp.jdbc.PoolDataSource = WARNING
A custom formatter is included with UCP for JDBC and can be entered as the value for the formatter property. For example:
java.util.logging.ConsoleHandler.formatter = oracle.ucp.util.logging.UCPFormatter
You can also download the ucpdemos.jar file, which is shipped with UCP, from Oracle Technology Network (OTN). This file contains a list of sample logging property files. For example, this file contains the logging property file that can be used for troubleshooting the Fast Connection Failover (FCF) feature.
Logging can be dynamically configured though either the UCP for JDBC API or the JDK API. When using the UCP for JDBC API, logging is configured using a connection pool manager. When using the JDK, logging is configured using the java.util.logging implementation.
The following example demonstrates using the UCP for JDBC API to configure logging:
UniversalConnectionPoolManager mgr = UniversalConnectionPoolManagerImpl. getUniversalConnectionPoolManager(); mgr.setLogLevel(Level.FINE);
The following example demonstrate using the JDK logging implementation directly:
Logger.getLogger("oracle.ucp").setLevel(Level.FINEST);
Logger.getLogger("oracle.ucp.jdbc.PoolDataSource").setLevel(Level.FINEST);
The following list describes each of the log levels that are supported for JDBC. Levels lower than FINE produce output that may not be meaningful to users. Levels lower than FINER will produce very large volumes of output.
INTERNAL_ERROR – Internal Errors
SEVERE – SQL Exceptions
WARNING – SQL Warnings and other invisible problems
INFO – Public events such as connection attempts or Oracle RAC events
CONFIG – SQL statements
FINE – Public APIs
TRACE_10 – Internal events
FINER – Internal APIs
TRACE_20 – Internal debug
TRACE_30 – High volume internal APIs
FINEST – High volume internal debug
Many UCP methods throw the UniversalConnectionPoolException, with exception chaining supported. You can call the printStackTrace method on the thrown exception, to identify the root cause of the exception. The UniversalConnectionPoolException includes standard Oracle error codes that are in the range of 45000 and 45499. The getErrorCode method can be used to retrieve the error code for an exception.
This chapter describes the following interfaces:
The AbandonedConnectionTimeoutCallback callback interface is used for the abandoned connection timeout feature. This feature enables applications to provide customized handling of abandoned connections.The callback object either uses one of its logical connection proxies or it is registered with each pooled connection. This enables applications to perform customized handling, when a particular connection is deemed abandoned by the pool. The handleTimedOutConnection method is invoked when a borrowed connection is deemed abandoned by the Universal Connection Pool. Applications can perform one of the following operations on the connection:
Completely override the pool-handling process
Invoke additional handling actions
Assume the default pool-handling
The JDBC applications can invoke cancel, close, and rollback methods on the abandoned connection within the handleTimedOutConnection method.
|
Note: If you try to register more than oneAbandonedConnectionTimeoutCallback interface on the same connection, then it results in an exception. This exception can be a UniversalConnectionPoolException at the pool layer or a java.sql.SQLException, specific to the type of the UCP Adapter like JDBC, JCA and so on. |
The TimeToLiveConnectionTimeoutCallback callback interface used for the time-to-live (TTL) connection timeout feature. This enables applications to provide customized handling for TTL timed-out connections.
The callback object either uses one of its logical connection proxies or it is registered with each pooled connection. This enables applications to perform customized handling, when the TTL of the particular connection times out.
The handleTimedOutConnection method is invoked when a borrowed connection is found to be TTL timed-out by the Universal Connection Pool. Applications can perform one of the following operations on the connection:
Completely override the pool-handling process
Invoke additional handling actions
Assume the default pool-handling
The JDBC applications can invoke cancel, close, and rollback methods on the abandoned connection within the handleTimedOutConnection method.
|
Note: If you try to register more than oneTimeToLiveConnectionTimeoutCallback interface on the same connection, then it results in an exception. This exception can be a UniversalConnectionPoolException at the pool layer or a java.sql.SQLException, specific to the type of the UCP Adapter like JDBC, JCA, and so on. |
Developer's Guide
11g Release 2 (11.2)
E12265-02
September 2009
This guide provides instructions for using Oracle's Universal Connection Pooling API. The API is JDBC driver agnostic.
Oracle Universal Connection Pool for JDBC Developer's Guide, 11g Release 2 (11.2)
E12265-02
Copyright © 1999, 2009, Oracle and/or its affiliates. All rights reserved.
Primary Author: Tulika Das, Joseph Ruzzi
Contributor: Rajkumar Irudayaraj, Tong Zhou, Yuri Dolgov, Paul Lo, Kuassi Mensah
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
The following sections are included in this chapter:
A connection pool is a cache of database connection objects. The objects represent physical database connections that can be used by an application to connect to a database. At run time, the application requests a connection from the pool. If the pool contains a connection that can satisfy the request, it returns the connection to the application. If no connections are found, a new connection is created and returned to the application. The application uses the connection to perform some work on the database and then returns the object back to the pool. The connection is then available for the next connection request.
Connection pools promote the reuse of connection objects and reduce the number of times that connection objects are created. Connection pools significantly improve performance for database-intensive applications because creating connection objects is costly both in terms of time and resources. Tasks such as network communication, reading connection strings, authentication, transaction enlistment, and memory allocation all contribute to the amount of time and resources it takes to create a connection object. In addition, because the connections are already created, the application waits less time to get the connection.
Connection pools often provide properties that are used to optimize the performance of a pool. The properties control behaviors such as the minimum and maximum number of connections allowed in the pool or the amount of time a connection can remain idle before it is returned to the pool. The best configured connection pools balance quick response times with the memory spent maintaining connections in the pool. It is often necessary to try different settings until the best balance is achieved for a specific application.
Applications that are database-intensive generally benefit the most from connection pools. As a policy, applications should use a connection pool whenever database usage is known to affect application performance.
Connection pools provide the following benefits:
Reduces the number of times new connection objects are created.
Promotes connection object reuse.
Quickens the process of getting a connection.
Reduces the amount of effort required to manually manage connection objects.
Minimizes the number of stale connections.
Controls the amount of resources spent on maintaining connections.
UCP for JDBC provides a connection pool implementation for caching JDBC connections. Java applications that are database-intensive use the connection pool to improve performance and better utilize system resources.
A UCP JDBC connection pool can use any JDBC driver to create physical connections that are then maintained by the pool. The pool can be configured and provides a full set of properties that are used to optimize pool behavior based on the performance and availability requirements of an application. For more advanced applications, UCP for JDBC provides a pool manager that can be used to manage a pool instance.
The pool also leverages many high availability and performance features available through an Oracle Real Application Clusters (RAC) database. These features include Fast Connection Failover (FCF), run-time connection load balancing, and connection affinity.
|
Note: Starting from Oracle Database 11g Release 2 (11.2), FCF is also supported by Oracle Restart on a single instance database. Oracle Restart was previously known as Single-Instance High Availability (SIHA). For more information on Oracle Restart, refer to Oracle Database Administrator's Guide. |
Applications use a UCP for JDBC pool-enabled data source to get connections from a UCP JDBC connection pool instance. The PoolDataSource data source is used for getting regular connections (java.sql.Connection), and the PoolXADataSource data source is used for getting XA connections (javax.sql.XAConnection). The same pool features are included in both XA and non-XA UCP JDBC connection pools.
The pool-enabled data source relies on a connection factory class to create the physical connections that are maintained by the pool. An application can choose to use any factory class that is capable of creating Connection or XAConnection objects. The pool-enabled data sources provide a method for setting the connection factory class, as well as methods for setting the database URL and database credentials that are used by the factory class to connect to a database.
Applications borrow a connection handle from the pool to perform work on a database. Once the work is completed, the connection is closed and the connection handle is returned to pool and is available to be used again. Figure 1-1 below shows the conceptual view of the interaction between an application and a UCP JDBC connection pool.
See Chapter 3, "Getting Database Connections in UCP," for more information on using pool-enabled data sources and borrowing database connections.
UCP JDBC Connection pool properties are configured through methods available on the pool-enabled data source. The pool properties are used to control the pool size, handle stale connections, and make autonomous decisions about how long connections can remain borrowed before they are returned to the pool. The optimal settings for the pool properties depend on the application and hardware resources. Typically, there is a trade-off between the time it takes for an application to get a connection versus the amount of memory it takes to maintain a certain pool size. In many cases, experimentation is required to find the optimal balance to achieve the desired performance for a specific application.
See Chapter 4, "Optimizing Universal Connection Pool Behavior," for more information on setting connection pool properties.
UCP for JDBC includes a connection pool manager that is used by applications that require administrative control over a connection pool. The manager is used to explicitly control the lifecycle of a pool and to perform maintenance on a pool. The manager also provides the opportunity for an application to expose the pool and its manageability through an administrative console.
See Chapter 7, "Using the Connection Pool Manager," for more information on explicitly controlling a connection pool.
A UCP JDBC connection pool provides many features that are used to ensure high connection availability and performance. Many of these features, such as refreshing a pool or validating connections, are generic and work across driver and database implementations. Some of these features, such as run-time connection load balancing, and connection affinity, require the use of an Oracle JDBC driver and an Oracle RAC database.
See Chapter 8, "Using Oracle RAC Features," for more information on using Oracle RAC features.
The following sections are included in this chapter:
This chapter provides instructions for setting connection pool properties in order to optimize pooling behavior. Upon creation, UCP JDBC connection pools are pre-configured with a default setup. The default setup provides a general, all-purpose connection pool. However, different applications may have different database connection requirements and may want to modify the default behavior of the connection pool. Behaviors, such as pool size and connection timeouts can be configured and can improve overall connection pool performance as well as connection availability. In many cases, the best way to tune a connection pool for a specific application is to try different property combinations using different values until optimal performance and throughput is achieved.
Setting Connection Pool Properties
Connection pool properties are set either when getting a connection through a pool-enabled data source or when creating a connection pool using the connection pool manager.
The following example demonstrates setting connection pool properties though a pool-enabled data source:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionPoolName("JDBC_UCP");
pds.setMinPoolSize(4);pds.setMaxPoolSize(20);
...
The following example demonstrates setting connection pool properties when creating a connection pool using the connection pool manager:
UniversalConnectionPoolManager mgr = UniversalConnectionPoolManagerImpl. getUniversalConnectionPoolManager();
pds.setConnectionPoolName("JDBC_UCP");
pds.setMinPoolSize(4);pds.setMaxPoolSize(20);
...
mgr.createConnectionPool(pds);
|
Tip: UCP JDBC connection pool properties may be set in any order and can be dynamically changed at runtime. For example,setMaxPoolSize could be changed after the pool is created and the pool recognizes the new value and adapts accordingly. |
UCP JDBC connection pools include a set of properties that are used to control the size of the pool. The properties allow the number of connections in the pool to increase and decrease as demand increases and decreases. This dynamic behavior helps conserve system resources that are otherwise lost on maintaining unnecessary connections.
The initial pool size property specifies the number of available connections that are created when the connection pool is initially created or re-initialized. This property is typically used to reduce the ramp-up time incurred by priming the pool to its optimal size.
A value of 0 indicates that no connections are pre-created. The default value is 0. The following example demonstrates configuring an initial pool size:
pds.setInitialPoolSize(5);
If the initial pool size property is greater than the maximum pool size property, then only the maximum number of connections are initialized.
If the initial pool size property is less than the minimum pool size property, then only the initial number of connections are initialized and maintained until enough connections are created to meet the minimum pool size value.
The minimum pool size property specifies the minimum amount of available and borrowed connections that a pool maintains. A connection pool always tries to return to the minimum pool size specified unless the minimum amount has yet to be reached. For example, if the minimum limit is set to 10 and only 2 connections are ever created and borrowed, then the number of connections maintained by the pool remains at 2.
This property allows the number of connections in the pool to decrease as demand decreases. At the same time, the property ensures that system resources are not wasted on maintaining connections that are unnecessary.
The default value is 0. The following example demonstrates configuring a minimum pool size:
pds.setMinPoolSize(2);
The maximum pool size property specifies the maximum number of available and borrowed (in use) connections that a pool maintains. If the maximum number of connections are borrowed, no connections will be available until a connection is returned to the pool.
This property allows the number of connections in the pool to increase as demand increases. At the same time, the property ensures that the pool doesn't grow to the point of exhausting a system's resources, which ultimately affects an application's performance and availability.
A value of 0 indicates that no connections are maintained by the pool. An attempt to get a connection results in an exception. The default value is to allow the pool to continue to create connections up to Integer.MAX_VALUE (2147483647 by default). The following example demonstrates configuring a maximum pool size:
pds.setMaxPoolSize(100);
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 otherwise wasted on maintaining unused connections for extended periods of time. The pool properties discussed in this section are used to control stale connections.
|
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. |
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.
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 has been 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 time-to-live connection timeout starts when a connection is borrowed from the pool; while, the maximum connection reuse time starts when the connection is physically created. In addition, with a time-to-live timeout, a connection is closed and returned to the pool for reuse if the timeout expires during the borrowed period. With maximum connection reuse time, a connection is closed and discarded from the pool after the timeout expires. See Setting the Time-To-Live Connection Timeout. |
The maximum connection reuse time value represents seconds. A value of 0 indicates that this feature is disabled. The default value is 0. The following example demonstrates configuring a maximum connection reuse time:
pds.setMaxConnectionReuseTime(300);
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);
The abandoned connection timeout 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. This 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: UCP for JDBC either cancels or rolls back connections that have local transactions pending before reclaiming connections for reuse. |
The abandoned 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 an abandoned connection timeout:
pds.setAbandonConnectionTimeout(10);
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 for JDBC 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)
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);
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) allows a connection pool to grow and shrink as application load changes.
The inactive 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 an inactive connection timeout:
pds.setInactiveConnectionTimeout(60);
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.setTimoutCheckInterval(60);
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 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.
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, thesetConnectionHarvestable 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);
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);
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:
For more information about abandoned connection timeout feature and time-to-live connection timeout feature, refer to Chapter 6. |
Statement caching makes working with statements more efficient. Statement caching improves performance by caching executable statements that are used repeatedly and makes it unnecessary for programmers to explicitly reuse prepared statements. Statement caching eliminates overhead due to repeated cursor creation, repeated statement parsing and creation and reduces overhead of communication between applications and the database. Statement caching and reuse is transparent to an application. Each statement cache is associated with a physical connection. That is, each physical connection will have its own statement cache.
The match criteria for cached statements are as follows:
The SQL string in the statement must be the same (case-sensitive) to one in the cache.
The statement type must be the same (prepared or callable) to the one in the cache.
The scrollable type of result sets produced by the statement must be the same (forward-only or scrollable) as the one in the cache.
Statement caching is implemented and enabled differently depending on the JDBC driver vendor. The instructions in this section are specific to Oracle's JDBC driver. Statement caching on other vendors' drivers can be configured by setting a connection property on a connection factory. See "Setting Connection Properties" for information on setting connection properties. In addition, refer to the JDBC vendor's documentation to determine whether statement caching is supported and if it can be set as a connection property. UCP for JDBC does support JDBC 4.0 (JDK16) APIs to enable statement pooling if a JDBC vendor supports it.
The maximum number of statements property specifies the number of statements to cache for each connection. The property only applies to the Oracle JDBC driver. If the property is not set, or if it is set to 0, then statement caching is disabled. By default, statement caching is disabled. When statement caching is enabled, a statement cache is associated with each physical connection maintained by the connection pool. A single statement cache is not shared across all physical connections.
The following example demonstrates enabling statement caching:
pds.setMaxStatements(10);
Determining the Statement Cache Size
The cache size should be set to the number of distinct statements the application issues to the database. If the number of statements that an application issues to the database is unknown, use the JDBC performance metrics to assist with determining the statement cache size.
Statement Cache Size Resource Issues
Each connection is associated with its own statement cache. Statements held in a connection's statement cache may hold on to database resources. It is possible that the number of opened connections combined with the number of cached statements for each connection could exceed the limit of open cursors allowed for the database. This issue may be avoided by reducing the number of statements allowed in the cache, or by increasing the limit of open cursors allowed by the database.
The following sections are included in this chapter:
Applications often initialize connections retrieved from a connection pool before using the connection. The initialization varies and could include simple state re-initialization that requires method calls within the application code or database operations that require round trips over the network. The cost of such initialization may be significant.
Labeling connections allows an application to attach arbitrary name/value pairs to a connection. The application can request a connection with the desired label from the connection pool. By associating particular labels with particular connection states, an application can retrieve an already initialized connection from the pool and avoid the time and cost of re-initialization. The connection labeling feature does not imposes any meaning on user-defined keys or values; the meaning of user-defined keys and values is defined solely by the application.
Some of the examples for connection labeling include, role, NLS language settings, transaction isolation levels, stored procedure calls, or any other state initialization that is expensive and necessary on the connection before work can be executed by the resource.
Connection labeling is application-driven and requires the use of two interfaces. The oracle.ucp.jdbc.LabelableConnection interface is used to apply and remove connection labels, as well as retrieve labels that have been set on a connection. The oracle.ucp.ConnectionLabelingCallback interface is used to create a labeling callback that determines whether or not a connection with a requested label already exists. If no connections exist, the interface allows current connections to be configured as required. The methods of these interfaces are described in detail throughout this chapter.
A labeling callback is used to define how the connection pool selects labeled connections and allows the selected connection to be configured before returning it to an application. Applications that use the connection labeling feature must provide a callback implementation.
A labeling callback is used when a labeled connection is requested but there are no connections in the pool that match the requested labels. The callback determines which connection requires the least amount of work in order to be re-configured to match the requested label and then allows the connection's labels to be updated before returning the connection to the application.
To create a labeling callback, an application implements the oracle.ucp.ConnectionLabelingCallback interface. One callback is created per connection pool. The interface provides two methods as shown below:
public int cost(Properties requestedLabels, Properties currentLabels); public boolean configure(Properties requestedLabels, Connection conn);
cost – This method projects the cost of configuring connections considering label-matching differences. Upon a connection request, the connection pool uses this method to select a connection with the least configuration cost.
configure – This method is called by the connection pool on the selected connection before returning it to the application. The method is used to set the state of the connection and apply or remove any labels to/from the connection.
The connection pool iterates over each connection available in the pool. For each connection, it calls the cost method. The result of the cost method is an integer which represents an estimate of the cost required to reconfigure the connection to the required state. The larger the value, the costlier it is to reconfigure the connection. The connection pool always returns connections with the lowest cost value. The algorithm is as follows:
If the cost method returns 0 for a connection, the connection is a match. The connection pool calls configure on the connection found and returns the connection.
If the cost method returns a value greater than 0, then the connection pool iterates until it finds a connection with a cost value of 0 or runs out of available connections.
If the pool has iterated through all available connections and the lowest cost of a connection is Integer.MAX_VALUE (2147483647 by default), then no connection in the pool is able to satisfy the connection request. The pool creates and returns a new connection. If the pool has reached the maximum pool size (it cannot create a new connection), then the pool either throws an SQL exception or waits if the connection wait timeout attribute is specified.
If the pool has iterated through all available connections and the lowest cost of a connection is less than Integer.MAX_VALUE, then the configure method is called on the connection and the connection is returned. If multiple connections are less than Integer.MAX_VALUE, the connection with the lowest cost is returned.
|
Note: A cost of0 does not imply that requestedLabels equals currentLabels. |
The following example demonstrates a simple labeling callback implementation that implements both the cost and configure methods. The callback is used to find a labeled connection that is initialized with a specific transaction isolation level.
class MyConnectionLabelingCallback
implements ConnectionLabelingCallback {
public MyConnectionLabelingCallback()
{
}
public int cost(Properties reqLabels, Properties currentLabels)
{
// Case 1: exact match
if (reqLabels.equals(currentLabels))
{
System.out.println("## Exact match found!! ##");
return 0;
}
// Case 2: some labels match with no unmatched labels
String iso1 = (String) reqLabels.get("TRANSACTION_ISOLATION");
String iso2 = (String) currentLabels.get("TRANSACTION_ISOLATION");
boolean match =
(iso1 != null && iso2 != null && iso1.equalsIgnoreCase(iso2));
Set rKeys = reqLabels.keySet();
Set cKeys = currentLabels.keySet();
if (match && rKeys.containsAll(cKeys))
{
System.out.println("## Partial match found!! ##");
return 10;
}
// No label matches to application's preference.
// Do not choose this connection.
System.out.println("## No match found!! ##");
return Integer.MAX_VALUE;
}
public boolean configure(Properties reqLabels, Object conn)
{
try
{
String isoStr = (String) reqLabels.get("TRANSACTION_ISOLATION");
((Connection)conn).setTransactionIsolation(Integer.valueOf(isoStr));
LabelableConnection lconn = (LabelableConnection) conn;
// Find the unmatched labels on this connection
Properties unmatchedLabels =
lconn.getUnmatchedConnectionLabels(reqLabels);
// Apply each label <key,value> in unmatchedLabels to conn
for (Map.Entry<Object, Object> label : unmatchedLabels.entrySet())
{
String key = (String) label.getKey();
String value = (String) label.getValue();
lconn.applyConnectionLabel(key, value);
}
}
catch (Exception exc)
{
return false;
}
return true;
}
}
A pool-enabled data source provides the registerConnectionLabelingCallback(ConnectionLabelingCallback callback) method for registering labeling callbacks. Only one callback may be registered on a connection pool. The following example demonstrates registering a labeling callback that is implemented in the MyConnectionLabelingCallback class:
MyConnectionLabelingCallback callback = new MyConnectionLabelingCallback(); pds.registerConnectionLabelingCallback( callback );
Labels are applied on a borrowed connection using the applyConnectionLabel method from the LabelableConnection interface. This method is typically called from the configure method of the labeling callback. Any number of connection labels may be cumulatively applied on a borrowed connection. Each time a label is applied to a connection, the supplied key/value pair is added to the collection of labels already applied to the connection. Only the last applied value is retained for any given key.
|
Note: A labeling callback must be registered on the connection pool before a label can be applied on a borrowed connection; otherwise, an exception is thrown. See "Implementing a Labeling Callback in UCP". |
The following example demonstrates initializing a connection with a transaction isolation level and then applying a label to the connection:
String pname = "property1"; String pvalue = "value"; Connection conn = pds.getConnection(); // initialize the connection as required. conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); ((LabelableConnection) conn).applyConnectionLabel(pname, pvalue);
In order to remove a given key from the set of connection labels applied, apply a label with the key to be removed and a null value. This may be used to clear a particular key/value pair from the set of connection labels.
A pool-enabled data source provides two getConnection methods that are used to borrow a labeled connection from the pool. The methods are shown below:
public Connection getConnection(java.util.Properties labels )
throws SQLException;
public Connection getConnection( String user, String password,
java.util.Properties labels )
throws SQLException;
The methods require that the label be passed to the getConnection method as a Properties object. The following example demonstrates getting a connection with the label property1, value.
String pname = "property1"; String pvalue = "value"; Properties label = new Properties(); label.setProperty(pname, pvalue); Connection conn = pds.getConnection(label);
A connection may have multiple labels that each uniquely identifies the connection based on some desired criteria. The getUnmatchedConnectionLabels method is used to verify which connection labels matched from the requested labels and which did not. The method is used after a connection with multiple labels is borrowed from the connection pool and is typically used by a labeling callback. The following example demonstrates checking for unmatched labels.
String pname = "property1"; String pvalue = "value"; Properties label = new Properties(); label.setProperty(pname, pvalue); Connecion conn = pds.getConnection(label); Properties unmatched = ((LabelableConnection) connection).getUnmatchedConnectionLabels (label);
The removeConnectionLabel method is used to remove a label from a connection. This method is used after a labeled connection is borrowed from the connection pool. The following example demonstrates removing a connection label.
String pname = "property1"; String pvalue = "value"; Properties label = new Properties(); label.setProperty(pname, pvalue); Connection conn = pds.getConnection(label); ((LabelableConnection) conn).removeConnectionLabel(pname);
The following sections are included in this chapter:
An application borrows connections using a pool-enabled data source. The UCP for JDBC API provides two pool-enabled data sources; one for borrowing regular connections; and one for borrowing XA connections. These data sources provide access to UCP JDBC connection pool functionality and include a set of getConnection methods that are used to borrow connections. The same pool features are included in both XA and non-XA UCP JDBC connection pools.
UCP JDBC connection pools maintain both available connections and borrowed connections. A connection is reused from the pool if an application requests to borrow a connection that matches an available connection. A new connection is created if no available connection in the pool match the requested connection. The number of available connections and borrowed connections are subjected to pool properties such as pool size, timeout intervals, and validation rules.
|
Note: The instructions in this section use a pool-enabled data source to implicitly create and start a connection pool. See Chapter 7, "Using the Connection Pool Manager"for instructions on using the connection pool manager to explicitly create a connection pool. |
UCP for JDBC provides a pool-enabled data source (oracle.ucp.jdbc.PoolDataSource) that is used to get connections to a database. The oracle.ucp.jdbc.PoolDataSourceFactory factory class provides a getPoolDataSource() method that creates the pool-enabled data source instance. For example:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
The pool-enabled data source requires a connection factory class in order to get an actual physical connection. The connection factory is typically provided as part of a JDBC driver and can be a data source itself. A UCP JDBC connection pool can use any JDBC driver to create physical connections that are then maintained by the pool. The setConnectionFactoryClassName(String) method is used to define the connection factory for the pool-enabled data source instance. The following example uses Oracle's oracle.jdbc.pool.OracleDataSource connection factory class included with the JDBC driver. If you are using a different vendor's JDBC driver, refer to the vendor's documentation for an appropriate connection factory class.
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
In addition to the connection factory class, a pool-enabled data source requires the URL, user name, and password that is used to connect to a database. A pool-enabled data source instance includes methods to set each of these properties. The following example uses an Oracle JDBC Thin driver syntax. If you are using a different vendor's JDBC driver, refer to the vendor's documentation for the appropriate URL syntax to use.
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("user");
pds.setPassword("password");
Lastly, a pool-enabled data source provides a set of getConnection methods. The methods include:
getConnection() – Returns a connection that is associated with the user name and password that was used to connect to the database.
getConnection(String username, String password) – Returns a connection that is associated with the given user name and password.
getConnection(java.util.Properties labels) – Returns a connection that matches a given label. See Chapter 5, "Labeling Connections in UCP," for detailed information on using connection labels.
getConnection(String username, String password, java.util.Properties labels) – Returns a connection that is associated with a given user name and password and that matches a given label. See Chapter 5, "Labeling Connections in UCP," for detailed information on using connection labels.
An application uses the getConnection methods to borrow a connection handle from the pool that is of the type java.sql.Connection. If a connection handle is already in the pool that matches the requested connection (same URL, user name, and password) then it is returned to the application; or else, a new connection is created and a new connection handle is returned to the application. An example for both Oracle and MySQL are provided.
Oracle Example
The following example demonstrates borrowing a connection when using Oracle's JDBC Thin driver:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("<user>");
pds.setPassword("<password>");
Connection conn = pds.getConnection();
MySQL Example
The following example demonstrates borrowing a connection when using the Connector/J JDBC driver from MySQL:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("com.mysql.jdbc.jdbc2.optional. MysqlDataSource");
pds.setURL("jdbc:mysql://host:3306/dbname");
pds.setUser("<user>");
pds.setPassword("<password>");
Connection conn = pds.getConnection();
UCP for JDBC provides a pool-enabled XA data source (oracle.ucp.jdbc.PoolXADataSource) that is used to get XA connections that can be enlisted in a distributed transaction. UCP JDBC XA pools have the same features as non-XA UCP JDBC pools. The oracle.ucp.jdbc.PoolDataSourceFactory factory class provides a getPoolXADataSource() method that creates the pool-enabled XA data source instance. For example:
PoolXADataSource pds = PoolDataSourceFactory.getPoolXADataSource();
A pool-enabled XA data source instance, like a non-XA data source instance, requires the connection factory, URL, user name, and password in order to get an actual physical connection. These properties are set in the same way as a non-XA data source instance (see above). However, an XA-specific connection factory class is required to get XA connections. The XA connection factory is typically provided as part of a JDBC driver and can be a data source itself. The following example uses Oracle's oracle.jdbc.xa.client.OracleXADataSource XA connection factory class included with the JDBC driver. If a different vendor's JDBC driver is used, refer to the vendor's documentation for an appropriate XA connection factory class.
pds.setConnectionFactoryClassName("oracle.jdbc.xa.client.OracleXADataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("user");
pds.setPassword("password");
Lastly, a pool-enabled XA data source provides a set of getXAConnection methods that are used to borrow a connection handle from the pool that is of the type javax.sql.XAConnection. The getXAConnection methods are the same as the getConnection methods previously described. The following example demonstrates borrowing an XA connection.
PoolXADataSource pds = PoolDataSourceFactory.getPoolXADataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.xa.client.OracleXADataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("<user>");
pds.setPassword("<password>");
XAConnection conn = pds.getXAConnection();
Oracle's connection factories support properties that configure connections with specific features. UCP for JDBC pool-enabled data sources provide the setConnectionProperties(Properties) method, which is used to set properties on a given connection factory. The following example demonstrates setting connection properties for Oracle's JDBC driver. If you are using a different vendor's JDBC driver, refer to their documentation to check whether setting properties in this manner is supported and what properties are available:
Properties connProps = new Properties();
connProps.put("fixedString", false);
connProps.put("remarksReporting", false);
connProps.put("restrictGetTables", false);
connProps.put("includeSynonyms", false);
connProps.put("defaultNChar", false);
connProps.put("AccumulateBatchResult", false);
pds.setConnectionProperties(connProps);
The UCP JDBC connection pool does not remove connections that are already created if setConnectionProperties is called after the pool is created and in use.
A connection can be borrowed from a connection pool by performing a JNDI look up for a pool-enabled data source and then calling getConnection() on the returned object. The pool-enabled data source must first be bound to a JNDI context and a logical name. This assumes that an application includes a Service Provider Interface (SPI) implementation for a naming and directory service where object references can be registered and located.
The following example uses Sun's file system JNDI service provider, which can be downloaded from the JNDI software download page:
http://java.sun.com/products/jndi/downloads/index.html
The example demonstrates creating an initial context and then performing a lookup for a pool-enabled data source that is bound to the name MyPooledDataSource. The object returned is then used to borrow a connection from the connection pool.
Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory"); env.put(Context.PROVIDER_URL, "file:/tmp"); ctx = new InitialContext(env); PoolDataSource jpds = (PoolDataSource)ctx.lookup(MyPooledDataSource); Connection conn = jpds.getConnection();
In the example, MyPoolDataSource must be bound to the context. For example:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("<user>");
pds.setPassword("<password>");
ctx.bind(MyPooledDataSource, pds);
UCP JDBC connection pools are configured using connection pool properties. The properties have get and set methods that are available through a pool-enabled data source instance. The methods are a convenient way to programmatically configure a pool. If no pool properties are set, then a connection pool uses default property values.
The following example demonstrates configuring connection pool properties. The example sets the connection pool name and the maximum/minimum number of connections allowed in the pool. See Chapter 4, "Optimizing Universal Connection Pool Behavior," for a detailed description of all the supported properties as well as their default values.
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionPoolName("JDBC_UCP");
pds.setMinPoolSize(4);pds.setMaxPoolSize(20);
UCP JDBC connection pool properties may be set in any order and can be dynamically changed at runtime. For example, setMaxPoolSize could be changed at any time and the pool recognizes the new value and adapts accordingly.
Connections can be validated using pool properties when the connection is borrowed, and also programmatically using the ValidConnection interface. Both approaches are detailed in this section. Invalid connections can affect application performance and availability.
A connection can be validated by executing an SQL statement on a connection when the connection is borrowed from the connection pool. Two connection pool properties are used in conjunction in order to enable connection validation:
setValidateConnectionOnBorrow(boolean) – Specifies whether or not connections are validated when the connection is borrowed from the connection pool. The method enables validation for every connection that is borrowed from the pool. A value of false means no validation is performed. The default value is false.
setSQLForValidateConnection(String) – Specifies the SQL statement that is executed on a connection when it is borrowed from the pool.
|
Note: ThesetSQLForValidateConnection property is not recommended when using an Oracle JDBC driver. UCP for JDBC performs an internal ping when using an Oracle JDBC driver. The mechanism is faster than executing an SQL statement and is overridden if this property is set. Instead, set the setValidateConnectionOnBorrow property to true and do not include the setSQLForValidateConnection property. |
The following example demonstrates validating a connection when borrowing the connection from the pool. The example uses Connector/J JDBC driver from MySQL:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("com.mysql.jdbc.jdbc2.optional. MysqlDataSource");
pds.setURL("jdbc:mysql://host:3306/mysql");
pds.setUser("<user>");
pds.setPassword("<password>");
pds.setValidateConnectionOnBorrow(true);
pds.setSQLForValidateConnection("select * from mysql.user");
Connection conn = pds.getConnection();
The oracle.ucp.jdbc.ValidConnection interface provides two methods: isValid and setInvalid. The isValid method returns whether or not a connection is usable and the setInvalid method is used to indicate that a connection should be removed from the pool instance. See "Removing Connections from UCP" for more information on using the setInvalid method.
The isValid method is used to check if a connection is still usable after an SQL exception has been thrown. This method can be used at any time to check if a borrowed connection is valid. The method is particularly useful in combination with a retry mechanism, such as the Fast Connection Failover actions that are triggered after a RAC-down event. See Chapter 8, "Using Oracle RAC Features," for more information on Fast Connection Failover.
|
Note: TheisValid method checks with the pool instance and Oracle JDBC driver to determine whether a connection is still valid. The isValid method results in a roundtrip to the database only if both the pool and the driver report that a connection is still valid. The roundtrip is used to check for database failures that are not immediately discovered by the pool or the driver. |
The isValid method is also helpful when used in conjunction with the connection timeout and connection harvesting features. These features may return a connection to the pool while a connection is still held by an application. In such cases, the isValid method returns false, allowing the application to get a new connection.
The following example demonstrates using the isValid method:
try { conn = poolDataSouorce.getConnection ...}catch (SQLException sqlexc)
{
if (conn == null || !((ValidConnection) conn).isValid())
// take the appropriate action
...
conn.close();
}
Borrowed connections that are no longer being used should be returned to the pool so that they can be available for the next connection request. The close method is used to close connections and automatically returns the connections to the pool. The close method does not physically remove the connection from the pool.
Borrowed connections that are not closed will remain borrowed; subsequent requests for a connection result in a new connection being created if no connections are available. This behavior can cause many connections to be created and can affect system performance.
The following example demonstrates closing a connection and returning it to the pool:
Connection conn = pds.getConnection(); //do some work with the connection. conn.close(); conn=null;
The setInvalid method of the ValidConnection interface indicates that a connection should be removed from the connection pool when it is closed. The method is typically used when a connection is no longer usable, such as after an exception or if the isValid method of the ValidConnection interface returns false. The method can also be used if an application deems the state on a connection to be bad. The following example demonstrates using the setInvalid method to close and remove a connection from the pool:
Connection conn = pds.getConnection(); ... ((ValidConnection) conn).setInvalid(); ... conn.close(); conn=null;
Third-party products, such as middleware platforms or frameworks, can use UCP to provide connection pooling functionality for their applications and services. UCP integration includes the same connection pool features that are available to stand-alone applications and offers the same tight integration with the Oracle Database.
Two data source classes are available as integration points with UCP: PoolDataSourceImpl for non-XA connection pools and PoolXADataSourceImpl for XA connection pools. Both classes are located in the oracle.ucp.jdbc package. These classes are implementations of the PoolDataSource and PoolXADataSource interfaces, respectively, and contain default constructors. For more information on the implementation classes refer to the Oracle Universal Connection Pool Java API Reference.
These implementations explicitly create connection pool instances and can return connections. For example:
PoolXADataSource pds = new PoolXADataSourceImpl();
pds.setConnectionFactoryClassName("oracle.jdbc.xa.client.OracleXADataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("user");
pds.setPassword("password");
XAConnection conn = pds.getXAConnection();
Third-party products can instantiate these data source implementation classes. In addition, the methods of these interfaces follow the JavaBean design pattern and can be used to set connection pool properties on the class using reflection. For example, a UCP data source that uses an Oracle JDBC connection factory and database might be defined as follows and loaded into a JNDI registry:
<data-sources>
<data-source
name="UCPDataSource"
jndi-name="jdbc/UCP_DS"
data-source-class="oracle.ucp.jdbc.PoolDataSourceImpl">
<property name="ConnectionFactoryClassName"
value="oracle.jdbc.pool.OracleDataSource"/>
<property name="URL" value="jdbc:oracle:thin:@//localhost:1521:oracle"/>
<property name="User" value"user"/>
<property name="Password" value="password"/>
<property name="ConnectionPoolName" value="MyPool"/>
<property name="MinPoolSize" value="5"/>
<property name="MaxPoolSize" value="50"/>
</data-source>
</data-sources>
When using reflection, the name attribute matches (case sensitive) the name of the setter method used to set the property. An application could then use the data source as follows:
Connection connection = null;
try {
InitialContext context = new InitialContext();
DataSource ds = (DataSource) context.lookup( "jdbc/UCP_DS" );
connection = ds.getConnection();
...
The Oracle Universal Connection Pool (UCP) for JDBC is a full-featured connection pool for managing database connections. Java applications that are database-intensive use the connection pool to improve performance and better utilize system resources.
The instructions in this guide detail how to use the UCP for JDBC API and cover a wide range of use cases. The guide does not provide detailed information about using Oracle JDBC Drivers, Oracle Database, or SQL except as required to understand UCP for JDBC.
This guide is primarily written for Application Developers and System Architects who want to learn how to use UCP for JDBC to create and manage database connections for their Java applications. Users must be familiar with Java and JDBC to use this guide. Knowledge of Oracle Database concepts (such as Oracle RAC and ONS) is required when using some UCP for JDBC features.
Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
Deaf/Hard of Hearing Access to Oracle Support Services
To reach Oracle Support Services, use a telecommunications relay service (TRS) to call Oracle Support at 1.800.223.1711. An Oracle Support Services engineer will handle technical issues and provide customer support according to the Oracle service request process. Information about TRS is available at http://www.fcc.gov/cgb/consumerfacts/trs.html, and a list of phone numbers is available at http://www.fcc.gov/cgb/dro/trsphonebk.html.
For more information about using Java with the Oracle Database, see the following documents in the Oracle Database documentation set:
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
