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@xxxxxxxxx> 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.
