Insights into Statistics, Query Optimization and the Oracle Optimizer

  • 12c
    March 1, 2017

New Optimizer Statistics White Paper

Nigel Bayliss
Product Manager

A new white paper on Oracle Optimizer statistics is now available. Just go to the Query Optimization page in OTN and click the Understanding Optimizer Statistics link.

Post comments to this blog if you want to give me feedback on the content.


Join the discussion

Comments ( 2 )
  • guest Thursday, March 2, 2017

    I have to think that my biggest disappointment with the Optimizer is the simplistic approach to statistics. Histograms sound like a great idea, right up until you have columns with NDV's of 100,000,000, and the "average" applies to virtually none of them. I've seen too many "adaptive" plans that calculate a hard "cusp" for the plan change, where they instantly become problem query. A lot of that has to do with the over optimistic calculation of full table scan times, which assume that no other full table scans will take place at the same time. Statistically speaking, mean and variance go hand-in-hand in understanding what the distribution looks like, yet in 20 years, Oracle has not considered variance meaningful for the optimizer. It also insists on calculating a "hard point" at which a plan will change, based on these imprecise numbers. I was hopeful that 12c would self correct over time through directives, but all they seem to do is make it worse. I can't help wondering if anyone working on the optimizer has ever studied statistics.

  • NigelBayliss Friday, March 3, 2017

    Hi there,

    It's true that histograms are not applicable in every situation. They are, after all, just one tool in the tool bag.

    Oracle has deal with the intractability of costing every possible SQL execution plan when a query is complex, especially if there's the potential to use a lot of different transformations or combinations of transformations. You’ll know that there are plenty of data models out there that have evolved into a state where a bunch of relational rules will be broken (e.g. non-atomic column values). This will make costing harder unless you bring into play additional techniques like virtual columns. There will always be predicate combinations that will make accurate cardinality estimation virtually impossible; no matter what statistical tricks are used (and that’s why we have dynamic statistics).

    The bottom line is this: finding the *best* SQL execution plan can be incredibly difficult, but the truth is that it isn’t usually necessary. “Good enough” goes unnoticed. I acknowledge that the best plan might be necessary if a super-critical query executes even just a tiny bit too slowly. Bugs or cardinality mis-estimates might make a query run for hours rather than seconds. Nevertheless - allow me to get philosophical for a moment - it's the bane of being a DBA (and Optimizer PM!) that a single bad execution plan will be the most important thing in the world, but the 10's of thousands of plans that are optimal or “good enough” will never be celebrated.

    Oracle acknowledges that optimization can’t be perfect. There are features and techniques to improve cardinality estimation and you’ve got to admit that we have great diagnostics and tools for resolving problems. I won’t make a list here in case you think I’m using this as a cheap opportunity to make a sales pitch (although I guess I did a little bit further up, but I couldn’t help myself).

    Yes, we introduced adaptive features to find better plans, and we are working to make this feature better. We also know that the majority of DBAs want *consistent* SQL response times that are "good enough" to fulfill the business requirement.

    You are always going to find cusps and inflection points no matter what statistical techniques you bring into play. The nature of estimation means that they won’t be in the best place all of the time. The nature of the real world is that there’s an almost unimaginable variety of queries and schema designs out there. This makes it very hard to predict (or even measure) the overall benefit of a change. I suppose I’m getting philosophical again.

    I’ll ping you an email in case you want to discuss further.



Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.