If you really want to “understand why a bad plan is chosen by optimizer,” then
you run a wolfgang trace and read it.
If the plan is then “adapted” at run time, I am uncertain where you trace that
without looking it up and testing it for a specific set of parameters and
patches.
good luck.
Answering your latest question, yes, there are a lot of changes from 12.1 to
12.2 to address a lot of sub-optimal plan choices.
Likewise patches and updates through 19x, including patches. Run time plan
adaptation is complex and difficult to design correctly and program correctly.
What we *should* have is a session and system level single parameter:
no_adapt=TRUE|FALSE that either completely disables or allows following any
other parameter combinations.
If this hypothesized parameter were set TRUE, then it would eliminate run time
snooping and spending CPU even considering tweaking the plan.
Qualitatively this would be like RULE versus COST, but with respect to
adaptation.
Sigh. Apparently a user on/off control to feature development Oracle wants to
alpha test on the customer base is contrary to simplicity and automation.
If you cannot change your code, it still may be possible to tweak the original
plan chosen by (guaranteed short of complete laundry list):
1) Adding items that get extended column group and/or function extended
statistics correct so that the plan chosen is far better than any adaptation
possibility that may be seen at run time.
2) Adding, revising, or removing some index.
3) Physically rebuilding [very selectively for a reason, doggone it] tables
or partitions probably with well considered attribute clustering so that plans
that get run don’t generate feedback that makes adaptation appear useful.
4) If available, [very selectively for a reason, doggone it] add zonemaps.
[Don’t add zonemaps prior to 12.2 unless for sure you have the patches that
prevent the entire zonemap from being recalculated and rebuilt for every
commit, and don’t put a zonemap on something for which you generate many
commits per second. {Which should be doggone rare anyway.}]
5) Check and read Wolfgang, JL, Chris Antognini, and SQL Maria, even just
scanning them with a current problem in mind and there is a good chance you’ll
see something that either directly fixes it or makes you think of something
likely to fix it.
6) Get the inmemory option and learn how to put everything problematic in
memory.
7) Standard step seven: Get a pizza and a beer, and the answer will be
obvious when you return to work.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of kunwar singh
Sent: Saturday, September 14, 2019 5:30 PM
To: Chris Taylor
Cc: Mladen Gogala; ORACLE-L
Subject: Re: Best course to understand why a bad plan is chosen by optimizer
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:
My Oracle Support Banner
<https://support.oracle.com/knowledge/oracle-support-banner-opt1.png>
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.
