It was a great pleasure presenting at Hotsos.
Thank you for attending my presentation – your comments and questions were quite insightful. I owe a bit more explanation to the lady who asked about the mechanism/formula for passing filter errors into the resulting set. I’ll get more information about that soon – stay tuned.
Working with Confidence: How Sure Is the Oracle CBO about its Cardinality Estimates, and Why Does It Matter?
The Oracle CBO is tasked to provide an execution plan for every syntactically correct statement. To tackle this very broad requirement, the CBO has to come up with selectivity coefficients for every possible type of predicate. In some cases, the selectivity coefficients are deeply rooted in math and common sense — we know for a fact that lookup by primary key would result in one or zero records.
In other cases, however, the CBO has to take a guess, sometimes even a wild guess, on how much a particular predicate would affect the cardinality of the resulting set. A simple LIKE clause with a leading wildcard would force the CBO to default the selectivity coefficient to around 5%, a guess that could be very far from reality.
To measure the level of guesswork employed by the CBO, the notion of “confidence” level of a cardinality estimate is introduced. Estimates of the confidence level the Oracle CBO should be getting for common SQL predicates are presented. The effects of joins on the confidence levels are reviewed as well as methods to reduce the level of guesswork the optimizer has to employ.
The practical implications of considering CBO’s confidence in its cardinality estimates are discussed in detail. Guidelines on how to vet new SQL and PL/SQL features, designs, and coding standards are given to aid the CBO in avoiding unnecessary guesses about predicate selectivity. A technique for analysis and resolution of performance issues based on dealing with constructs that force the CBO to make guesses is presented.
The presentation is here.
The white paper is here. It is quite comprehensive, with lots of details and references.
Use XPLAN_CONFIDENCE package entirely at your own risk: