|Oracle® Universal Connection Pool for JDBC Developer's Guide
11g Release 2 (11.2)
Part Number E12265-02
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:
These statistics apply only to the current running connection pool instance.
These statistics are collected across multiple pool start/stop cycles.
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 +".");
oracle.ucp.jdbc.PoolDataSource.getStatistics method can also be called by itself to return all connection pool statistics as a
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.
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.
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
Sting fcfInfo = ((OracleJDBCConnectionPoolStatistics)pds.getStatistics()). getFCFProcessingInfo(); System.out.println("The FCF information: "+fcfInfo+".");
Following is a sample output string from the
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
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
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 is
null. This ensures that a parent logger's log level is used by default.
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
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:
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.