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

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

>
> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com>
>>> 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_at_gmail.com> 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_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
>>>>>>
>>>>>
>>>>
>>>> --
>>>> Best regards,
>>>> Sayan Malakshinov
>>>> Oracle performance tuning engineer
>>>> Oracle ACE Associate
>>>> http://orasql.org
>>>>
>>>

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