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@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:

 

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@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@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