-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: Hibernate causes a soft parse on every SQL statement
PostPosted: Mon Jun 20, 2005 5:23 am 
Newbie

Joined: Thu Apr 22, 2004 6:30 am
Posts: 4
Our Oracle DBA has noticed that the database is soft parsing each SQL statement issued by our Hibernate-based application. We are using Hibernate for batch processing - almost exclusively inserts - and he is concerned that the accumalitve effect of these soft parses is having a detrimental effect on performance. Is this behaviour expected, or is there some way to force Hibernate to reuse prepared statements? Note, we recently switched to using Apache DBCP with prepared statement caching enabled but according to our DBA, this hasn't reduced the number of soft parses. Any insights into this matter would be greatly appreciated.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 20, 2005 8:03 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
http://asktom.oracle.com/pls/ask/f?p=49 ... 8723819082


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 22, 2005 4:51 pm 
Beginner
Beginner

Joined: Tue Nov 11, 2003 4:49 am
Posts: 47
Location: Florence, Italy
Please, help me understanding that article.
It means that when I launch statistics on my production Database, and I see that for every statement generated by Hibernate
Code:
executions == parse calls

these are all soft parse ?
I made some experiment with Oracle drivers, and setting this property on the connection that I get from the pool:
Code:
conn.setConnectionCachingEnabled(true);

I obtain
Code:
parse calls==1

for every statement !!!!
If I understood well, this mean that with this Oracle Driver can let me skip also the soft parsing of SQL ?
I would like to understand......

_________________
Ciao.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 22, 2005 5:28 pm 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
In short hard parsing includes query plan calculation, soft parsing doe's not need to recalculate plan, but it needs to parse SQL, cache lookup and related locking is expensive anyway (hard parsing is more expensive). Solution to avoid soft parse is to cache cursors in session scope, oracle doe's this itself on server side (session_cached_cursors parameter). Good practice is to reuse statement objects in session or transaction scope, probably JDBC pools can help too.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 23, 2005 2:57 am 
Beginner
Beginner

Joined: Tue Nov 11, 2003 4:49 am
Posts: 47
Location: Florence, Italy
Thank you baliukas for your fast response.
I solved the problem, without reusing cursors on the client side, using JDBC 3.0 style of implicit caching and that worked great, NO more soft parsing too !!!!!
Now I'm rewriting Hibernate's DataSourceConnection class to use this and take the best of this feature.
Do you have any more suggestion ?

_________________
Ciao.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 23, 2005 3:30 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
check cursor limits in init.ora file, "Too many open cursors" error is very common with cursor cache.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.