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@xxxxxxxxx>
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
