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

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_at_gmail.com> 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_at_gmail.com>
> 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_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