File Library

In the popular novel (and movie) the Hunger Games, 24 competitors are selected to participate in the ultimate challenge that ends with only a single victor left alive. The contestants are encouraged with the statement "May the odds be ever in your favor." But, if you think about it, if the odds were in their favor, they wouldn't have been randomly selected in the first place. And given that only 1 out of the 24 will come out alive, the odds are (by default) particularly poor.

So, what does this have to do with developing an understanding of how the Oracle optimizer uses statistics? Well, in one sense, the life of every SQL statement a developer writes is engaged in a contest. A contest where the winners are the SQL statements that maintain stable performance over time. SQL that doesn't perform well can figuratively (and sometimes literally) kill the database. The question is whether or not a developer is doing everything possible to help their SQL make it through in one piece?

Understanding how the optimizer uses statistics to formulate the execution plan for a SQL statement is critical to tipping the odds in the developer's favor. The statistics the optimizer uses drive everything from whether or not a full table scan or index scan is used to what order tables are joined. The calculations the optimizer utilizes to make plan operation choices are rooted in some very simple statistical formulas. These formulas, if understood, can help developers make better choices when writing SQL and help them performance test their code so that they are confident in how well their code will perform over the long haul in production.

In this session, developers will learn:
* Statistics fundamentals for computing cardinality/selectivity for single and multi-column predicates.
* How statistics can help the optimizer understand data distribution patterns (skew).
* Reasons why certain ways of writing SQL limit the optimizer's ability to determine the best plan and how the optimizer uses query transformations to improve the odds.
* How to use extended statistics to help the optimizer create more accurate cardinality estimates.
* How the optimizer learns and self-corrects plan choices using cardinality feedback as well as a look at Oracle 12c enhancements such as adaptive execution plans and enhanced statistics.

Understanding how statistics are used will help developers ensure the odds are in their favor so that they consistently formulate better, more stable and higher performance SQL.