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

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