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

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