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

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Sun, 22 Sep 2019 16:54:31 +0200
Message-ID: <1833b1cf-d12f-dd6a-d0a6-397321066661_at_bluewin.ch>



You should have done all off that at midnight in a full moon night. I have terribly good results with that.

Am 22.09.2019 um 16:11 schrieb Mladen Gogala:
>
> 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
Received on Sun Sep 22 2019 - 16:54:31 CEST

Original text of this message