P6SPY – An Easy way to view all the SQL statements fired by your application.
There are times while developing applications using ORM tools like IBATIS, that you wish to view the sql statements finally compiled and executed on the database. In many cases, you can find many ways to log and view the sql statements.
One approach is using the ORM tool like IBATIS itself to log the sql statements for you. This can be done by setting the log mode to DEBUG level on the respective packages of IBATIS. Once changes done to log4j, all the statements along with their binding parameter values can be logged and viewed. A sample/ready-made log4j configuration file can be found here.
Another approach is just quering the system tables of the database which keeps track of all the sql statements compiled and executed. For example, in Oracle, we can use V$SQLAREA table to view the sql statement executed. Ideally I believe the sql statements are stored in the column, SQL_TEXT.
So the query to the table could be as simple as this:
SELECT SQL_TEXT FROM V$SQLAREA WHERE SQL_TEXT LIKE ‘%my_statement_portion%’
But one of the most simplest and quickest way to log and view the SQL log is through tools like P6SPY. I found this open source tool awesome for its shear simplicity and ease of use.
All that I want to do is download the P6SPY archive from the official site. Copy the p6spy.jar and spy.properties to the appropriate classpath folder. Ofcourse there is some minor changes required in configuration but it is so simple to do.
For P6Spy to start logging the SQL statements fired through your application, you have allow P6Spy driver to handle the sql statements which I believe ideally does nothing but log the sql statements and delegate the sql statements execution to the respective database drivers.
To be more clear, say if I have configured my datasource properties to use “oracle.jdbc.driver.OracleDriver” to communicate to Oracle, all that I need to do is change this property’s value to use “com.p6spy.engine.spy.P6SpyDriver”. And in the spy.properties (which I moved to classpath folder) set the value of “realDriver” to “oracle.jdbc.driver.OracleDriver”. That is all about it! From now on when you run your application, all the sql statements with its values would be logged in a file named ‘Spy.log’. Isn’t it really cool! 🙂
I could use the P6SPY logging from within my IDE while executing and debugging my testcases and also could use it with the Application Server to view the SQL statements fired by my web application.
Check out the following document to see more on using P6Spy with various application servers.
The latest version of P6Spy can be downloaded from the following sourceforge link:
There are many more things that you can use P6SPY in conjunction with some other tools like SQL Tuning etc., but I would stop it here for letting you further explore on the same. The best place to start would be the P6SPY’s home page itself.