AW: Thought experiment

That should be „low“ cost … the sentence will make more sense if you correct the typo. :-(

> (1) SQL executed for first time, one sensitive bind variable used, the low cost execution plan is cached and the result set is consistent with expectations

> Am 22/03/2022 um 13:45 schrieb Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk>:
>
> Hi all, just thinking out loud …...
>
> I was reading this https://svenweller.wordpress.com/2017/12/13/exotic-sql-hints-that-can-change-results/ <https://svenweller.wordpress.com/2017/12/13/exotic-sql-hints-that-can-change-results/> blog article over lunch break and it occurred to me that if an SQL hint can change the execution plan (not contentious), and the result set can change depending on the execution plan chosen (contentious, but read Sven’s blog article for example, note it’s dated 2017), then are there edge case examples out there where consecutive executions of the same piece of SQL against the same data and same stats give different result sets?
>
> Is this even possible? I think it is but want to canvass opinion.
>
> I am thinking very specifically about this scenario:
>
> (1) SQL executed for first time, one sensitive bind variable used, the lost cost execution plan is cached and the result set is consistent with expectations
> (2) The sensitive bind variable is changed and the SQL executed a second time using the cached plan above. The second time around the actual vs. estimated vs. prior low cost is vastly different. The result set is the same as in (1)
> (3) SQL executed third time, same bind variable as in (2), Adaptive Cursor Sharing kicks in resulting in a different execution plan than in (2) but with lower cost (so up to now, this is all expected behaviour), but the result set returned differs than that observed above (so this is unexpected/bug)
>
> Mike
> http://www.strychnine.co.uk <http://www.strychnine.co.uk/>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l