15 Monitoring WebLogic JDBC Resources

For monitoring WebLogic JDBC resources you need to understand how to create, collect, analyze, archive, and access diagnostic data generated by a running server and the applications deployed within its containers. This data provides insight into the run-time performance of servers and applications and enables you to isolate and diagnose faults when they occur. WebLogic JDBC takes advantage of this service to provide enhanced run-time statistics, profile information over a period of time, logging, and debugging to help you keep your WebLogic domain running smoothly.

You can use the run-time statistics to monitor the data sources in your WebLogic domain to see if there is a problem. If there is a problem, you can use profiling to determine which application is the source of the problem. Once you've narrowed it down to the application, you can then use JDBC debugging features to find the problem within the application.

• Viewing Run-Time Statistics
  Viewing run-time statistics allows you to monitor the data sources in your WebLogic domain.
• Profile Logging
  WebLogic Server uses a data source profile log to store events.
• Collecting Profile Information
  If the statistics indicate a problem in your WebLogic domain, you can configure any data source to collect profile information to help you pinpoint the source of the problem. The collected profile information is stored in records in the profile log.
• Debugging JDBC Data Sources
  Once you narrow the problem down to a specific application, you can activate the WebLogic Server debugging features to isolate the problem with the application.

Viewing Run-Time Statistics

Viewing run-time statistics allows you to monitor the data sources in your WebLogic domain.

• Data Source Statistics
  
• Prepared Statement Cache Statistics
  

Data Source Statistics

You can view run-time statistics for a data source using the WebLogic Server Administration Console (see JDBC Data Source:Monitoring:Statistics in Oracle WebLogic Server Administration Console Online Help ) or through the JBCDataSourceRuntimeMBean. The JDBCDataSourceRuntimeMBean provides methods for getting the current state of the data source and for getting statistics about the data source, such as the average number of active connections, the current number of active connections, the highest number of active connections, and so forth. For more information, see JDBCDataSourceRuntimeMBean in the MBean Reference for Oracle WebLogic Server.

Prepared Statement Cache Statistics

You can view run-time statistics for a prepared statement cache via the WebLogic Server Administration Console or through the JBCDataSourceRuntimeMBean. For more information, see JDBCDataSourceRuntimeMBean in the MBean Reference for Oracle WebLogic Server.

Profile Logging

WebLogic Server uses a data source profile log to store events.

The profile log has the following benefits:

• Log-rotation—provides the ability to configure, rotate, and retire old data using the standard WebLogic logging implementation. See the DataSourceLogFileMBean in MBean Reference for Oracle WebLogic Server.

• Data accessibility—provides the ability to use common text editors, the WLDF Data Accessor, or the WebLogic Server Administration Console. See Accessing Diagnostic Data.

Basic characteristics of the log for data source profiling are:

• A single log file is used for all data source profile types. Each profile record has the profile type name for filtering. See Profile Types.

• A single log file is used for all data sources on the server. Each profile record has the decorated data source name for filtering (fully qualified with application@module@component, if applicable). See the DataSourceLogFileMBean in MBean Reference for Oracle WebLogic Server.

For more information on WebLogic logging services, see:

• Enable and configure Datasource Profile logs in Oracle WebLogic Server Administration Console Online Help.

• Understanding WebLogic Logging Services in Configuring Log Files and Filtering Log Messages for Oracle WebLogic Server.

Collecting Profile Information

If the statistics indicate a problem in your WebLogic domain, you can configure any data source to collect profile information to help you pinpoint the source of the problem. The collected profile information is stored in records in the profile log.

When configuring your data source for profiling, you must specify the interval at which profile data is harvested (Harvest Frequency Seconds); if the interval is set to 0, harvesting of data is disabled. See Configure diagnostic profiling for a JDBC data source in Oracle WebLogic Server Administration Console Online Help.

The fields contain different information for different profile types:

• Profile Types
  
• Accessing Diagnostic Data
  
