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 .
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 .
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