Re: Best course to understand why a bad plan is chosen by optimizer

12.1.0.2 comes with 2 very bad parameters that are almost universally instructed to set from the defaults.

Optimizer_adaptive_plans & optimizer_adaptive_features

(Disclaimer: there's been a lot of changes in this area in 12.1 and Oracle backported a patch from 12.2 to fix it and further granularlize the adaptive features so I may have the parameter names a bit confused)

Anyway Oracle issued guidance for 12.1 and the adaptive features found here:

[image: My Oracle Support Banner]Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics & 12c SQL Performance) (Doc ID 2187449.1)

HTH Chris Taylor

(P.S. I cannot recommend strongly enough to go immediately to 12.2 and get off 12.1!)

On Thu, Sep 12, 2019, 8:57 PM kunwar singh <krishsingh.111_at_gmail.com> wrote:

> Thanks Mladen for the response.
> Yes , i have tried to pretty much everything under the sun with the
> statistics, histograms, dynamic sampling.
>
>
> On Thu, Sep 12, 2019 at 9:52 PM Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>>
>> On 9/12/19 9:43 PM, kunwar singh wrote:
>> > Hi Listers,
>> > Silly question :)
>> >
>> > Is there any Oracle guru who give course on this specific topic and
>> > goes much in depth.
>> >
>> > Not looking for course on sql tuning. I am able to tune the query in
>> > most cases, it is the optimizer decisions for many queries in a new
>> > version upgrade(11g to 12.1.0.2).. which are giving me some headaches
>> > as many applications we support generate queries dynamically and
>> > statistics optimization is the farthest i am able to go to get good
>> > plans.
>> > Cannot use SPM options due to dynamic nature of sqls generation.
>> >
>> >
>> Have you tried with different statistics options? System statistics,
>> OPTIMIZER_DYNAMIC_SAMPLING, proper histograms and alike? I found out
>> long time ago that collecting statistics properly usually gives me good
>> performance for 99% of the queries. The best way to tune is not to do
>> it at all.
>>
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
> --
> Cheers,
> Kunwar
>

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