Fusion Middleware Documentation
Advanced Search


Administering JDBC Data Sources for Oracle WebLogic Server
Close Window

Table of Contents

Show All | Collapse

15 Monitoring WebLogic JDBC Resources

This chapter provides information on 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.

The following sections include details about monitoring JDBC objects:

Viewing Run-Time Statistics

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

Data Source Statistics

You can view run-time statistics for a data source via the Administration Console 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 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 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:

Collecting Profile Information

If the statistics that you are seeing indicate that there is a problem in your WebLogic Server 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. The fields contain different information for different profile types, as described in the sections that follow.

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 following sections include details about profile information:

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, as described in the following sections of this document:

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

    Note:

    Inactive Connection Timeout must be set 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 expires.

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

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 Administration Console. See:

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

Callbacks for Monitoring Driver-Level Statistics (Deprecated)

Note:

This feature is deprecated in WebLogic Server 10.3.6.0 and may be removed in a future release.

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 have narrowed the problem down to a specific application, you can activate WebLogic Server's debugging features to track down the specific problem within the application.

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

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

Use the WebLogic Server Administration Console to set the debugging values:

  1. If you have not already done so, in the Change Center of the Administration Console, click Lock & Edit (see "Using the Change Center" in Understanding Oracle WebLogic Server).

  2. In the left pane of the console, expand Environment and select Servers.

  3. On the Summary of Servers page, click the server on which you want to enable or disable debugging to open the settings page for that server.

  4. Click Debug.

  5. Expand default.

  6. Select the check box for the debug scopes or attributes you want to modify.

  7. Select Enable to enable (or Disable to disable) the debug scopes or attributes you have checked.

  8. To activate these changes, in the Change Center of the Administration Console, click Activate Changes.

  9. Not all changes take effect immediately—some require a restart (see "Using the Change Center" in Understanding Oracle WebLogic Server).

    This method is dynamic and can be used to enable debugging while the server is running.

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

Setting Debugging for UCP/ONS

The following sections provide information on how to set debugging for UCP/ONS.

Debugging UCP

Set UCP debugging directly using:

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

Debugging ONS

To enable debugging for ONS, use:

oracle.ons.debug=true

To print output, make the following call:

oracle.ons.ONS public static void setLogStream(PrintStream ps, PrintStream ps2);

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.