SQL Statement Performance Measurement

Measuring application SQL Statement performance

The Admin4J JDBC driver acts as a proxy for the database specific driver that you're using. It passes through all of your statements and operations, but it records SQL statement performance time along the way.

Admin4J provides a SQL Performance display servlet where you can view the top 50 (the number is configurable) most resource intensive SQL statements. Needless to say, this performance display servlet only works properly if you've installed the Admin4J JDBC driver.

Connection activity is also viewable via JMX or via the JmxServlet.

Screen Shots

Admin4J SQL Performance Statistics shot

Installing SQL Statement Monitoring

Depending on the runtime version of Java you're using, the specific driver class to use is the following (all package net.admin4j.jdbc.driver):

JDK Version Admin4J JDBC Driver Class
Java V1.5x net.admin4j.jdbc.driver.Admin4jJdbcDriverJdk5
Java V1.6x net.admin4j.jdbc.driver.Admin4jJdbcDriverJdk5
Java V1.7x net.admin4j.jdbc.driver.Admin4jJdbcDriverJdk7
Java V1.8x net.admin4j.jdbc.driver.Admin4jJdbcDriverJdk7 (Requires Admin4J 1.0.3-rc1 or above)

The connection URL for this driver is in the following format:

jdbcx:admin4j:[admin4J properties]::[underlying driver URL]

For example:

jdbcx:admin4j:driver=org.hsqldb.jdbcDriver,poolName=mainPoolDB::jdbc:hsqldb:mem:Admin4JTestDb

Supported Admin4J driver properties are the following:

Parameter Required Default Description
driver Y n/a Class name of the underlying JDBC driver
poolName N none Name of the connection pool to which this driver is being assigned. This is displayed with performance metrics.
stackTrace N (true/false) true Specifies if execution stack traces are tracked so you know where in your code a SQL statement is being executed. Tests show setting to false will save approximately 1 millisecond per seven SQL Statement executions.

This driver honors the following configuration settings (see net.admin4j.config.PropertyConfigurator):

Parameter Required Default Description
sql.nbr.retained.sql.statements N 50 Number of top resource intensive SQL statements retained.
sql.retention.time.in.millis N 6 Hrs Maximum time SQL statements are retained since last executed.

Installing SQL Metrics Display

Note
If you followed the installation procedure detailed in the Getting Started section, there is no need to install the SQL Metrics Display separately. Only follow this procedure if you're not using the Admin4J Home Page Servlet.

Add the following servlet and servlet mapping to your applications web.xml:

		<servlet>
			<servlet-name>SQL Display Servlet</servlet-name>
			<servlet-class>net.admin4j.ui.servlets.SqlDisplayServlet</servlet-class>
			<load-on-startup>1</load-on-startup>
		</servlet>

		<servlet-mapping>
			<servlet-name>SQL Display Servlet</servlet-name>
			<url-pattern>/admin4j/sql</url-pattern>
		</servlet-mapping>
Note
You might want to define a security constraint on the mapping to this servlet if you only want to provide display access to administrators and support personnel.