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

The plan change only happened twice since 1st April, so setting 10053 trace for that exact sql_id to catch the odd ones , not sure how much time we have to wait to see that bad execution and if that will be helpful. So I was thinking if I could do something to find out the cause of the bad plan looking into history/awr views.
And also as manual execution of the SELECT part of the query is not changing plan anyway with the optimizer feature hint, so in this way also 10053 trace won't help us find the cause of the bad execution path.

On Fri, Apr 16, 2021 at 5:49 PM 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