Timing Oracle SQL queries with Glassbox

Glassbox, the slick AOP tool for automatically diagnosing your Java webapp's performance bottlenecks, first caught my eye nearly a year ago. In consulting activities, where off-the-shelf products and custom solution code mix, it can be very difficult to determine whose code is at fault when web pages load slowly.

Glassbox comes to the rescue by tracking each web request through every layer of the J2EE stack. It identifies slow requests and groups them by URL, then identifies the controllers, methods, database queries, network operations, etc. that are causing the operation to be slow. It comes packaged in a war file: just drop it into the app server, hit the webapp root from a browser, and let it autoconfigure itself.

The hitch I ran into was that Glassbox had trouble instrumenting our Oracle JDBC driver, so it couldn't identify the precise SQL queries that were causing slowness. I went to the forums initially, then forgot about it for a while when an answer wasn't readily available.

I became interested again over the past couple days, so I contacted Ron Bodkin directly: he suggested the AspectJ load-time weaving is probably at issue. He gave me some quick pointers on how to use the ajc tool to create an instrumented version of the Oracle JDBC driver so that it wouldn't need to be instrumented on the fly at load-time. I gave it a shot and it worked!

In testing, I purposely crafted a slow query by using a ridiculous number of pointless joins, to verify whether Glassbox would call me out on it.

The cookbook

First, grab Glassbox 2.0 and AspectJ 1.5.3. (To do offline weaving you'll need aspectjtools.jar, which doesn't ship with Glassbox).

Create a project directory and drop in the jar file you want to weave. Then create a lib/ subdirectory. Add aspectjtools.jar from the AspectJ download, and then explode glassbox.war into it. Find ./lib/glassbox.war/install/glassboxMonitor.jar and extract the file META-INF/Xlint.properties from it; place it in the root directory of the project.

Finally, run the following ant script:

<project name="glassbox-instrument-oracle" default="compile"> <taskdef resource="org/aspectj/tools/ant/taskdefs/aspectjTaskdefs.properties"> <classpath> <pathelement location="./lib/aspectjtools.jar" /> </classpath> </taskdef> <target name="compile"> <iajc inpath="./ojdbc14.jar" outjar="./odjbc14-glassbox-woven.jar" aspectpath="./lib/glassbox.war/install/glassboxMonitor.jar" xlintfile="./Xlint.properties" classpath="./lib/glassbox.war/WEB-INF/lib/aspectjrt.jar" /> </target> </project>

Note: Adjust your path to AspectJ as needed.