In our case in one one of the migration scenarios from 11.2 to 19C we were
experiencing such plan deviation(statistics collector influencing the path
of a first_row query in suboptimal way) with default
optimizer_adaprive_reporting_only i.e false. But then turning it to TRUE
was making that behave good. Are you sure in your case it's already set as
non default i.e. TRUE but still 'statistics collectors' are influencing the
path?
On Wed, Dec 29, 2021 at 12:30 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Thank You Jonathan.
I tried fetching the plan using display_cursor and also the sql monitor
side by side. And attached the same here.
*S*till trying to understand your comment *"The type of bug I'm thinking
of would then be the join has managed to get a non-zero count from a
statistics collector row when the count should have been zero. Since 11g
has no statistics collector operation this mistake would not have been
possible in that version."*
Do you mean to say the statistics collector has provided wrong
cardinality information causing it to evaluate the line 74-75, which in an
ideal scenario would not have been evaluated? But Jonathan , my
understanding was these statistics collector information will be fed to the
optimizer in runtime only when optimizer_adaprive_reporting_only set as
FALSE. But in our case its set a non default value i.e. TRUE, so why is it
still using those?
Also trying to understand if this behavior is buggy , is just in this
patch 19.9 and if it's fine to disable the adaptive_plan by setting
optimizer_adaptive_plans to FALSE at system level or would it be a foolish
idea to do so.
On Tue, Dec 28, 2021 at 5:17 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Its' very hard to tell when all the adaptive lines are in place, but it
looks to me as if there's a possibility you've found a bug in the adaptive
code path.
I'd have to see the plan pulled from memory without the 'adaptive' bit
being reported so that I could see the ultimate path.
All your time SEEMS to be spent in an indexed access at operations 74/75
which should not have happened because of a join which has no "first child"
rows, but untangling which operations actually occured makes it hard to be
certain.
If I've worked it out correctly, it would suggest that a join with no
data in the first rowsource has called its second rowsource when it
shouldn't have done so. (The type of bug I'm thinking of would then be the
join has managed to get a non-zero count from a statistics collector row
when the count should have been zero. Since 11g has no statistics collector
operation this mistake would not have been possible in that version.)
Regards
Jonathan Lewis
On Mon, 27 Dec 2021 at 12:16, Lok P <loknath.73@xxxxxxxxx> wrote:
Hello Listers, During one of our database migrations from version 11.2
to 19C(19.9 patch), we encountered many similar queries suffering from plan
changes and running longer. And those were complex queries with 10+ tables
in the join conditions etc. But as we kept encountering the same for many
we tried creating profile/patch for some with OFE 11.2 hints but then endup
setting the OFE back to 11.2.0.4 in system level so as to avoid
application/customer impact.
After verifying the difference in explain plan we found the new plans
were having 'statistics collectors' in many of the lines and mostly adding
some buffered operations and changing joins causing slowness for these
queries. And these are part of 'adaptive plan' which has been introduced in
this new version and I have attached a sql monitor for one such execution ,
it was taking seconds in 11.2 vs 19c and it was running for 10minutes+.
Tried by tweaking the optimizer_adpative_reporting_only to TRUE but was
getting similar results and by setting optimizer_adaptive_plans to false
only its finishing in quick time.
Now , as we want to move ahead and make the OFE back to 19.1.0 at system
level. So my question was , is it okay to set the optimizer_adaptive_plans
set as 'FALSE' at system level in this scenario? Or will it cause any other
harm or say we end up missing some related good 19C features?
Regards
Lok
