There are many things that are hard to model so even when I have an idea
that might be a good explanation for some observation that's been reported
I don't try modelling it unless the requirement is urgent and important .
On that basis I can make two comments about your latest email.
a) With your settings (true / true ) Oracle still produces the full
adaptive plan even if it never takes advantage of it. I suspect that there
are cases where a plan WILL change when you switch but I don't think I've
proved it yet. If you check this blog note (
https://jonathanlewis.wordpress.com/2021/10/11/adaptive-study/ ;) starting
from the sentence " If you have an execution plan which, stripped to a
minimum, looks like this:" you will see the argument.
b) Why would the "alternative plan" look exactly the same as the actual
plan? Perhaps it's for the simplest reason of all. What it there's a
subroutine which says "Print the default plan", and another that says
"Print the plan that makes the choice eash time". Sometimes the plan with
choices will be the same as the default plan. Perhaps the title should be
"Plan with adaptive options taken" rather than "Alternative Plan" - the
latter makes it sound as if the "Alternative Plan" is supposed to be
different.
Regards
Jonathan Lewis
