Re: Question on adaptive plans

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

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