Reply

Hibernate: Use Hibernate Statistics When Optimizing Queries

At 11:49 PM on Jul 24, 2005, R.J. Lorimer wrote:

Some of the recent spotlight features on Javalobby have been full of tips and tricks for working with Hibernate:

Hibernate Querying 101 : tips and tricks
Hibernate Querying 102 : Criteria API

I highly recommend reading them. The first post is directly about tips and tricks when working with Hibernate; and knowing what to do to optimize your queries. The author recommends that unit testing and metrics using System.currentTimeMillis() can help you get the information you need to start the optimization process right.

Hibernate 3 also ships with a complete statistics and metrics API that allows you to figure out *everything* that is happening under the covers. All you have to do is a.) enable staticstics for the session factory and b.) retrieve the statistics and use them. Getting them an enabling them is easy:

SessionFactory sessionFactory = getSessionFactoryForApplication();
Statistics stats = sessionFactory.getStatistics();
stats.setStatisticsEnabled(true);

The tricky part (or at least the part that requires the most attention) is figuring out what statistics are available, and what they really mean. There are a billion methods available on the top level, but here is a glimpse (note, all of these values are based off of when statistics were enabled):

// Number of connection requests. Note that this number represents 
// the number of times Hibernate asked for a connection, and 
// NOT the number of connections (which is determined by your 
// pooling mechanism).
stats.getConnectCount();
// Number of flushes done on the session (either by client code or 
// by hibernate).
stats.getFlushCount();
// The number of completed transactions (failed and successful).
stats.getTransactionCount();
// The number of transactions completed without failure
stats.getSuccessfulTransactionCount();
// The number of sessions your code has opened.
stats.getSessionOpenCount();
// The number of sessions your code has closed.
stats.getSessionCloseCount();
// All of the queries that have executed.
stats.getQueries();
// Total number of queries executed.
stats.getQueryExecutionCount();
// Time of the slowest query executed.
stats.getQueryExecutionMaxTime();

There are also a lot of values related to the retrieval of your objects and collections of objects (directly or via association):

// the number of collections fetched from the DB.
stats.getCollectionFetchCount();
// The number of collections loaded from the DB.
stats.getCollectionLoadCount();
// The number of collections that were rebuilt
stats.getCollectionRecreateCount();
// The number of collections that were 'deleted' batch.
stats.getCollectionRemoveCount();
// The number of collections that were updated batch.
stats.getCollectionUpdateCount();
 
// The number of your objects deleted.
stats.getEntityDeleteCount();
// The number of your objects fetched.
stats.getEntityFetchCount();
// The number of your objects actually loaded (fully populated).
stats.getEntityLoadCount();
// The number of your objects inserted.
stats.getEntityInsertCount();
// The number of your object updated.
stats.getEntityUpdateCount();

In addition to all of this, there is information about cache performance (stolen from Hibernate documentation):

double queryCacheHitCount  = stats.getQueryCacheHitCount();
double queryCacheMissCount = stats.getQueryCacheMissCount();
double queryCacheHitRatio =
  queryCacheHitCount / (queryCacheHitCount + queryCacheMissCount);

That is a whole bunch of statistics on object loading - these statistics are a good way to get your head wrapped around the total throughput of your application, but for most optimization attempts, they are a bit high level.

If that weren't enough, you can also get statistics for a specific query:

QueryStatistics queryStats = stats.getQueryStatistics("from Sale sale");
// total hits on cache by this query.
queryStats.getCacheHitCount();
// total misses on cache by this query.
queryStats.getCacheMissCount();
// total number of objects put into cache by this query execution
queryStats.getCachePutCount();
// Number of times this query has been invoked
queryStats.getExecutionCount();
// average time for invoking this query.
queryStats.getExecutionAvgTime();
// maximum time incurred by query execution
queryStats.getExecutionMaxTime();
// minimum time incurred by query execution
queryStats.getExecutionMinTime();
// Number of rows returned over all invocations of this query
queryStats.getExecutionRowCount();

Just like a made-for-tv knife salesman - but wait, there's more! You can also get the statistics for a particular entity:

EntityStatistics entityStats = stats.getEntityStatistics("com.javalobby.tnt.hibernate.Sale"); // or Sale.class.getName();
// exactly the same as the global values, but for a single entity class.
entityStats.getFetchCount();
entityStats.getLoadCount();
entityStats.getInsertCount();
entityStats.getUpdateCount();
entityStats.getDeleteCount();

But wait, there's more! You can see all of the statistics for any second level cache by region:

SecondLevelCacheStatistics cacheStats = stats.getSecondLevelCacheStatistics("com.javalobby.tnt.hibernate.sale.cache");
cacheStats.getElementCountInMemory();
cacheStats.getElementCountOnDisk();
cacheStats.getEntries();
cacheStats.getHitCount();
cacheStats.getMissCount();
cacheStats.getPutCount();
cacheStats.getSizeInMemory();

There is even more. You can get statistics for a collection associated with a particular type (say, for instance, the coupons used for a sale):

CollectionStatistics collectionStats = stats.getCollectionStatistics("com.javalobby.tnt.hibernate.Sale.coupons");
collectionStats.getFetchCount();
collectionStats.getLoadCount();
collectionStats.getRecreateCount();
collectionStats.getRemoveCount();
collectionStats.getUpdateCount();

So go out into this brave new world, and improve your hibernate apps until you're blue in the face!

Until next time,

R.J. Lorimer
Contributing Editor - rj -at- javalobby.org
Author              - http://www.coffee-bytes.com
Software Consultant - http://www.crosslogic.com