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.

Paper Presentation


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.

Paper Presentation


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.

Paper Presentation


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.

Paper Presentation


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 findings presented are based on experience and tests with Oracle 8i (8.1.7) on Windows 2000, Linux Redhat 7.2, hp-ux 11.0, and Compaq Tru64 5.1.  Comments on changes in Oracle 9i are based on Oracle 9.2.0 on Windows 2000 and Linux Redhat 7.2.

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

Paper Presentation


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.

Paper Presentation

 


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.

Paper Presentation

 


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?
But instead
    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.

Paper Presentation

 


Joins, Skew and Histograms

Using examples the presentation looks at how histograms and skew in the value distribution affect the join cardinality estimate of the Cost Based Optimizer. A different join cardinality can result in a different join selection, a different join order, a different access plan and ultimately different performance of the SQL. Not necessarily better performance.

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

Paper Presentation

 


Active Statistics

The presentation demonstrates techniques to use statistics, both object and system statistics, as an active tool in performance tuning. It will show what can be done with statistics other than simply gathering them. The presentation, however, will also look at statistics gathering:

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

Paper Presentation

 


Anatomy of a SQL Tuning Session

The presentation will lead through the progression of steps tuning a query. It is based on the actual process of tuning a SQL to be introduced to production and highlights some of the techniques used to achieve a faster query - all based on the information available from DBMS_XPLAN.DISPLAY_CURSOR.
No need for a 10046 or 10053 trace.

The steps include

         modifying a view used in the query,

         altering the query itself

and eventually

         some hints.


The query which initially timed out at over 5 minutes ultimately ran in a few seconds.

The presentation was given at the 2010 Hotsos Symposium on Oracle® System Performance held March 7–11, 2010 in Dallas, TX.

Paper Presentation