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

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