Re: Plan change with difference in Note and plan outline section.

Clearly the two plans came from sessions with complete different optimizer environments - nothing about the optimizer "accidentally" changes the OFE , someone much have included an explicit setting in some code.

Two things to note in your SQL Monitor report a) the value of the in_dt is significantly different in the two reports, so even if the object statistic had not changed the plan could change significantly.
b) you said that STAT had two distinct values, but the estimate of the index range scan is 14M on a table you say had 48M rows - which suggests that the stats said there were three values and you didn't have a histogram. The fact that the ACTUAL was then 49M suggests you do need a histogram on the STAT column

You mention querying the AWR: one thing you could do is query the optimizer stats history to see whether the stats on that table have changed over time, and if any significant changes happened around the time of the changed plans.

Regards
Jonathan Lewis

On Fri, 16 Apr 2021 at 19:56, Lok P <loknath.73_at_gmail.com> wrote:

> I will try to see if I can mimic the plan by hinting it will have
> different opt_estimate hints, but still the difference in outline and note
> section of the bad execution path is something very odd and I can't figure
> out why it's showing such?
>
> On Fri, Apr 16, 2021 at 8:19 PM Laurentiu Oprea <
> laurentiu.oprea06_at_gmail.com> wrote:
>
>> To me looks like there is a point when accessing the table using index
>> scan has a lower cost than accessing using full scan and looks to be
>> dictated by lower cardinality.
>>
>> You can play around using opt_estimate to see where that limit is:
>> https://blog.pythian.com/oracles-opt_estimate-hint-usage-guide/
>>
>> Most probably you need to baseline the good plan or use a SQL patch
>>
>> On Fri, Apr 16, 2021, 15:19 Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:
>>
>>> Lok,
>>>
>>> Just compare 10053 traces for both cases, you can get them using 'alter
>>> system set events 'trace[SQL_Optimizer.*][sql: your_sqlid]'; when they will
>>> run again
>>> or reparse and dump existing ones using
>>> https://blogs.oracle.com/optimizer/capturing-10053-trace-files-continued
>>>
>>> On Fri, Apr 16, 2021 at 3:06 PM Lok P <loknath.73_at_gmail.com> wrote:
>>>
>>>> Thank you Sayan. It seems v$ses_optimizer_env will be populated when
>>>> that query is running , so I need to wait for that occurrence in run time.
>>>> However, when i tried that same query with hint
>>>> /*+optimizer_features_enable('11.2.0.4')*/ and
>>>> /*+optimizer_features_enable('11.2.0.3')*/ the plan didn't change. And in
>>>> both cases I am seeing the good plan when testing the SELECT part of that
>>>> INSERT query. So it seems it's not that straight forward and something else
>>>> is happening.
>>>>
>>>> On Fri, Apr 16, 2021 at 5:15 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Lok,
>>>>>
>>>>> Looks like that session changes OFE on session level.
>>>>> Check it using:
>>>>> select * from v$ses_optimizer_env e where
>>>>> name='optimizer_features_enable' and sid=&sid;
>>>>>
>>>>> On Fri, Apr 16, 2021 at 2:38 PM Lok P <loknath.73_at_gmail.com> wrote:
>>>>>
>>>>>> Hello Listers,
>>>>>>
>>>>>> We are seeing one of the queries occasionally change its execution
>>>>>> path. And as i understand, in such types of cases normally stats is the
>>>>>> cause but i was not able to point out any stats which can be the cause
>>>>>> here. However, what I found is some odd difference in the "Note" section
>>>>>> pointing to some parallel execution. But this query is executed in serial
>>>>>> only, no parallel hint used. And also the outline section is different in
>>>>>> terms of parameters for both the plans. We have our database version 12.1
>>>>>> with optimizer_feature_enable as 11.2.0.4, but in the case of outline
>>>>>> section in good plan , i am seeing OFE as '11.2.0.3' wondering from where
>>>>>> is this coming from. This sql is executed by one and the same process but
>>>>>> we are seeing the plan change twice in the month of April.
>>>>>>
>>>>>> I have attached the good and the bad plan with sql monitor and
>>>>>> outline section. The index-MFE_IX4 which the bad plan is opting is on
>>>>>> column (STAT, CODE2) both having 2 distinct values each in that ~48million
>>>>>> rows of partition table SFE. Wondering what is causing that not so
>>>>>> selective index to be chosen in case of a bad path. Is the note section
>>>>>> pointing to anything suspicious?
>>>>>>
>>>>>> fyi, Oracle version 12.1.0.2 with optimizer_feature_enable- 11.2.0.4.
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best regards,
>>>>> Sayan Malakshinov
>>>>> Oracle performance tuning engineer
>>>>> Oracle ACE Associate
>>>>> http://orasql.org
>>>>>
>>>>
>>>
>>> --
>>> Best regards,
>>> Sayan Malakshinov
>>> Oracle performance tuning engineer
>>> Oracle ACE Associate
>>> http://orasql.org
>>>
>>

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