10 Diagnosing a Connection Pool

The following sections are included in this chapter:

Pool Statistics

Universal Connection Pool (UCP) for JDBC provides a set of run-time 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.

Dynamic Monitoring Service Metrics

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 class path 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.

Viewing Oracle RAC Statistics

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.

Fast Connection Failover 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.

Run-Time Connection Load Balance Statistics

The run-time connection load balance statistics are used to determine if a connection pool is effectively utilizing the run-time connection load balancing feature of Oracle RAC. 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 Oracle RAC Load Balancing Advisory or connection pool is not configured properly.

Connection Affinity Statistics

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+".");

Setting Up Logging in UCP

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.

Using a Logging Properties File

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.

Using UCP for JDBC and JDK API

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);

Supported Log Levels

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

Exceptions and Error Codes

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.