Re: Question on adaptive plans

Thank you so much. Another thing i noticed, even though we have 'optimizer_adaptive_plans' set as default TRUE, but we have 'optmizer_adaptive_reporting_only' also set as 'TRUE'. So in this case the optimizer should only report but should not change the runtime execution paths from the runtime actual rows estimation, so why is it still impacting us then? Isn't that setting "optmizer_adaptive_reporting_only" to TRUE is as good as not having adaptive optimization implemented in the query?

On Tue, Dec 28, 2021 at 4:01 AM Gogala, Mladen <gogala.mladen_at_gmail.com> wrote:

>
> On 12/27/2021 10:33 AM, Lok P wrote:
> > Thank you so much.
> >
> > Yes this is an exadata system. And mostly OLTP kind of
> > queries/applications hosted on this. So , do you mean "Adaptive plans'
> > will not be of much use or help for OLTP but for warehousing type of
> > queries/applications and thus in our case we should be good here
> > disabling it. And i was thinking if this variation in execution path
> > during run time (i.e. from nested loop to hash join and vice versa)
> > because of the runtime cardinality estimation can result in
> > instability of the execution path, if it's better to keep it disabled
> > at least in this current 19.9 patch?
>
> Yes, adaptive plans will not do you much good with an OLTP application
> and yes, you can keep it disabled. Keeping it disabled will shorten the
> path through the code and result in faster execution.
>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
> Blog: https://dbwhisperer.wordpress.com
>

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