Hibernate second level caching to reduce query executions

There are situations in web applications where some queries could be executed in almost every http request. If those queries return static kind of data, a good approach is caching to reduce database execution calls. Reduced execution means reduced CPU usage. So, related to database performance in anyway.

Recently, we have discovered that one of the static kind of query is quite frequently parsed and executed in database. The parsing had been increased as we experimentally disabled "Statement Caching" in WebLogic connection pool.

AWR output: 11:30 AM - 12:00 PM; Parse Calls in 30 min

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
68,185	68,187	20.38	dxyfqwzsn9727		select title0_.id as id27_0_, ...

The above query returns kind of static information, less frequently changed. The query has been generated from a Hibernate call on that db object. So, we could easily introduce a second level caching for that - it is something that caches individual values, not the instances of an object.

Here is a good article on Hibernate caching -
http://www.javalobby.org/java/forums/t48846.html

What we need to do to enable second level caching?

What I just did -

1. Added 'Cache' annotation to the db class (Title.java). We are using Hibernate 3, so there is no hbm.xml now.
2. Now for the corresponding db class, I added caching configurations in ehcache.xml. Ehcache is a widely used java distributed cache for general purpose caching for Java EE applications.


#Title.java (the db class)

@Entity
@Table(name = "title")
@SequenceGenerator(name = "Seq_Title",sequenceName = "title_seq")
@Cache(usage = org.hibernate.annotations.CacheConcurrencyStrategy.READ_WRITE)
public class Title extends Persistent {

private int id;

.................
.................

}

#ehcache.xml (ehcache configuration file)

< ehcache >
.................
.................


< name="net.service.common.db.Title"
maxelementsinmemory="1000"
eternal="false"
overflowtodisk="false"
timetoidleseconds="900"
timetoliveseconds="900" / >


< / ehcache >


To test if it works or not, I took one test database box and flushed shared pool to remove old stats. Then connecting to the database, ran the application which requires large invocation of the query.

It is working according to my expectation, just loaded new titles (by invoking the query) after each 15 min!

col sql_text format a40;
set linesize 200;

select sq.SQL_TEXT, st.PARSE_CALLS, st.EXECUTIONS,
round( 100*(1-( st.PARSE_CALLS / st.EXECUTIONS )),2) execute_to_parse
from v$sqlstats st, v$sql sq
where st.sql_id = sq.sql_id
and sq.PARSING_SCHEMA_NAME = 'PROD'
and sq.sql_text like 'select title0_.id%'
and st.EXECUTIONS <> 0
order by execute_to_parse;

SQL_TEXT     PARSE_CALLS EXECUTIONS EXECUTE_TO_PARSE
---------------------------------------- ----------- ---------- -------
select title0_.id as id27_0_, title0_.cr     3   140   97.86
eated as created27_0_, title0_.updated a
s updated27_0_, title0_.version as versi
on27_0_, title0_.description as descript
5_27_0_, title0_.prov_id as prov6_27_0_,
title0_.title as title27_0_ from title
title0_ where title0_.id=:1


3 parse calls, 140 executions in 20 min!! I was requesting to load many titles, but it was actually serving from the cache. So, there will less executions for the query from now on :-). Also less parse calls since we are using "Prepared Statement Caching" in WebLogic connection pool.