Re: Plan Baseline

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_at_gmail.com> 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?
>
>
>
>

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