Re: Question on adaptive plans

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




text/plain attachment: Display_cursor_plan.txt