9 Diagnosing a Connection Pool

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

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

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