A Look under the Hood of CBO: The 10053 Event

Have you ever been faced with a sql statement where you know that if only Oracle would use an index it would perform so much better? Or not use an index, or use a different driving table, or use a hash join, or, or, or ... If you are a dba then this is a rhetorical question. Generally, it is just a matter of using the appropriate hint to solve the problem. However, increasingly dbas are faced with sql statements that are generated dynamically by applications, where the source is inaccessible, or which can not be changed because of licensing or support restrictions. This paper will shed some light on the decisions the cbo makes when parsing a sql statement and choosing an access plan. It will show how the cbo calculates the cost of a plan and some of the rules and factors that go into these calculations. By extrapolation, if you know how the cbo assigned the cost of a plan, you may be able to make changes that lead the optimizer to choose a different plan – without the need to make changes to the sql statement. While you can not compare the costs of different explain plans, the optimizer does compare the costs of different plans, and chooses the one with the lowest cost, in its current parse tree! You just don’t see any of the plans which "lost out". Unless you activate the 10053 event trace, that is. There you see all the access plans the cbo evaluated and the costs assigned to them. So, lastly, this paper will act as a guide should you decide to venture into the jungle of a 10053 event trace yourself.

The presentation was given at the IOUG Live! 2002 conference in San Diego and again at the 2003 Hotsos Symposium on Oracle® System Performance February 9–12, 2003 in Dallas, Texas.

Fallacies of the Cost Based Optimizer

Does lower cost mean faster SQL? We all know that it does not. However, the premise of a cost based optimizer is just that. If that is so, then why is it not true?

When looking at an explain plan, do NOT look at the cost. By the time you see the end product the cost has become meaningless. It might as well be a random number. Instead look at the cardinalities. Do THEY make sense? The costs are a byproduct, derived from the cardinality estimates. If those estimates are right, the CBO does an excellent job of finding the optimal plan. Conversely, whenever the plan is wrong it can be tracked down to incorrect cardinality estimates. This paper and presentation shows some of the reasons why the estimates can be wrong - despite the best statistics.

The task of SQL (as opposed to application ) tuning then “simply” becomes helping the optimizer to correct its estimates – tuning with hints is like treating a symptom rather than curing the illness: in the best case we alter the cost associated with an incorrect estimate towards what the cost would be for the correct estimate.

When the estimates converge towards reality then there is also convergence towards a correlation between cost and performance – actually between cost and resource consumption, but there is generally a strong correlation between resource consumption and performance – unless the poor performance is due to wait issues, which is another area of tuning.

Achieving good correlation between cost and performance becomes even more important if you want to use the resource manager in Oracle 9i with max_estimated_execution_time because it is “based on the cbo's costed plan” (Tom Kyte). While blocking a sql because of a cost overestimation is bad, NOT blocking one because of a cost underestimation can be downright devastating.

This paper identifies three basic assumptions made by the cost based optimizer when it estimates the cardinalities of base and intermediate row sources of a SQL query. These assumptions, if violated, can render the cardinality estimates to be off by orders of magnitude and with them the basis for choosing the plan with the lowest cost. Unfortunately, in reality these assumptions tend to be frequently violated. We will examine examples of tables and queries which breach these assumptions and show how that affects the execution plan evaluation. We will also show what remedies and workarounds, if any, are possible to correct the estimates.

The presentation was given at the 2003 Hotsos Symposium on Oracle® System Performance February 9–12, 2003 in Dallas, Texas.

What is new in the Oracle 9i CBO 

Take an in-depth look at the new features of the Oracle cost based optimizer in Oracle 9i. Learn what  parameters and defaults have changed from Oracle 8i and what their impact is. Understand what difference collecting system statistics makes to the access plan costing. Compare 10053 event traces for the same sql statements between Oracle 8.1.7, 9.0.1, and 9.2.0 and look at the differences of both the trace output itself and as well as the differences in cost calculations and ultimately the resulting access plans. 

The presentation was given at the IOUG Live! 2003 and at the Atlantic OTC Spring 2003 conferences.

SQL Tuning with Statistics

This paper looks at the DBMS_STATS package and how it can be used – beyond just the gathering of statistics – in the tuning effort, particularly where the SQL may not be changed for technical or license reasons. After introducing the DBMS_STATS package, the paper demonstrates how it can be used to alter the access path – and the performance – of a SQL without touching the statement itself. 

The presentation was given at the Atlantic OTC Spring 2003 conference.

Using DBMS_STATS in Access Path Optimization

The paper and presentation look at the differences of gather_xxx_stats between Oracle 8 and 9. It will further show ways of transferring statistics between databases with caveats on what to watch for when using production statistics in a test database. Finally, in section 3 we will show two scenarios where the deliberate use of set_column_stats to change column statistics helps the optimizer choose a better, faster and more scalable access plan. The goal is to promote the notion that the statistics are a means to give the cbo information about the data in the database, that the statistics are not a “sacred cow” but that it is ok to alter them in order to enable the cbo to make the best possible access path decisions.

The presentation was given at the 2004 Hotsos Symposium on Oracle® System Performance March 7–10, 2004 in Dallas, Texas.

The Effects of optimizer_index_cost_adj and optimizer_index_caching on Access Plans

The Two init.ora parameters are widely touted as the “silver bullet” to end all CBO bad access path choices: OPTIMIZER_INDEX_COST_ADJ (OICA) AND OPTIMIZER_INDEX_CACHING (OIC). Everyone seems to have their own favorite numbers for them . Tim Gorman explains why and how to derive the numbers from formulas using Oracle system-wide statistics. This presentation shows where and how the setting of these parameters affect the index access cost and access path composition, contrasting OICA with the SREADTIM and MREADTIM values of the Oracle 9 and 10 system statistics. The goal is to come to a better understanding of the CBO’s index access cost “formulas”.

The presentation was given at the 2005 Hotsos Symposium on Oracle® System Performance March 6–9, 2005 in Dallas, Texas.

Histograms - Myths and Facts

The presentation sheds some light on Oracle's use of histograms. It explores different options available in Oracle 9 and 10 to gather column histograms and their effect on the histogram. It endeavors to dispel some commonly held misconceptions about histograms and offer guidance as to where histograms are useful and where not.

The findings presented are based on experience and tests with Oracle 9i (9.2.0.4/6) and Oracle 10g (10.1.0.1/3) on Windows 2000 and Linux Redhat ES 3.

The presentation was given at the 2005 Hotsos Symposium on Oracle® System Performance March 6–9, 2005 in Dallas, Texas, at Collaborate 06 April 23-27 in Nashville, Tennessee and at CBO Days June 21-22 in Zurich, Switzerland.

Tuning by Cardinality Feedback

Tuning by cardinality feedback looks at discrepancies between estimated and real row source cardinalities of an execution plan and attempts to find ways to correct the CBO’s error in estimation and trusting it to find a better plan based on the corrected, more accurate estimates.

The presentation was given at the 2006 Hotsos Symposium on Oracle® System Performance March 5–9, 2006 in Dallas, Texas and at CBO Days June 21-22 in Zurich, Switzerland.

Joins, Skew and Histograms