Some time ago we upgraded our connection pool from Apache Commons DBCP to The Tomcat JDBC Connection Pool. We did so mostly because of the improved concurrency handling but soon found out that the addition of JDBC interceptors was a useful resource in locating performance issues.
SlowQueryReport is one of the included interceptors. It logs queries which take longer to execute then the specified threshold.
While this information can be very valuable it doesn’t quite tell the complete story.
It could be that a certain unit of work (a transaction, a method call) doesn’t log a single slow query and still takes a long time to complete because it executes a lot of queries. To count the number of queries in a transaction I’ve created a CountQueryReport extension for the Tomcat JDBC Connection Pool. It is a very simple extension which will count the number of statements (per thread) executed between a call to ‘resetCount()’ and ‘numberOfStatements()’.
Configuration example (for a Spring environment):
Usage in a basic Servlet filter:
You can download the distribution zip here (which contains the binaries, sources and Javadocs).
Or have a look at the code on GitHub.