You might find something useful in this note (which was based on 12.1) -
especially in the stream of updates:
https://jonathanlewis.wordpress.com/2016/08/02/adaptive-mayhem/ and linked
articles.
What were your conclusions about the 8168 vs. 10 estimates for the
collection which looks as if it might be thing driving the change in plan.
What makes you think that switching the optimizer back to 11.2.0.4 will
make a difference? It might, but whatever has made it possible for the
plan to vary in 12.1 may still allow the plan to vary in 11.2. Have you
installed the patch that allows adaptive features to be split into adaptive
plans and adaptive stats - if so then disabling adaptive stats through a
hint in this query *might* be a better idea (see the link from my note to
Mike Dietrich's blog about the patch ).
I note you have 3 fix controls in the query. One of them relates to
adaptive dynamic sampling, but I can't find the other two in
v$system_control in a default (unpatched) install, so I would want to check
what they were, and possibly switch them back to their defaults as well.)
The difficulty with trying things that look as if they MIGHT help when you
don't know why the problem exists is that your tests could make it look as
if the problem is solved - right up to the moment you go to production and
discover that the fix doesn't work and the randomly appearing plan still
appears for no apparent reason - which means I wouldn't want to base my
hopes on a couple of hints that tweak the environment unless I knew they
would definitely work and were only to be used as a temporary workaround
pending a correct fix.
Regards
Jonathan Lewis
On Wed, 11 Aug 2021 at 07:10, manikandan <pvmanikandan@xxxxxxxxx> wrote:
Hi Jonathan,
Thanks for the detailed explanation. We are going to enable 10053 trace
for the sql today and will keep you posted.
I have already verified the baseline and ENABLED, ACCEPTED , FIXED --> YES.
Sorry for the confusion, the baseline is "pinned" --> I meant SPB was
created on the new PHV for the sql.
We are also planning to put the hints "
opt_param('_optimizer_use_feedback' 'false')
opt_param('optimizer_features_enable', '11.2.0.4') " to this sql code as an
interim solution until the code rewrite. What is your thoughts on this?