• Callbacks for Monitoring Driver-Level Statistics
  

Profile Types

For each of the profile types in this section, the User information provides a stack trace of the thread that allocated the connection and is associated with the operation being profiled. By default, the value is not set because of the overhead in tracking this information. To obtain this information, you must also enable profiling of connection leaks in addition to profile type that you want to track. For more information about profiling connection leaks, see Connection Leak (WEBLOGIC.JDBC.CONN.LEAK).

You can choose to profile the following information about data sources and the prepared statement cache:

• Connection Usage (WEBLOGIC.JDBC.CONN.USAGE)
  
• Connection Reservation Wait (WEBLOGIC.JDBC.CONN.RESV.WAIT)
  
• Connection Reservation Failed (WEBLOGIC.JDBC.CONN.RESV.FAIL)
  
• Connection Leak (WEBLOGIC.JDBC.CONN.LEAK)
  
• Connection Last Usage (WEBLOGIC.JDBC.CONN.LAST_USAGE)
  
• Connection Multithreaded Usage (WEBLOGIC.JDBC.CONN.MT_USAGE)
  
• Statement Cache Entry (WEBLOGIC.JDBC.STMT_CACHE.ENTRY)
  
• Statements Usage (WEBLOGIC.JDBC.STMT.USAGE)
  
• Connection Unwrap (WEBLOGIC.JDBC.CONN.UNWRAP)
  
• JDBC Object Closed Usage (WEBLOGIC.JDBC.CLOSED_USAGE)
  
• Local Transaction Connection Leak (WEBLOGIC.JDBC.CONN.LOCALTX_LEAK)
  
• Example Profile Information Record Log
  

Connection Usage (WEBLOGIC.JDBC.CONN.USAGE)

Enable connection usage profiling to collect information about threads currently using connections from the pool of connections in the data source. This profile information can help determine why applications are unable to get connections from the data source.

The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - connection ID

• User - stack trace of the thread using the connection

• Timestamp - time stamp showing when the connection was given to the thread

Connection Reservation Wait (WEBLOGIC.JDBC.CONN.RESV.WAIT)

Enable connection reservation wait profiling to collect information about threads currently waiting to reserve a connection from the data source. This profile information can help determine why applications are unable to get connections from the data source or to wait for connections. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - thread ID

• User - stack trace of the thread waiting for the connection

• Timestamp - time stamp showing when the thread started waiting for a connection

Connection Reservation Failed (WEBLOGIC.JDBC.CONN.RESV.FAIL)

Enable connection reservation failure profiling to collect information about threads that attempt to reserve a connection from the data source but fail to get that connection. This profile information can help determine why applications are unable to get connections from the data source even after reserving them. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - thread ID

• User - stack trace of the thread waiting for the connection plus the exception received when the reservation request failed

• Timestamp - time stamp showing when the reservation request failed

Connection Leak (WEBLOGIC.JDBC.CONN.LEAK)

Enable connection leak profiling to collect information about threads that have reserved a connection from the data source and the connection leaked (was not properly returned to the pool of connections). This profile information can help determine which applications are not properly closing JDBC connections. Connection leak profiling must be enabled to get user stack trace information for any of the profile types.The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - connection ID

• User - stack trace of the thread waiting for the connection

• Timestamp - time stamp showing when the connection leak was detected

To specify the length of time before a reserved connection is considered leaked, do one of the following:

• Set Inactive Connection Timeout Seconds to a value greater than zero. WebLogic prints a stack trace of where a JDBC pool connection was reserved. The stack trace is printed after the Inactive Connection Timeout Seconds expires.

• Set Connection Leak Timeout Seconds to a value greater than zero. The value specifies the number of seconds that a JDBC connection needs to be held by an application before triggering a connection leak diagnostic profiling record. If set to 0, the timeout is disabled.

Connection Last Usage (WEBLOGIC.JDBC.CONN.LAST_USAGE)

