Re: Question on adaptive plans

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