Hibernate 3.0 Issue, Queries executed millions of times

[sankey jain]

Hello All,

I have a problem at hand which is causing a lot of concern out here.

Background : we use hibernate for all our database activities, version of hibernate is 3.0.
we have 3 schemas and each contains close to 15 - 20 tables

Now, recently we extracted the AWR report for our database instance, and we were startled by the section which told us which query had executed how many times

some queries have executed 5 million times in a span of 2 days, there is no call in the application which makes the hit so many times

So wanted some expert advice on what is going on, are we doing something wrong. AWR report is attached

[swapnil kachave]

Hi sankey jain,

To improve performance of your application , Implement level 2 cache in application.
Normally when you do normal select also database query is fired in hibernate but if you use caching in application then only one time query is fired and next time request come to your server for same, Then database is picked from cache for that no new query is fired..

[sankey jain]

swapnil kachave wrote:Hi sankey jain,

To improve performance of your application , Implement level 2 cache in application.
Normally when you do normal select also database query is fired in hibernate but if you use caching in application then only one time query is fired and next time request come to your server for same, Then database is picked from cache for that no new query is fired..

Hi Swapnil,

Thanks for the suggestion i will try this out.
i can see this statement in my hibernate configuration file, should i be removing this from the file, will that do the trick?
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

[swapnil kachave]

read this document
http://www.javabeat.net/articles/37-introduction-to-hibernate-caching-1.html
this will give the idea about how caching is work in hibernate..

[David O'Meara]

You were saying that some queries show up a lot, but that isn't a problem in itself unless there is a performance problem in the DB or the application.
Yes, it is worth plugging in a low level cache, but don't try to over-solve the issue at this point if it isn't displaying any real problems.

[Paul Sturrock]

swapnil kachave wrote:Hi sankey jain,

To improve performance of your application , Implement level 2 cache in application.
Normally when you do normal select also database query is fired in hibernate but if you use caching in application then only one time query is fired and next time request come to your server for same, Then database is picked from cache for that no new query is fired..

Be aware that the second level cache is one of the last performance changes you might normally make to an application. Its usually the very big hammer approach. If you do have a performance problem (do you?) there are much smaller configuration changes you can make - such as tuning the fetch startegies you use.

[sankey jain]

Paul Sturrock wrote:

swapnil kachave wrote:Hi sankey jain,

To improve performance of your application , Implement level 2 cache in application.
Normally when you do normal select also database query is fired in hibernate but if you use caching in application then only one time query is fired and next time request come to your server for same, Then database is picked from cache for that no new query is fired..

Be aware that the second level cache is one of the last performance changes you might normally make to an application. Its usually the very big hammer approach. If you do have a performance problem (do you?) there are much smaller configuration changes you can make - such as tuning the fetch startegies you use.

Hello Paul,

Well, We are not exactly having a performance issue, but the AWR report is recording too many SQL queries being hit in a very short span of time ( 2 days the database has been hit with 4 million calls) just trying to figure out the reason behind it

[sankey jain]

David O'Meara wrote:You were saying that some queries show up a lot, but that isn't a problem in itself unless there is a performance problem in the DB or the application.
Yes, it is worth plugging in a low level cache, but don't try to over-solve the issue at this point if it isn't displaying any real problems.

Well, You are right ! But there is some concern to this as well. As these are not fired by the application, the queries are internal.
And we are not able to decide if this is causing any issues, as our database server is shared

but in the recent performance report, we did see the 20Gb ram hittting it s maximum capacity at one or two instances

i will take the point of oversolving and proceed further

please let me know other suggestions, if you may have any

Cheers

Sanket

[David Newton]

This is not a caching issue, it's likely an n+1 problem, but it's impossible to help without knowing more.

[Paul Sturrock]

I'd look at your fetch strategies. Are there HQL/Criteria queries running for obejcts where you always use the associated objects but don't eagerly fetch them? Are there any associations that are not lazily fetched when they should be? How do you use the Session? Is it open for the length of a business transaction, or do you just open and close it as needed?