Enable connection last usage profiling to collect information about the previous thread that last used the connection. This information is useful when you are debugging problems with connections infected in pending transactions that cause subsequent XA operations on the connections to fail. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - stack trace of the XA exception thrown

• User - stack trace of the thread that last used the connection

• Timestamp - timestamp showing when the exception was thrown

Connection Multithreaded Usage (WEBLOGIC.JDBC.CONN.MT_USAGE)

Enable connection multithreaded usage profiling to collect information about threads that erroneously use a connection that was previously obtained by a different thread. This information is useful when an application reports a problem that you suspect may have been caused by the simultaneous use of a connection by more than one thread. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - stack trace of the other thread that was found using the connection

• User - stack trace of the thread that reserved the connection

• Timestamp - time stamp showing when usage of the connection by multiple threads was detected

Statement Cache Entry (WEBLOGIC.JDBC.STMT_CACHE.ENTRY)

Enable statement cache entry profiling to collect information for prepared and callable statements added to the statement cache, and for the threads that originated the cached statements. This information can help you determine how the cache is being used. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - string representation of the statement

• User - stack trace of the thread using the statement

• Timestamp - time stamp showing when the statement was added to the cache

Statements Usage (WEBLOGIC.JDBC.STMT.USAGE)

Enable statements usage profiling to collect information about threads currently executing SQL statements from the statement cache. This information can help you determine how statements are being used. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - SQL statement being executed via the statement

• User - stack trace of the thread using the statement

• Timestamp - duration of statement execution

Connection Unwrap (WEBLOGIC.JDBC.CONN.UNWRAP)

Enable connection unwrap profiling to collect profile information about application components that access the underlying JDBC connection using either the getVendorObject WebLogic extension API or the JDBC 4.0 method unwrap. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - stack trace of where the object was unwrapped

• User - stack trace of the thread unwrapping the object

• Timestamp - time stamp showing when the object was unwrapped.

JDBC Object Closed Usage (WEBLOGIC.JDBC.CLOSED_USAGE)

Enable JDBC object usage profiling to collect profile information about JDBC objects (Connection, Statement, or ResultSet) that are accessed after the close() method has been invoked. This information can help you determine both the thread that initially closed the object and the thread that attempted to access the closed object. The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - stack trace of the current thread attempting to close the object

• User - stack trace of the thread that closed the object plus where the close was done

• Timestamp - time stamp showing when the object was closed

Local Transaction Connection Leak (WEBLOGIC.JDBC.CONN.LOCALTX_LEAK)

