Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Prepared statement - when to use and when not to Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared statement - when to use and when not to" Watch "Prepared statement - when to use and when not to" New topic
Author

Prepared statement - when to use and when not to

anagha patankar
Ranch Hand

Joined: Dec 26, 2005
Posts: 53
Hello ,
Couple of questions related to prepared statement that am confused ( w.r.t Oracle )
1 >
Execute a PreparedStatement
2 >
Believe Oracle parses / creates execution plan and stores this in "Library Cache" ?
3 >
Question : If I close the Prepared statement and close the connection - would this affect the query plan in the "Library cache " of Oracle ?
4 >
When is the Execution Plan in LRU cleared ?
5 >
If I use a plain statement is the execution plan not cached by Oracle ?

Your observations / points would help me in getting a better understanding of the situation .

Thanks ,
-anagha
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333


Question : If I close the Prepared statement and close the connection - would this affect the query plan in the "Library cache " of Oracle ?

No. It remains in cache. When a textually identical SQL statement is submitted for execution (and other requirements are met), the execution plan is retrieved and reused. Oracle referst to this matching, testing, and re-use as a "soft parse" and it's much much much more efficient than a "hard parse" (when the statement isn't in cache).



When is the Execution Plan in LRU cleared ?

It's a classic LRU cache, so when the cache is full and a new statement/plan needs to go in, then the oldest is dropped. Closing statements or connections has no effect. The entire cache can be explicitly flushed by a DBA, and the cache is not preserved across database restarts. If you have the necessary privileges, you can see the statements in cache and get other information, from the V$SQL view. It's owned by the SYSTEM user and if you can query it, you don't need to qualify the view name with a schema, e.g. "SELECT COUNT(*) FROM V$SQL WHERE SQL_TEXT LIKE '%MY_BIG_TABLE%'". Accessing some of the V$ views causes very very brief memory locking (they're really structure in memory and not views or tables and the locking is to enforce some read consistency), so don't use complicated sub-queries or joins directly against these views, unless you know how to kill running SQl or have a forgiving DBA.


If I use a plain statement is the execution plan not cached by Oracle ?

All DML statements are cached, however the cache lookup is based on a hash code of the text of the SQL; if the SQL is not character-for-character identical, then no match. In other words, "SELECT password FROM my_users WHERE username = 'john'" would occupy one slot in the cache, and "SELECT password FROM my_users WHERE username = 'jane'" would occupy another. If the SQL statement was "SELECT password FROM my_users WHERE username = ?", then it occupies just one slot in the cache and allows plan reuse. Not only does not binding (binding = using ?) not allow plan re-use, but if this is executed with high frequency for enough values of username, it will start to push other things that could be reused out of the LRU cache, if they aren't being re-used frequently enough. It only take one heavily used statement that's not being prepared to have measurable impact on the database.
Your observations / points would help me in getting a better understanding of the situation .

So in other words, you should almost always use PreparedStatement with Oracle for all DML. There are a couple of very rare exceptions related to quirks in the way Oracle generates execution plans; the one I could explain best is the "data skew" problem. However, I and others explained several times elsewhere, if you're interested in this rare special case, look here:
http://forum.java.sun.com/thread.jspa?forumID=48&threadID=711505
http://forum.java.sun.com/thread.jspa?forumID=48&threadID=719888
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3126073805757
http://forum.java.sun.com/thread.jspa?forumID=48&threadID=665336
anagha patankar
Ranch Hand

Joined: Dec 26, 2005
Posts: 53
Thanks Stu for your most excellent explanation .
I have read the links that you have been so kind to refer for "dataskew"

I believe I definitely need to read some more regarding Prepared statements

So was just wondering - if performance is an issue ( Java to database communication )what would the factors be that I should be considering :

1 >
Use of proper JDBC driver .
2 >
Database that Java code is communicating with ( Oracle , SQL Server etc )
its pros / cons / peculiar behavioural characteristics
3 >
Judicious use of Statements Vs prepared statements

Please let me know if I am missing something .
Thanks,
-anagha
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared statement - when to use and when not to
 
Similar Threads
Pooling Prepared Statements
Prepared Statement Doubt!!
inserting an image in to database
Reuse PreparedStatement
slow query speed -- an Indexing problem ?