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

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_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 <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_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> 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




 

-- 

Cheers,
Kunwar




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