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

I have had quite decent results with disabling stats job altogether and setting OPTIMIZER_DYNAMIC_SAMPLING to 11. However, developers were traditionalists and didn't want to do that. If you come think of it, OPTIMIZER_DYNAMIC_SAMPLING=11 means that Oracle will gather what it needs when it needs. Why would I gather stats which will quickly get out of date? I have many indexes on date columns and every working day the maximum value for the column changes which means that the optimizer will ignore stats when it detects it. OPTIMIZER_DYNAMIC_SAMPLING=11 truly means "autonomous database". The price, of course, is giving up control over statistics and fixing SQL only by associating different baselines or adding hints using sys.dbms_sqldiag_internal.i_create_patch. As a matter of fact any automation means giving up control. That's just the nature of the beast.

On 9/22/19 9:54 AM, William Robertson wrote:
> 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
> <mailto: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:
>
>
> 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
> <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
>
>
>
>
> --
> Cheers,
> Kunwar
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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