Enable JDBC local transaction connection leak profiling to collect profile information about application components that leak a local transaction (start it but don't commit or rollback the transaction). The log record will include the call stack and details about the thread releasing the connection.The record contains the following information:

• PoolName - name of the data source to which this connection belongs

• ID - stack trace of the thread that is releasing the connection

• User - stack trace of the reserving thread plus a stack trace of the thread at the time the connection was closed

• Timestamp - time stamp showing when the connection was closed

Example Profile Information Record Log

The following is an example profile information record for Statements Usage (WEBLOGIC.JDBC.STMT.USAGE) from a standard output log.

####<JDBC Data Source-0> <WEBLOGIC.JDBC.STMT.USAGE> <0> <java.lang.Exception
     at
.
.
.
 weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run( ContainerSupportProviderImpl.java:254)
     at weblogic.work.ExecuteThread.execute(ExecuteThread.java:295)
     at weblogic.work.ExecuteThread.run(ExecuteThread.java:254)
 > <select 1 from dual> 

Each component of the profile log is surrounded by brackets ("<" and ">"):

• The PoolName—JDBC Data Source-0

• The Profile Type— WEBLOGIC.JDBC.STMT.USAGE

• The Timestamp—0 (milliseconds)

• User—java.lang.Exception at . . . at weblogic.work.ExecuteThread.run(ExecuteThread.java:254

• ID—select 1 from dual

Accessing Diagnostic Data

You can use one of the following methods to access diagnostic data:

• The WebLogic Server Administration Console. SeeView and configure logs and Monitor Statistics for a JDBC data source in Oracle WebLogic Server Administration Console Online Help.

• The Data Accessor component of the WebLogic Diagnostic Framework (WLDF). See Accessing Diagnostic Data With the Data Accessor in Configuring and Using the Diagnostics Framework for Oracle WebLogic Server

• Manually review information using text editors.

• When running with DataSource profiling, the default harvesting time is 300 seconds so you may not be able to view data immediately. You may need to set the harvest time to a small value (say 5 seconds) to better visualize results. To see all connections, take a diagnostic image. To see the stack trace, enable leak profiling.

Callbacks for Monitoring Driver-Level Statistics

WebLogic Server provides callbacks for methods called on a JDBC driver. You can use these callbacks to monitor and profile JDBC driver usage, including methods being executed, any exceptions thrown, and the time spent executing driver methods.

To enable the callback feature, you specify the fully qualified path of the callback handler for the driver-interceptor element in the JDBC data source descriptor (module). Your callback handler must implement the weblogic.jdbc.extensions.DriverInterceptor interface. When you enable JDBC driver callbacks, WebLogic Server calls the preInvokeCallback(), postInvokeExceptionCallback(), and postInvokeCallback() methods of the registered callback handler before and after invoking any method inside the JDBC driver.

Any time an application calls the JDBC driver, a callback is sent to the class that implemented the driver.

Debugging JDBC Data Sources

Once you narrow the problem down to a specific application, you can activate the WebLogic Server debugging features to isolate the problem with the application.

• Enabling Debugging
  
• JDBC Debugging Scopes
  
• Set Debugging for UCP or ONS
  
• Request Dyeing
  

Enabling Debugging

You can enable debugging by setting the appropriate ServerDebug configuration attribute to "true." Optionally, you can also set the server StdoutSeverity to "Debug".

You can modify the configuration attribute in any of the following ways:

• Enable Debugging Using the Command Line
  
• Enable Debugging Using the WebLogic Server Administration Console
  
• Enable Debugging Using the WebLogic Scripting Tool
  
• Changes to the config.xml File
  

Enable Debugging Using the Command Line

Set the appropriate properties on the command line. For example,

-Dweblogic.debug.DebugJDBCSQL=true 
-Dweblogic.log.StdoutSeverity="Debug"

This method is static and can only be used at server startup.

Enable Debugging Using the WebLogic Server Administration Console

To track down problems within the application you can enable debugging using the WebLogic Server Administration Console.

To enable debugging do the following:

1. Locate the Change Center in the upper left corner of the Administration Console.
2. Click Lock & Edit to lock the editable configuration hierarchy for the domain.
3. In the left pane of the console, select Environment > Servers.
4. Enable or disable debugging on the relevant page of the Console.
5. Click Save on each page where you make a change
6. When you have finished making all the necessary changes, click Activate Changes in the Change Center.

For more information, see Using the Change Center in Understanding Oracle WebLogic Server.

Enable Debugging Using the WebLogic Scripting Tool

Use the WebLogic Scripting Tool (WLST) to set the debugging values. For example, the following command runs a program for setting debugging values called debug.py:

java weblogic.WLST debug.py

The debug.py program contains the following code:

user='user1'
password='password'
url='t3://localhost:7001'
connect(user, password, url)
edit()
cd('Servers/myserver/ServerDebug/myserver')
startEdit()
set('DebugJDBCSQL','true')
save()
activate()

Note that you can also use WLST from Java. The following example shows a Java file used to set debugging values:

import weblogic.management.scripting.utils.WLSTInterpreter;
import java.io.*;
import weblogic.jndi.Environment;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class test {
 public static void main(String args[]) {
try {
WLSTInterpreter interpreter = null;
String user="user1";
String pass="pw12ab";
String url ="t3://localhost:7001";
Environment env = new Environment();
env.setProviderUrl(url);
env.setSecurityPrincipal(user);
env.setSecurityCredentials(pass);
Context ctx = env.getInitialContext();

interpreter = new WLSTInterpreter();
interpreter.exec
("connect('"+user+"','"+pass+"','"+url+"')");
interpreter.exec("edit()");
interpreter.exec("startEdit()");
interpreter.exec
("cd('Servers/myserver/ServerDebug/myserver')");
interpreter.exec("set('DebugJDBCSQL','true')");
interpreter.exec("save()");
interpreter.exec("activate()");

} catch (Exception e) {
System.out.println("Exception "+e);
}
}
}

Using the WLST is a dynamic method and can be used to enable debugging while the server is running.

Changes to the config.xml File

Changes in debugging characteristics, through console, or WLST, or command line are persisted in the config.xml file. See Example 15-1:

Example 15-1 Example Debugging Stanza for JDBC

.
.
.
<server>
<name>myserver</name>
<server-debug>
<debug-scope>
<name>weblogic.transaction</name>
<enabled>true</enabled>
</debug-scope>
<debug-jdbcsql>true</debug-jdbcsql>
</server-debug>
</server> 
.
.
.

This sample config.xml fragment shows a transaction debug scope (set of debug attributes) and a single JDBC attribute.

JDBC Debugging Scopes

The following are registered debugging scopes for JDBC:

• DebugJDBCSQL (scope weblogic.jdbc.sql) - prints information about all JDBC methods invoked, including their arguments and return values, and thrown exceptions.

• DebugJDBCConn (scope weblogic.jdbc.connection) - trace all connection reserve and release operations in data sources as well as all application requests to get or close connections.

• DebugJDBCONS (scope weblogic.jdbc.rac) - trace low-level ONS debugging.

• DebugJDBCRAC (scope weblogic.jdbc.rac) - trace RAC debugging.

• DebugJDBCUCP (scope weblogic.jdbc.rac) - trace low-level UCP debugging.

• DebugJDBCReplay (scope weblogic.jdbc.rac) - trace Replay debugging.

• DebugJDBCRMI (scope weblogic.jdbc.rmi) - similar to JDBCSQL but at the RMI level; turning on this one and JDBCSQL will get two sets of debug messages for each operation called from a client.

• DebugJDBCInternal (scope weblogic.jdbc.internal) - low level debugging in weblogic/jdbc/common/internal related to the data source, the connection environment, and the data source manager.

• DebugJDBCDriverLogging (scope weblogic.jdbc.driverlogging) - enables JDBC driver level logging (this replaces ServerMBean JDBCLoggingEnabled and getJDBCLogFileName).

  Note:

  To get driver level tracing for Oracle, you need to use ojdbc6_g.jar instead of ojdbc6.jar. Note that for this debug scope, it can be turned on once via the command line or configuration when the server is booted but cannot be turned on or off dynamically (due to the DriverManager interface).

• DebugJTAJDBC (scope weblogic.jdbc.transaction) - trace transaction debugging.

Set Debugging for UCP or ONS

Debugging UCP

Set UCP debugging directly using:

oracle.ucp.level = FINEST;
oracle.ucp.jdbc.PoolDataSource = WARNING;

Debugging ONS

To enable debugging for ONS, you must configure java.util.logging.

-Djava.util.logging.config.file=configfile
-Doracle.ons.debug=true

In this command, configfile is the path and file name of the configuration property file property used by standard JDK logging to control the log output format and logging level. The configfile must include the following line:

oracle.ons.level=FINEST

For more information, see java.util.logging in Java Platform Standard Edition API Specification.

Request Dyeing

Another option for debugging is to trace the flow of an individual (typically "dyed") application request through the JDBC subsystem. For more information, see Configuring the Dye Vector via the DyeInjection Monitor in Configuring and Using the Diagnostics Framework for Oracle WebLogic Server.