To be specific the stats gather currently happening on that list- hash
composite partitioned table as below method.
BEGIN dbms_stats.gather_table_stats('USER1', TABNAME => 'MFE',
GRANULARITY => 'ALL', cascade=>true, degree=>10, estimate_percent=>1);
END;
On Wed, Apr 21, 2021 at 9:07 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Thanks a lot. You are correct , that is really the case. And i verified
that from WRI$_OPTSTAT_HISTHEAD_HISTORY for column STAT. The days having
issues were holding num_dictinct as "3" as opposed to other days
NUM_DISTINCt for column STAT was "2".
This table is a list-hash composite partitioned table and perhaps due to
that reason the stats gather is happening in a non incremental fashion, but
with estimate_percent=>1, and thus even now i see the actual data contains
"3" distinct values for column STAT, but one of the value is very rare and
possibly for that reason when(in some unlucky day) the sample picks that
rare STAT value it updates the global column stats NUM_DISTINCT to "3" else
other days or say most of the day its populating NUM_DISTINCt as "2" thus
favoring index path.
So i think here , As long as the estimate_percent is not increased or say
made AUTO_SAMPLE_SIZE, we may suffer from the same issue even with a
frequency histogram on this column. So we have to work on
elevating sample_size. Correct me wrong.
STAT Count(*)
AA 48918649
BB 2930
CC 7
Regards
Lok
On Sat, Apr 17, 2021 at 12:45 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
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
