Is there any Hibernate property I could set to see all the slow queries? I’, interested in queries that take too much time to return the result set.
I’m using Spring with Hibernate, configured via an applicationContext.xml Spring configuration file.
Advertisement
Answer
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