Hotsos Symposium Speaker – Iordan Iotzov

Iordan Iotzov is a returning Hotsos Symposium presenter.

Biography

Iordan IotzovIordan Iotzov is a senior DBA/Architect with more than ten years of database administration and development experience. His professional interests span many areas, with performance tuning being one of his favorites. During his career, Iordan has worked on complex database problems across various industries, providing original and effective solutions. Currently, he is a senior DBA at News America Marketing (NewsCorp). Iordan has an MS in Computer Science and holds numerous Oracle certifications.

Iordan was a speaker at Hotsos (2012), NYOUG (2010-2012) and BrainSurface VirtaThon (2011). He participates in OTN forums and actively blogs at http://iiotzov.wordpress.com/.

Presentation 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.

Presentation Materials

Presentation materials are available online to attendees only.

Schedule

The speaker schedule is as follows: