Re: Question on adaptive plans

Thank you so much Jonathan for the detailed suggestion. It really helped.

Something strange I am seeing, As i was still trying to understand the difference between two parameters "optimizer_adaptive_plans" and "optimizer_adaptive_reporting_only". As I mentioned in our case optimizer_adaptive_reporting_only is true(which is non default) and optimizer_adaptive_plans is aso true(which is default) and so as per the below oracle doc and also I am expecting to not see any plan change in effect because of adaptive optimization.

If i see the display_cursor report with 'adaptive+report' option (as attached), it's showing two plans and exactly both the plans with the same phv. However as per note section , one plan i.e "the first one in the report which it's currently taking" which is matching the "real time sql monitor report" too and the other one it's claiming it would have taken that plan if optimizer_adaptive_reporting_only would have been set as false. However what I am wondering is that both the plans are the same , howcome so? And the same is also experienced even though I set the OFE hint to 11.2.0.4. It's(statistics collectors in the plans) only disappearing with setting optimizer_adaptive_plans to false and optimizer_adaptive_reporting_only seems to have no effect here. So wondering how differently these two parameters impact the optimizer decision then?

*- this is an adaptive plan (rows marked '-' are inactive)*

*Adaptive plan:-------------This cursor has an adaptive plan, but adaptive plans are enabled for reporting mode only. The plan that would be executed if adaptive plans were enabled is displayed below.*

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_ADAPTIVE_REPORTING_ONLY.html#GUID-8DD128F9-4891-4061-9B2D-9D45315D44FB

 select * from
table(dbms_xplan.display_cursor('&sql_id','&child_no','adaptive +report'));

On Wed, Dec 29, 2021 at 5:56 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> Here's a quick starting reference to what the STATISTICS COLLECTOR does
> and how adaptive plans choose at run-time between nested loop and hash
> join:
> https://blogs.oracle.com/optimizer/post/whats-new-in-12c-adaptive-joinss
>
> The statistics collector is NOT doing cardinality calculations, it is
> buffering and counting rows from its child so that the run-time engine can
> decide whether the next join should be a hash join or a nested loop join.
> When you understand this, the meaning of the comment you quoted may become
> obvious: I think that MAYBE your problem is appearing because the
> statistics collector has an empty buffer but a flaw in the code that passes
> the (empty) buffer to the parent operation still results in a call to the
> second child that shouldn't be happening. (There is some precedent for
> imagining that this might happen based on the behaviour of parallel hash
> joins: https://jonathanlewis.wordpress.com/2014/02/28/empty-hash/ )
>
>
>
> Here's the reference from 19c for the parameter adaptive_reporting_only:
> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_ADAPTIVE_REPORTING_ONLY.html#GUID-8DD128F9-4891-4061-9B2D-9D45315D44FB
> Again you may understand this better once you understand what the
> Statistics Collector operation does.
>
>
> >>> 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?
>
> General Guideline - it is better to disable one feature that's causing a
> problem than to set the optimizer_features_enable back to an old version.
>
> Guideline 2 - if a feature can be enabled or disabled by a hint (applied
> through an SQL_Patch, possibly) then you have to decide whether to enable
> the feature and fix the few things that perform badly because of the
> feature, or disable the feature and fix the few things that perform badly
> because the feature is disabled - and that's more political than technical.
>
> Guideline 3 - don't expect anyone to be able to give you good answers to
> questions like that when they know virtually nothing about your system,
> working environment, and office politics. The best you will get is a
> personal opinion based on experiences with other systems.
>
>
> My personal preference for a case like this - guided partly by the fact
> that I have the skill and time to investigate whether or not it's a bug and
> produce a repeatable test case for Oracle Support to look at, partly
> because you said you're looking at an OLTP system (see Mladen's comment
> about that) - would be to disable the feature (optimizer_adaptive_plans =
> false) and hint any queries /*+ adapative_plan */ that were going badly
> wrong because they were more like DSS queries than OLTP queries but the
> optimizer was using indexed access and nested loops.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
> On Tue, 28 Dec 2021 at 19:00, 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




text/plain attachment: Adaptive_reporting_only_test.txt