SQL Profiler

Introduction
This is a quickly hacked tool to do statistics on SELECT queries in order to know where it is most efficient to create indexes.

This small tool, released under an Apache-based license connects to the P6Spy JDBC logger and displays in real time the queries going to the database. It uses an integrated SQL parser to build statistics on the most accessed tables and columns and can generate SQL index creation files. Other information is also gathered and displayed, such as the request time for a single request, for a class of request, and for all the requests. Sorting may be done on these views to detect database problems efficiently.

This tool can be very useful when you have a big volume of queries that you need to analyze not one by one (meaning that the specific time isn't that much of interest), but rather when you want to know what "group" of queries is taking a lot of time, such as queries on the same tables and columns but with different query values. The integrated SQL parser (built with ANTLR) is used to analyze the incoming SELECT queries.

The Swing GUI was based on Apache's Log4J Chainsaw, but all the bugs are mine. Also contributors are welcome to test, make new suggestions, give their opinion and submit patches.

News

2003-11-22 : Version 0.3 released !

This release has been achieved much faster than the previous one, and this is mostly due to the great work by Jean-Philippe Valentin, that has contributed the following new features : - parsing of UPDATE, INSERT and DELETE statement. These are not used to create indexes (yet), but are using to provided statistics on these statements and the tables that are used. - CSV output of the profile results, so that they may be re-used outside of the SQL Profiler. Thanks Jean-Philippe ! And for the others potentiel contributors out there I think the project is still young enough to get into quite easily, so please, don't be scared to mess with the code and share your hacks.

2003-10-15 : Version 0.2 released !

After a much longer time than I had anticipated, the new release is finally here. Here is a quick overview of the new features : New features in release 0.2 : - optimized request processing - millisecond precise time display - order of columns in request is taken into account to build more efficient indexes - new charting view, that displays the amount of requests per second as well as an experimental traffic indicator to/from the database - new detail panel for the profile view, that allows to view the full details of an SQL query without having to switch to log view. - included p6spy.jar, no need for seperate download. Go download it now and tell me what you think about it !

2003-08-31 : Working on version 0.2

I have more or less finalized the new features for version 0.2. I need to do some more tests and finalize some of the docs, but you can already check out the new screenshots that are on this page, and if you want to help in the testing, you are more than welcome to pull the code from the CVS and send me your feedback !

2003-07-22 : Article on SQL Profiler

An article reviewing the SQL Profiler may be found here.

2003-05-14 : Version 0.1 released

The first main release of this tool has been made under an Apache-based license with full source code, hosted on Sourceforge.

Requirements
In order to use this tool most efficiently you must configure your JDBC application to use the P6Spy pass-thru driver. In the distribution we include a sample spy.properties configuration file that contains the default settings for connecting to the SQL Profiler socket.

Binary packages
This software is available in a pre-compiled binary form. Check out the downloads link on the right. If you are downloading the binaries you can skip the compiling steps described here below.

Usage and compilation instructions
You can find more details about the usage of this tool, as well as details about how to compiling and even integrate with JBuilder in the README.txt file available in the CVS.

License
This software is licensed under the terms of the Jahia Open Software License, Version 1.1, with parts licensed under the The Apache Software License, Version 1.1.

Credits
The original GUI code for this application is loosely based on the Apache Foundation's Jakarta Log4J Chainsaw GUI. So all my thanks go to the original contributors of this code, notably :

Oliver Burn
Ceki Gülcü
Mark Womack

Contributors
Serge Huber
Jean-Philippe Valentin

ENJOY !