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

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