Re: cardinality feedback in 19c

Hello Sayan

Thanks for your answer. It confirms why I haven't experienced any performs pain with cardinality feedback in all the 19c databases I have to deal with. Until today where 6 critical queries changed their execution plan from a good one to a very bad one and the non-sharing reason was USE_FEEDBACK_STATS Best regards
Mohamed

Le mar. 27 avr. 2021 à 14:20, Sayan Malakshinov <xt.and.r_at_gmail.com> a écrit :

> Hi Mohamed,
>
> "_optimizer_use_feedback" works differently since 12.2 (and on 12.1.0.2
> after the patch for bug# 22652097):
> https://antognini.ch/2016/10/adaptive-query-optimization-configuration-parameters-preferences-and-fix-controls/
> So it works pretty good now and with the disabled
> OPTIMIZER_ADAPTIVE_STATISTICS by default, I do not change
> _optimizer_use_feedback now.
> If I need to fix some rare issues, I do that only for specific queries
> using SQL profiles or patches.
>
> On Tue, Apr 27, 2021 at 1:29 PM Mohamed Houri <mohamed.houri_at_gmail.com>
> wrote:
>
>> Hello
>>
>> In 11g and 12c, I used to disable the parameter _optimizer_use_feedback
>> because the re-optimization was always producing a suboptimal execution
>> plan. But I kept this parameter enabled while upgrading to 19c. And, after
>> more than 10 19c upgrades I haven’t noticed any performance issue induced
>> by the cardinality feedback feature. Until today, where a bunch of queries
>> changed their execution plan (from 1 second on average to 300 seconds)
>> because of cardinality feedback usage
>>
>> Are you still disabling this feature in 19c?
>>
>> Thanks
>>
>> Mohamed Houri
>>
>> --
>>
>> Houri Mohamed
>>
>> Oracle DBA-Developer-Performance & Tuning
>>
>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>
>> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>
>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>> <https://twitter.com/MohamedHouri>
>>
>>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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