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

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