hibernate show query execution time

Question

I was looking for setup hibernate or log4j to show me, how long are executed queries in hibernate, which are invoked by hql.

Till now i didn't found anything interesting.

Maybe some of you might help me with this issue ?

score 36 · Answer 1 · edited Sep 02 '21 at 07:02

36

There is a simple way (built in hibernate) to achieve it. It was fixed by HHH-3659 in 3.5.4 and 3.6.0.Beta1.

To go for it, use:

logging on the package: org.hibernate.stat at least on DEBUG and
set hibernate property: hibernate.generate_statistics=true

Afterwards all the SQLs will be logged including the row count fetched and the time the query execution took.

Moreover if interested, it provides statistics on queries. To have them exposed via JMX and see them using JConsole, you can use Hibernate JConsole plugin, available on: http://hibernate-jcons.sourceforge.net/

edited Sep 02 '21 at 07:02

Sander

390
1
4
13

answered Aug 13 '13 at 08:33

Peter Butkovic

11,143
10
57
81

7

Hi Peter, It worked for me but I had to change the log4j's level to trace on org.hibernate.stat to get that working. Thanks for your solution – Paulo Miguel Almeida Sep 01 '13 at 04:26
1

That worked for me! In logback.xml: In hibernate.properties: hibernate.generate_statistics=true – formica Nov 09 '15 at 22:26
1

It does work for JDBC queries sent by org.hibernate.loader.Loader, but not those sent by org.hibernate.jdbc.AbstractBatcher (lazy loading). The easiest solution was to configure logging on DEBUG for "org.hibernate.*" and see which query is executed at which log timestamp. – metatechbe Oct 18 '16 at 12:52
4

I tried this, one drawback is that it reports statistics at the end of the session. If you do many things in one session you can't determine the performance of each. For example "1436424992 nanoseconds spent executing 14 JDBC statements;" – Uncle Long Hair Mar 31 '17 at 14:37
It is showing HQL and not SQL. Is there a way to change that? – Ruslan Jul 20 '17 at 18:41
1

`org.hibernate.stat` has to be of `DEBUG` level. – kolobok Dec 24 '19 at 11:48

score 5 · Answer 2 · answered Apr 04 '22 at 13:35

5

With Spring Boot you can use these two rows in your application.properties:

logging.level.org.hibernate.stat=DEBUG
spring.jpa.properties.hibernate.generate_statistics=true

answered Apr 04 '22 at 13:35

Datz

3,156
3
22
50

score 4 · Answer 3 · answered Sep 29 '12 at 21:10

To log queries with its execution time to log file you may try Craftsman Spy JDBC Driver which do exactly same things and has simple integration with Spring.

Another way is to using Spring Insight which shows JDBC/Hibernate calls and many more details.

hibernate show query execution time

4 Answers4

Linked