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
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
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
The findings presented are based on experience and tests with Oracle 9i (22.214.171.124/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.
Faced with an underperforming SQL,
the question the TCF method is trying to answer is not
What would be a better access plan?
Why is this plan, which the CBO chose as optimal, performing so poorly?
Once the answer to that question is found, the next goal is to find a way to remedy the cause for the miscalculation, but ultimately get out of the way and let the CBO do its job again.
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
A better understanding of this aspect of the CBO is becoming important as the automatic statistics gathering job of Oracle 10 also automatically gathers histograms on columns used in predicates.
Along the way we will uncover and highlight some changes in histogram gathering and usage in Oracle 126.96.36.199-8, 10.1.0.2-5 and 10.2.0.1-2
The presentation was given at the 2007 Hotsos Symposium on Oracle® System Performance held March 4–8, 2007 in Dallas, Texas.
The difference in the effect of the “stattab parameters” between gather_object_stats and gather_system_stats and how to “safely” gather and prepare system statistics
The effect of different parameter settings, estimate_percent and cascade, on the performance of gathering and the accuracy of the gathered object statistics
The presentation will then cover issues related to gathering statistics and possible solutions through actively using statistics
Problems with export_xxx_stats/import_xxx_stats in order to copy statistics to another partition, schema or database and another way of copying or cloning statistics besides export_stats/import_stats
Expose the danger of
sampling, i.e. estimate_percent < 100, when gathering statistics
Expose a hidden danger of index rebuild
Examples of how to use the prepare_column_values procedure to create your own column statistics
The presentation was given at the UKOUG 2008 Conference & Exhibition held December 1-5, 2008 in Birmingham, UK.
Anatomy of a SQL Tuning Session
The steps include
• modifying a view used in the query,
• altering the query itself
• some hints.
The presentation was given at the 2010 Hotsos Symposium on Oracle® System Performance held March 7–11, 2010 in Dallas, TX.