When response time of a page is slow, we might be interested in viewing database queries executed on that page. Enabling SQL Logging shows all queries but we are interested in queries for that particular request only.
We can get this information by just simply adding the following filter to our application(Filters in Grails)
	        logHibernateStats(controller: '*', action: '*') {
		        before = {
				Statistics stats = sessionFactory.statistics;
				if(!stats.statisticsEnabled) {stats.setStatisticsEnabled(true)}
                       }

			afterView = {
				Statistics stats = sessionFactory.getStatistics()
				double queryCacheHitCount  = stats.getQueryCacheHitCount();
				double queryCacheMissCount = stats.getQueryCacheMissCount();
				double queryCacheHitRatio = (queryCacheHitCount / ((queryCacheHitCount + queryCacheMissCount) ?: 1))
				log.info """
######################## Hibernate Stats ##############################################
Transaction Count:${stats.transactionCount}
Flush Count:${stats.flushCount}
Total Collections Fetched:${stats.collectionFetchCount}
Total Collections Loaded:${stats.collectionLoadCount}
Total Entities Fetched:${stats.entityFetchCount}
Total Entities Loaded:${stats.entityFetchCount}
Total Queries:${stats.queryExecutionCount}
queryCacheHitCount:${queryCacheHitCount}
queryCacheMissCount:${queryCacheMissCount}
queryCacheHitRatio:${queryCacheHitRatio}
######################## Hibernate Stats ##############################################
"""
				stats.clear()
			}

        }

There are a bunch of other stats that we can get to do more analysis  If you see a large amount of queries, you can simply turn sql logging on for the particular action in this way

Gotchas:

  1. Since we are getting the stats from sessionFactory, they just do not pertain to any particular request. So this will not work in a multiuser enviornment(prod/qa)
  2. Also these stats may not be correct if you have any background jobs running simultaneously

This is basically one of the things that we do when we want to ‘tune’ the performance of an application.

PS: Performance tuning comprises of many more things. Peter ledbrook gave an excellent session on it in last year’s SpringOne2GX. You can find a summary here

Regards
~~Himanshu Seth~~

http://www.IntelliGrape.com

Himanshu Seth

Groovy, Grails Practitioner and Agile Enthusiast

Website: http://www.IntelliGrape.com

laukendra@intelligrape.comUday Pratap SinghRajan Shergill

You May Also Like

Subscribe to our Blog

Get latest articles straight to your inbox. Enter your email address below: