Clearly the two plans came from sessions with complete different optimizer
environments - nothing about the optimizer "accidentally" changes the OFE ,
someone much have included an explicit setting in some code.
Two things to note in your SQL Monitor report
a) the value of the in_dt is significantly different in the two reports, so
even if the object statistic had not changed the plan could change
significantly.
b) you said that STAT had two distinct values, but the estimate of the
index range scan is 14M on a table you say had 48M rows - which suggests
that the stats said there were three values and you didn't have a
histogram. The fact that the ACTUAL was then 49M suggests you do need a
histogram on the STAT column
You mention querying the AWR: one thing you could do is query the optimizer
stats history to see whether the stats on that table have changed over
time, and if any significant changes happened around the time of the
changed plans.
Regards
Jonathan Lewis
On Fri, 16 Apr 2021 at 19:56, Lok P <loknath.73@xxxxxxxxx> wrote:
I will try to see if I can mimic the plan by hinting it will have
different opt_estimate hints, but still the difference in outline and note
section of the bad execution path is something very odd and I can't figure
out why it's showing such?
On Fri, Apr 16, 2021 at 8:19 PM Laurentiu Oprea <
laurentiu.oprea06@xxxxxxxxx> wrote:
To me looks like there is a point when accessing the table using index
scan has a lower cost than accessing using full scan and looks to be
dictated by lower cardinality.
You can play around using opt_estimate to see where that limit is:
https://blog.pythian.com/oracles-opt_estimate-hint-usage-guide/
Most probably you need to baseline the good plan or use a SQL patch
On Fri, Apr 16, 2021, 15:19 Sayan Malakshinov <xt.and.r@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
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@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.
--
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
