Hotsos Symposium 2013

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.

Title:

Working with Confidence: How Sure Is the Oracle CBO about its Cardinality Estimates, and Why Does It Matter?

Abstract:

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.

Files:

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:

The deployment instructions are here. The spec of the package is here, and the body is here.

Advertisements

One Response to Hotsos Symposium 2013

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: