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

i just tested few of the queries on a 12.2 env. Didnt resolve the issues .

On Sat, Sep 14, 2019 at 5:30 PM kunwar singh <krishsingh.111_at_gmail.com> wrote:

> Thanks Chris. We already tested with disabling these two parameters.
> Are there many changes in 12.2 compared to 12.1 that could fix such issues?
>
> On Thu, Sep 12, 2019 at 10:51 PM Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> 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
>>>
>>
>
> --
> Cheers,
> Kunwar
>

-- 
Cheers,
Kunwar

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