Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate 3.0 Issue, Queries executed millions of times

 
sankey jain
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
awrrpt_1807_1907.jpg
[Thumbnail for awrrpt_1807_1907.jpg]
Awr Report 1 rename to HTML after download
 
swapnil kachave
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Author
Rancher
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is not a caching issue, it's likely an n+1 problem, but it's impossible to help without knowing more.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic