We have a query with literals and executed through execute immediate in a procedure. The query with same literal values, having same sql id is going for two execution plans intermittently most of the time the efficient plan is picked but some times it picks the inefficient plan. The difference between the plans is different index is picked for a huge table.
I just want to know the reason why sometimes inefficient plan is picked?
If stats are outdated then it would have picked inefficient plan consistently.
We can solve this by multiple ways but I want to understand what may be the reason for unstable plan?
Here's a great paper on the Adaptive Optimizer in 12c. In 11g, we had the beginings of that with a feature called "Cardinality Feedback"
https://www.doag.org/formes/pubfiles/9625966/2017-DB-Nigel_Bayliss-Optimizer_Adaption_from_Oracle_Database_11g_to_Today-Praesentation.pdf which covers all the details, but in a nutshell, what is happening is:
- we run a SQL
- we see that the real performance/resources/etc did not match the plan we estimated
- we flag that SQL as perhaps needing some adjustment for future executions based on the previous executions
- a different plan is then chosen.
The *aim* is that the different plan is *better* than the original one
The resolution is typically to use SQL Plan Management to lock the plan you want into place.