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@xxxxxxxxx <mailto:christopherdtaylor1994@xxxxxxxxx>> 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@xxxxxxxxx <mailto:krishsingh.111@xxxxxxxxx>> 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@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
-- Cheers,
Kunwar
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
