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

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
>

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