We are on Oracle 188.8.131.52 on Solaris 10. I have a situaion where a query coming from java code and which uses prepared statement is not doing well. Now if I put this hint /*+ CURSOR_SHARING_EXACT */ and run the sql with literal it goes very fast.
something like select ename from emp where empno=112; is very fast but select ename from emp where empno=:1 is very slow. Now we have to ensure the query does not use bind variable. How do I ensure it? What do I need to tell the developer so that he writes the sql in such a way that it doesn't use bind variables (please take other facts are proven - I mean that once the query doesn't use bind variable it goes very fast is repeatedly tested and confirmed, the question is now how to ensure it).
The question got resolved in a very simple and unexpected way! The java developer had to write the sql in this way: "select ename from emp where empno=112" instead of this: " select ename from emp where empno=?" He already did that and that fixed it!!
Thanks to everyone who shared his knowledge and views for my question....
If you really need to force hard parsing on every execution of a query, you can wrap it into a view and define a VPD policy with a randomly generated predicate function to achieve that (see http://oracle-randolf.blogspot.ru/2009/02/how-to-force-hard-parse.html?m=1). However, I would not recommend you to go down that road if the query runs frequently, because hard parsing is expensive.