Using JDBC Profiling MBeans

The WebLogic Server management system uses Java Management Extensions (JMX) and Managed Beans (MBeans) to configure servers. The Using WebLogic JMX Services guide provides detailed information and code samples for working with WebLogic Server MBeans.

BEA provides several JDBC MBeans that you can use to store and analyze metrics for SQL statements, prepared statements, and JDBC connection leaks. The following sections describe how to enable and use JDBC profiling. For additional information, refer to the Javadoc for the following WebLogic Server MBeans and related classes:

• JDBCConnectionPoolMBean
• JDBCConnectionPoolRuntimeMBean
• JDBCStatementProfile
• JDBCConnectionLeakProfile

Enabling JDBC Profiling

Before you can analyze SQL statements or connection leak profiles, you must enable profiling for the connection pool you want to observe. When profiling is enabled, the connection pool stores metrics in an external repository for later analysis.

Applications enable and disable JDBC profiling options using the JDBCConnectionPoolMBean. In addition to providing get/set methods for standard connection pool properties, JDBCConnectionPoolMBean provides the following methods for enabling and disabling profiling:

• setConnLeakProfilingEnabled() enables or disables profiling for JDBC connection leaks. Connection leaks represent connections that were checked out of the connection pool but never returned with a close() method. It is important to analyze the connection leak profiles, as leaked connections cannot be used to fulfill later connection requests.
• setSqlStmtProfilingEnabled() enables or disables profiling for SQL statements. When this type of profiling is enabled, the connection pool stores both SQL statement text as well as the statement execution time and other metrics. You can analyze the SQL statement profile to determine which queries consume the most time in your applications.
• setSqlStmtParamLoggingEnabled() enables or disables profiling for the bind parameters of prepared and callable statements. Because statement parameters can be very large, you can optionally use setSqlStmtMaxParamLength() to limit the size of parameters that are stored in the profile.

For information on obtaining MBeans in WebLogic Server, see Accessing WebLogic Server MBeans. The following excerpt shows an application that obtains the JDBCConnectionPoolMBean and activates all profiling options. This example stores a maximum of 20 characters for each statement parameter:

// Obtain MBeanHome for the administration server.

...

JDBCConnectionPoolMBean mbean =
     (JDBCConnectionPoolMBean)home.getConfigurationMBean(poolName,
      "JDBCConnectionPoolConfig");

mbean.setConnLeakProfilingEnabled(true);

mbean.setSqlStmtParamLoggingEnabled(true);

mbean.setSqlStmtMaxParamLength(maxLen);

...

Accessing JDBC Profiles

Once you have enabled the desired profiling option(s), you can analyze the stored metrics using the JDBCStatementProfile and JDBCConnectionLeakProfile classes. Both of these profile classes can be easily obtained using the JDBCConnectionPoolRuntimeMBean.

JDBCStatementProfile stores the SQL statements and associated metrics (and optionally, bind parameters) for the connection pool. JDBCConnectionLeakProfile stores stack traces for leaked connections.

Obtaining all profiles at once may consume considerable resources. For this reason, applications should generally retrieve only a subset of profiles at a given time. You can accomplish this by first determining the total number of profiles in storage, then retrieving profiles in smaller subsets.

The following example shows a simple way to divide the number of profiles into smaller fractions.

// Obtain MBeanHome for the server that hosts the connection pool.

. . .

// Get the JDBCRuntimeMbean for the "testPool" connection pool.

String poolName = "testPool";

JDBCConnectionPoolRuntimeMBean mbean =
     (JDBCConnectionPoolRuntimeMBean)home.getRuntimeMBean
          (poolName,"JDBCConnectionPoolRuntime");

JDBCConnectionLeakProfile[] profiles = null; 

// Get the total number of available prepared statement cache profiles
int profileCount = mbean.getConnectionLeakProfileCount();

// Request profilesPerStep number of profiles
int profilesPerStep = 10;

// Begin with profile number profileIndex
int profileIndex = 0;
boolean done = (profileCount > 0);
while (!done) {

     // Get profiles
     profiles = mbean.getConnectionLeakProfiles(profileIndex,
          profilesPerStep);

     // Go through retrieved profiles
          for (int index = 0; index < profiles.length; index++) {

          // Get pool name
          String poolName = profiles[index].getPoolName();

          // Get stack trace
          String stackTrace = profiles[index].getStackTrace();
          }

     profileIndex = profileIndex + profilesPerStep - 1;

     // Finish if number of retrieved profiles is
     // less then requested
     done = (profiles.lengh < profilesPerStep);
}