Re: Multiple SQL version count with cusror_sharing=similar
Date: Wed, 1 Jul 2009 18:35:34 +0530
Many thanks Randolf. Your reply really justified the results.
I just checked that after parsing first version of the query, sql trace of second version (i.e. where deptno=20) is resulting in library cache miss. Need to re-run the case, and if result is consistent, will definitely share.
Oracle creating multiple child cursors, in hope that literal replacement may result in sub-optimal plan, but at last choose same execution plan, is a bizarre thing. It is related to safe and unsafe binding, that i will cover next by reading 10046 trace file. It is important to understnad in which cases, Oracle treats unsafe binding.
BTW, while search in metalink, there are numbers of bug hits related to the issue.