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

From: William Robertson <william_at_williamrobertson.net>
Date: Sun, 22 Sep 2019 14:54:11 +0100
Message-Id: <2E985E2D-6AE8-4423-8F9C-FA70315103E8_at_williamrobertson.net>



I had a couple of thoughts about the earlier suggestion to disable optimizer_adaptive_plans and optimizer_adaptive_statistics, since this is being proposed at my current site which seems to have a similar workload as Kunwar's (12.1 batch/reporting system generating dynamic queries against a somewhat challenging data model). It has seemed to me that these are generally useful features even if they occasionally cause problems (and when there are problems, it is never clear that adaptive features were the direct cause anyway). Having a query run better on the second attempt is a pretty cool feature when it works. It seems the downside is that occasionally a terrible plan appears for no obvious reason.

Another reason for disabling them is that they can play havoc with incremental statistics. Say you have a large subpartitioned table that takes 5 hours to gather stats from scratch, but your incremental stats are by some miracle currently up to date and it'll only take 1 minute to gather for the subpartition you have just loaded. Then due to a detected cardinality miss-estimate in some dynamically generated report, Oracle adds an extended stats column group. Now your next stats gather will take 5 hours again, instead of the 1 minute you were expecting. This means you simply can't risk gathering stats on the subpartition you have just loaded as part of your batch job, because it might take 1 minute or it might take 5 hours. Or, you can, but only by using granularity 'PARTITION' or 'SUBPARTITION', which have the disadvantage of being (1) mutually exclusive and (2) incompatible with incremental stats, so there is still the 5 hour stats penalty waiting for your overnight schema stats job, which is now unlikely to complete within its processing window, so your stats are permanently broken. Without adaptive features, incremental statistics has a chance of working as advertised.

Another point while I remember: if you simply disable the adaptive features after they have been in use for a while, unless I am missing something you will still have the plan directives, extended statistics column groups and their associated stats and histograms that were generated while the features were in force. If you suspect that adaptive features are causing problems, you should probably get rid of those as well.

The 12.2 (also backported to 12.1) parameters are nicely summarised here: https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2

William Robertson

On 13 Sep 2019, at 03:51, 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:  

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 <mailto: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 <mailto: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 <http://www.freelists.org/webpage/oracle-l>




-- 
Cheers,
Kunwar


--
http://www.freelists.org/webpage/oracle-l
Received on Sun Sep 22 2019 - 15:54:11 CEST

Original text of this message