hibernate see slow query

Question

inside hibernate.property. is that any parameter i can set to see all the slow query that take too much time to return result? i'm using spring-hibernate applicationContext.xml

Answer 1

You can enable logging of all SQL in the hibernate by setting the hibernate.show_sql property to true (see http://www.javalobby.org/java/forums/t44119.html). However there is no way to filter to only see slow queries. However, if you are using mysql you can enable its slow query log and see the queries that are slower than a set threshold there.

See: http://adminlinux.blogspot.com/2009/07/mysql-slow-queries-log.html

I'm sure most other databases will offer a similar feature.

Answer 2

I have 2 suggestions:

• you could use Oracle's Statspack.
• you could use some kind of JDBC proxy driver logging execution time P6Spy or log4jdbc.

I used P6Spy and IronTrack SQL from IronGrid in the past and this was a decent combo (see this blog post). But note that:

• P6Spy isn't really maintained (5 years without a change).
• The measures weren't totally accurate (that's not a huge problem when chasing greedy pigs).
• IronTrack SQL doesn't seem to be available anymore (but you can still find it, for example here).

I'm personally using log4jdbc now which provides similar features (without the fancy GUI but who cares, either use Excel or the script they provide to produce a report). From its website:

Features

• Full support for JDBC 3 and JDBC 4!
• Easy to configure, in most cases all you need to do is change the driver class name to net.sf.log4jdbc.DriverSpy and prepend "jdbc:log4" to your existing jdbc url, set up your logging categories and you're ready to go!
• In the logged output, for prepared statements, the bind arguments are automatically inserted into the SQL output. This greatly Improves readability and debugging for many cases.
• SQL timing information can be generated to help identify how long SQL statements take to run, helping to identify statements that are running too slowly and this data can be post processed with an included tool to produce profiling report data for quickly identifying slow SQL in your application.
• SQL connection number information is generated to help identify connection pooling or threading problems.
• Works with any underlying JDBC driver, with JDK 1.4 and above, and SLF4J 1.x.
• Open source software, licensed under the business friendly Apache 2.0 license: http://www.apache.org/licenses/LICENSE-2.0