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@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:
[image: 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>
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>
wrote:
On 9/12/19 9:43 PM, kunwar singh wrote:
Hi Listers,Have you tried with different statistics options? System statistics,
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.
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
