Overview - Iordan Iotzov's technology blog



Working with Confidence: How Sure Is the Oracle CBO About Its Cardinality Estimates, and Why Does It Matter?Iordan K. IotzovOverviewAsk WhyA typical SQL tuning exercise begins with identification of a statement that is taking too long to execute. The three major reasons, excluding bugs and locking, are poor physical database design, inadequate hardware resources and Optimizer error.Poor physical database design can cover a multitude of areas including lack of supporting structures such as indexes or materialized views, inefficient partitioning strategies, etc. Inadequate hardware resources are self-explanatory. When we refer to Optimizer error we are talking about cases in which Oracle picks a subpar execution plan that leads to poor performance even though an alternative plan that would finish in acceptable time exists. Optimizer errors are typically caused by incorrect cardinality estimates, which in turn are consequence of flawed statistics and guessing!All statistics stored in the data dictionary must be in sync with the corresponding data at all times. When that is not the case, for whatever reason, we have flawed statistics. Since table and index statistics are used extensively by the optimizer, errors in those statistics usually result in incorrect cardinality estimates that eventually lead to a suboptimal execution plan. The default statistics gathering mechanisms usually work fine for non-volatile data, even though we might occasionally need to manually adjust some statistics (Lewis, 2006). Dealing with volatile data presents a bigger challenge, but there are number of effective techniques (Iotzov, 2012).Guessing the selectivity of predicates is another major reason for Optimizer errors. The Oracle CBO is an excellent piece of software that is able to decipher very complex statements; however, it does not have a crystal ball. Since it is expected to assign selectivity to every predicate, no matter how complex, the CBO is frequently left with no other option but to guess. When a guess turns out to be incorrect, the related cardinality estimates are off and the chances of picking a suboptimal plan are higher. Unnecessary guesswork in design and troubleshooting is rightfully criticized by most responsible database professionals and there is even a party dedicated to addressing the issue (BAAG, n.d.). Less attention is paid to guesses made by complex software, such as the Oracle CBO. Even though we cannot change the software and most of the assumptions are the best assumptions that could be made under the circumstances, we still need to know about the guesses made by the optimizer. That knowledge would not only help us write better SQL, but would also enable us to troubleshoot more effectively. The amount of guesswork included in a cardinality estimate could be used by the optimizer to deliver better execution plans. For example, the CBO could decide to go with a robust option, such as hash join, when it detects that it is not confident about a cardinality estimate. Further exploration of this topic is beyond the scope of this paper. Foundations of Estimating CardinalityJoinsCardinality has a central role in SQL optimization. It drives most optimizer decisions, from the order of processing and the type of joins, to the way to access the records. REF _Ref377722400 \h Figure 1 depicts a join in Oracle. All joins have two source sets and one result set. Most joins also have a predicate that links the two source sets. Figure SEQ Figure \* ARABIC 1: Join of two sourcesThe cardinality of the result can be computed using the following formula (Lewis, 2006).CardResult=CardSource 1 ×CardSource 2×SelPredEven though the above formula is used in the Oracle CBO, it does not account for errors. Errors are inevitable and accounting for them can enhance any model. REF _Ref377722518 \h Figure 2 shows a join of two sources with consideration for errors. The cardinality of each of the sources is not a specific number, but rather a range, shown with light blue lines. The cardinality of the result is also a range. The ranges for the result set are significantly wider compared to the ranges of the source sets. Taking into account the distribution of the errors would result in an even better model, but for simplicity we are not going to pursue that route. Figure SEQ Figure \* ARABIC 2:Join of two sources, accounting for errorsThe formulas for the lower and upper level of the cardinality range are: (Ioannidis 1991)1+e+RCardResult=1+e+S1×CardSource 1 ×1+e+S2×CardSource 2×SelPred1-e-RCardResult=1-e-S1×CardSource 1 ×1-e-S2×CardSource 2×SelPredWhere the relative cardinality error of the result set is in between -e-R and e+R . Respectively, the relative cardinality error for source 1 set is between -e-S1 and e+S1 and for source 2 set is between -e-S2 and e+S2 .For clarity, we do not take into considerations the errors introduced by the predicate. The formulas for the lower and upper level of the cardinality range imply that the maximum error grows exponentially with the number of joins. An illustration of that behavior is shown in REF _Ref377722581 \h Figure 3. If we assume that each basic set comes with 10% error, we can observe that after six joins, the maximum relative cardinality error goes up to 110%. Figure SEQ Figure \* ARABIC 3: Propagation of errorsFilters REF _Ref377722672 \h Figure 4 depicts filtering a source set with two filters. Oracle allows enormous flexibility in specifying filters, so this is a rather simple scenario.Figure SEQ Figure \* ARABIC 4:Two filters applied to a source setAssuming that the two filters are linked with an AND logical operator, the cardinality estimate of the result set would be CardResult=CardSource ×SelFilter 1×SelFilter 2according to Metalink Note 10626.1 (MOS 2012a). REF _Ref377722783 \h Figure 5 shows the same two filters, along with the errors that they would introduce.Figure SEQ Figure \* ARABIC 5:Two filters applied to a source set, accounting for errorsSince our focus here is on filters, we would ignore any errors that may come from the source set. Below are the formulas for the lower and upper level of the result cardinality range.1+e+RCardResult=CardSource ×(1+e+F1)SelFilter 1×(1+e+F2)SelFilter 21-e-RCardResult=CardSource ×(1-e-F1)SelFilter 1×(1-e-F2)SelFilter 2There relative error for filter 1 is between -e-F1 and e+F1 , and the relative error for filter 2 is between -e-F2 and e+F2 REF _Ref377722843 \h Figure 6 illustrates how errors in multiple filters affect the error of the result set. Figure SEQ Figure \* ARABIC 6: Aggregation errors from multiple filtersEach filter contributes to the error, so the more filters we have, the higher the error. It must be noted that we are looking at the relative error. It is possible for a single filter to produce higher absolute error than a combination of that filter and some other filters. The relative error of multiple filters would be higher though.Confidence of Cardinality Estimates DefinitionI consider confidence of a cardinality estimate to be inversely related to the maximum relative cardinality error. Figure SEQ Figure \* ARABIC 7: Confidence of cardinality estimates REF _Ref377722948 \h Figure 7 illustrates the point. The absolute value of the cardinality error is not important. The value of the error compared to the value of the set is what is important. Maximum cardinality error of 1M for a 100M set is not a problem, while the same 1M maximum cardinality error for a 50K set is certainly a cause for concern.Current StateThroughout the paper, I assume that the Oracle CBO does not universally compute or use confidence level of its cardinality estimates. That is, in most cases, the CBO is not aware whether an estimate is based on sound mathematical models or whether it is based on guesses. My assumption about Oracle CBO’s behavior is based on the following three factors.First, I was not able to find any mention of confidence, maximum error of estimate or anything that might imply that Oracle computes or uses this type of statistic. Next, I couldn’t find any information about the topic by independent experts. In fact, Jonathan Lewis was very nice to confirm in general my assumptions regarding this matter (Lewis, 2012) (Lewis,2013).Finally, I was not able to find any information in the 10053 trace files that may relate to confidence of estimates. I ran a trace on two similar queries. REF _Ref377723020 \h Figure 8 shows a query with a LIKE predicate with a leading wildcard, a construct whose selectivity is genuinely difficult to assess. select tab2.* from? tab1 , tab2where tab1.str like '%BAA%'and tab1.id = tab2.idselect tab2.* from? tab1 , tab2where tab1.str like '%BAA%'and tab1.id = tab2.idFigure SEQ Figure \* ARABIC 8: Query that forces the CBO to make a wild guess REF _Ref347237757 \h REF _Ref377723020 \h REF _Ref377723118 \h Figure 9 shows a query that contains an equality predicate, a popular construct whose selectivity can be reliably measured, particularly if the underlying data is not skewed.select tab2.*from tab1 , tab2where tab1.NUM = 14and tab1.id = tab2.idselect tab2.*from tab1 , tab2where tab1.NUM = 14and tab1.id = tab2.idFigure SEQ Figure \* ARABIC 9: Query that forces the CBO to make a reasonable guessThe execution plans for those two queries, shown in REF _Ref377723207 \h Figure 10 and REF _Ref377723230 \h Figure 11, are very similar. They have the same sequence of operations, the same type of joins and same estimated cardinalities. | Id | Operation | Name | Rows | Bytes | Cost | Time |--------------------------------------+-----------------------------------+| 0 | SELECT STATEMENT | | | | 38K | || 1 | HASH JOIN | | 488K | 21M | 38K | 00:08:49 || 2 | TABLE ACCESS FULL | TAB1 | 488K | 11M | 11K | 00:02:20 || 3 | TABLE ACCESS FULL | TAB2 | 9766K | 210M | 10K | 00:02:06 |--------------------------------------+-----------------------------------+| Id | Operation | Name | Rows | Bytes | Cost | Time |--------------------------------------+-----------------------------------+| 0 | SELECT STATEMENT | | | | 38K | || 1 | HASH JOIN | | 488K | 21M | 38K | 00:08:49 || 2 | TABLE ACCESS FULL | TAB1 | 488K | 11M | 11K | 00:02:20 || 3 | TABLE ACCESS FULL | TAB2 | 9766K | 210M | 10K | 00:02:06 |--------------------------------------+-----------------------------------+Figure SEQ Figure \* ARABIC 10: Execution plan for query that forced CBO to make a wild guess| Id | Operation | Name | Rows | Bytes | Cost | Time |--------------------------------------+-----------------------------------+| 0 | SELECT STATEMENT | | | | 38K | || 1 | HASH JOIN | | 488K | 15M | 38K | 00:08:45 || 2 | TABLE ACCESS FULL | TAB1 | 488K | 4395K | 11K | 00:02:20 || 3 | TABLE ACCESS FULL | TAB2 | 9766K | 210M | 10K | 00:02:06 |--------------------------------------+-----------------------------------+| Id | Operation | Name | Rows | Bytes | Cost | Time |--------------------------------------+-----------------------------------+| 0 | SELECT STATEMENT | | | | 38K | || 1 | HASH JOIN | | 488K | 15M | 38K | 00:08:45 || 2 | TABLE ACCESS FULL | TAB1 | 488K | 4395K | 11K | 00:02:20 || 3 | TABLE ACCESS FULL | TAB2 | 9766K | 210M | 10K | 00:02:06 |--------------------------------------+-----------------------------------+Figure SEQ Figure \* ARABIC 11:Execution plan for query that forced CBO to make a reasonable guessThere is only a small difference in the number of estimated bytes. Nothing in the execution plan or the 10053 trace, as far as I can see, indicates that the Oracle CBO computed or used any information related to the level of guesswork it had to employ in the query. The SQL Tuning Advisor may issue some recommendations that are indirectly related to cardinality confidence.Even though Oracle CBO does not appear to take confidence of cardinality into account when making its calculations, it could trigger dynamic sampling when it encounters predicates that require guesswork.Table SEQ Table \* ARABIC 1: Dynamic sampling levels 1-10 REF _Ref377723356 \h Table 1 shows a brief description of dynamic sampling levels 1 to 10. Dynamic sampling levels 3 and up can trigger dynamic sampling for predicates that CBO believes it cannot make good cardinality estimates otherwise. In general, dynamic sampling is a powerful, but not universal way of dealing with low confidence cardinality estimates. Current State, Oracle 12cOracle 12c, the latest version of the database, has a few news features. Dynamic sampling got an existing new option, the all-encompassing AUTO switch. A summary of the new sampling level is described in REF _Ref377729512 \h Table 2.Table SEQ Table \* ARABIC 2: Dynamic sampling level11 Ideally, this should be able to detect the confidence of cardinality estimates and act accordingly. Since I was not able to find any documentation about the algorithm used to determine when to trigger dynamic sampling for level 11, I ran my own tests. I created a simple table with a primary key and inserted 1000 records.Table SEQ Table \* ARABIC 3:Structure of TAB2Next, I set dynamic sampling level to 11 and executed a simple select by primary key.alter session set optimizer_dynamic_sampling = 11 ;Select * from tab3 where id = 123 ; ....Note----- - dynamic statistics used: dynamic sampling (level=AUTO)To my surprise, Oracle decided to trigger dynamic sampling for this query, even though it is clear that the size of the output is either one or zero. It seems that the new AUTO option does not rely on confidence of cardinality estimates and triggers dynamic sampling even when there is no real need for that. Now, let review another important practical aspect of dynamic sampling - the resources it consumes. REF _Ref377723230 \h Figure 11 shows some basic statistics for the lookup query. Figure SEQ Figure \* ARABIC 12: Statistics for difference DS levelsThe resources required by the new AUTO dynamic sampling are excessive in this simple case. Even though this observation should not be generalized, one needs to conduct thorough testing before setting dynamic sampling to AUTO at system level.Adaptive Execution Plans is another new Oracle 12c feature. The exciting new feature allows the Oracle CBO to make runtime adjustments to execution plans. When a join a marked as adaptive, Oracle defers the decision whether to use Nested Loop Join or Hash Join until run time. During run time, Oracle looks at the actual row counts and proceeds with the best join method. While detailed understanding of how Adaptive Plans work is beyond the scope of the paper, REF _Ref377729890 \h Figure 13 may give some basic ideas about this feature. In essence, Oracle resorts to Adaptive Plans when it is not certain which type of join would be best. If at SQL parse time, the Oracle CBO estimated that one of the sets to join is “significantly” smaller the other, where “significantly” is defined internally by the CBO, and there are appropriate indexes, then Oracle would opt for Nested Loops. If one of the sets is only “slightly” smaller than the other, where “slightly” is defined internally by the CBO, then the performance of the two standard join types would be similar, so Oracle would typically decide to go with an Adaptive Plan and postpone the decision until run time. Finally, when the two sets have “similar” sizes, where “similar” is defined internally by the CBO, then Oracle would go with Hash join. Figure SEQ Figure \* ARABIC 13:Adaptive execution plansCardinality miscalculations, frequently associated with low confidence cardinality estimates, manifest themselves in different types of performance problems. Once of the most common problem is selecting an improper join type. Therefore, Adaptive Plans could be very useful in mitigating the ill-effects of low cardinality confidence. Now, let’s see if Oracle CBO’s decision to use Adaptive Plans is influenced by the confidence of the cardinality estimates.To find out if Oracle CBO takes into account the level of guessing, we’ll review how the behavior of a query Q1 that forces significant guessing ( REF _Ref377723020 \h Figure 8) differs from the behavior of a query Q2 that makes reasonable assumptions ( REF _Ref377723118 \h Figure 9). During the test, as illustrated in REF _Ref377730065 \h Figure 14:Testing behavior of Q1 and Q2, we keep table TAB2 unchanged, while we grow table TAB1.Figure SEQ Figure \* ARABIC 14:Testing behavior of Q1 and Q2As we grow table TAB1, we look at the type of join used by queries Q1 and Q2.Figure SEQ Figure \* ARABIC 15:Result of test- switching from NLJ to Adaptive and from Adaptive to HJFigure shows the result of the experiment. The execution plans for both queries Q1 and Q2 switch from Nested Loop to Adaptive Plan at the exact same size of table TAB1. Similarly, the execution plans for both queries Q1 and Q2 switch from Adaptive Plan to Hash Plan at the exact same size of table TAB1. Therefore, it seems that Oracle CBO’s decision to use Adaptive Plans is not based on the confidence of cardinality estimates. An attempt to measure To model the confidence of Oracle’s cardinality estimates, I created the XPLAN_CONFIDENCE package. It measures the maximum relative error (e+R) for every step in the execution plan. The output is a continuous variable, which would enable us to gain good inside into the strengths and the limitations of the Oracle CBO. The package has multiple limitations and is intended for demonstration purposes only. I accept no liability or responsibility for XPLAN_CONFIDENCE package, nor would I be able to support it. Use it at your own risk! The package uses only very basic factors and ignores many important ones. It does not recognize sub-queries, inline views and other “complex” structures. It has limited ability to parse filters and properly handle built-in and custom PL/SQL functions. It is not aware of profiles, dynamic sampling, adaptive cursor sharing and many other features. It is not compatible with oracle 12c. It is not aware of materialized views and query rewrite. It also has very limited ability to handle extended statistics. In short, the package, and the model it represents, is very far from accurate and complete. REF _Ref347310012 \h REF _Ref377730227 \h Figure 16 shows the package in action. It is invoked similarly to DBMS_XPLAN.DISPLAY_CURSOR function. The output is also similar, but with one additional column – “Max. Error”. That column measures the maximum cardinality error at every execution plan step, a proxy for CBO confidence in its estimate at that step. It is assumed that the statistics completely represent the underlying data, so the package assigns zero cardinality error for full table scans without filters. The errors start when filters and predicates are applied.Figure SEQ Figure \* ARABIC 16:Sample run of XPLAN_CONFIDENCEThe package uses V$SQL_PLAN to get the execution plan and the filters, so a SQL statements must be present there. The package works best when executed through SQL*Developer. The definition of the package is shown in REF _Ref377730281 \h Figure 17.Figure SEQ Figure \* ARABIC 17: Specification of XPLAN_CONFIDENCE REF _Ref377730456 \h Figure 18 gives an overview of the architecture of the package. The first step is parsing the execution plan from V$SQL_PLAN. At mentioned, the ability to handle complex statements is very limited, so unless the filters fall under small number of predefined templates, they are classified as complex and not parsed further. After that, a procedure that computes the maximum relative errors is invoked recursively. After all calculations are done, the execution plan in text form is retrieved using DBMS_XPLAN.DISPLAY_CURSOR function. The plan is then parsed and the maximum error information is appended to the relevant lines.Figure SEQ Figure \* ARABIC 18: XPLAN_CONFIDENCE architectureThe maximum error computation algorithm is based on the formulas discussed in the previous section. We would also need to assign specific maximum errors for most of the common filters in SQL. REF _Ref377730525 \h Table 4 shows the specific numbers I used in the package. Predicate/ DB structuresComplexBind/VariableHistogramsAssigned max. errorDS3Opportunities for improving CBO’s confidence =Y20%N/ASubstitute with simple predicates NY5%NConsider literals (be aware of parsing implications) NY1%NN5%NConsider histograms if appropriate >Y40%N/ASubstitute with simple predicates NY10%NConsider literals (be aware of parsing implications) NY1%NN10%NConsider histograms if appropriate LIKE200%YForce dynamic sampling MEMBER OF200%NIN predicate, if number of records is low.Store records in DB table; make sure table stats are available Unique Index 0%Extended Statistics Columns 5%A very effective way to deal with correlated columns as well as large number of filter conditions Table SEQ Table \* ARABIC 4:Maximum relative errorsIt is important to note that assigned maximum error numbers are purely subjective and are not a result of a scientific study. That is not that big of a problem because the purpose of the package is not to forecast, but rather to show general trends. We assume that we get the smallest error when a unique key is used. The next best thing is using histograms and literals. Using binds or not having histograms is assumed to give slightly higher maximum error. Extended statistics can also help reduce errors; they are assigned 5% maximum error, but the underlying predicates are not counted. Complex structures, everything that the simple parser could not recognize, are assigned a bigger error. Since the CBO has difficult time getting sound selectivity coefficients from LIKE and MEMBER OF, they are assigned 200% maximum error. The last column in the table provides guidance on how we can modify our code and database structures to minimize the guesses CBO has to make. XPLAN_CONFIDENCE has only limited capabilities, so it does not take into account many important SQL constructs. REF _Ref347323067 \h REF _Ref377730608 \h Table 5 shows some important constructs that are not included in XPLAN_CONFIDENCE, but are challenging for the CBO. Predicate Opportunities for improving CBO’s confidence PL/SQL functionsForce dynamic samplingUtilize ASSOCIATE STATISTICSPipeline functionsForce dynamic sampling (DYNAMIC_SAMPLING hint, version 11.1.0.7 +)Utilize ASSOCIATE STATISTICSCONNECT BY LEVEL < Table SEQ Table \* ARABIC 5: Constructs not included in the modelEven though integrating SQL and PL/SQL opens up many interesting design options, we should be aware of how CBO costs PL/SQL functions, and what we can do to supply more information to the optimizer (Senegacnik, 2010). Pipeline functions are also a very useful feature, but they too force the CBO to make unsubstantiated assumptions (Billington, 2009).Practical applicationsA Proactive Design ApproachIt is very important to understand how the Oracle CBO computes the selectivity and cardinality for any SQL predicate or structure we intend to use. Due to simplicity and prevalent use, common predicates are almost always optimally processed by the CBO, that is, the CBO makes only the absolutely necessary guesses regarding their selectivity. New or complex structures might present a challenge to the CBO. CBO would not complain or warn about it, except when the SQL Tuning Adviser is invoked, but would rather make blanket assumptions and move on. I believe, it is the responsibility of the database administrator/architect to vet all new SQL features and uses and see how well the CBO can handle them. The vetting process should include running the SQL with the new feature with different inputs. Does the execution plan change as you change the size of any of the inputs? A look at the explain plan usually suffices, but sometimes reviewing 10053 trace is needed. If the default CBO behavior is not satisfactory, it is possible to supply the CBO with more information? How difficult would that process be?Normal Confidence Deterioration REF _Ref377730678 \h Figure 19 shows an execution plan of a typical query. Each line is colored in a shade of red, where the brightness of the color is proportional to the maximum cardinality error for the respective step. We can see a slow and steady deterioration of CBO’s confidence in the size of the result set. Those findings are in line with the observations in REF _Ref347241335 \h Figure 9. The more tables are involved in a query, the higher the maximum cardinality error, the higher the chance of a suboptimal plan. That is because most SQL constructs either pass or amplify the cardinality errors from the previous steps. Few constructs, such as “col in(select max(col1) from subq)”, reduce cardinality errors. That is, the errors accumulated in subq are not passed on to the next step. XPLAN_CONFIDENCE does not recognize any such construct at this time. Figure SEQ Figure \* ARABIC 19: Normal confidence deteriorationQueries that include large number of tables may get a suboptimal execution plan for reasons explained in Metalink Notes 212809.1 and 73489.1 (MOS 2011)(MOS 2004). In theory, for a query of n joins, the CBO need to review (n!) possible permutations and the number of permutation grows really fast. For instance, (13!) is 6 billion, while (14!) is 87 billion. Therefore, the CBO has to abandon some permutations, possibly missing good plans. Faster CPU and improvements in the CBO, such as heuristics to reduce the search space, allow for more permutations to be reviewed. On the other hand, increased complexity of computing cost restricts that number of permutations to be reviewed. A sensible solution to this problem is to either avoid or logically split large SQLs, so the issues related with confidence deterioration and exponential growth of possible execution plans are minimized. REF _Ref377730732 \h Figure 20 illustrates that point. The higher the maximum error of a block, the higher chance the CBO will choose a suboptimal plan at a consecutive step. The query on the left has more blocks with high maximum error than the query on the right. Please note that the top block does not count because since it is the last step, the errors there would not affect the execution plan. Figure SEQ Figure \* ARABIC 20: Logically "splitting" a query REF _Ref377730785 \h Figure 21 shows how to use hints to achieve a logical split. The logical split is not always possible, and when it is, it is usually not as simple as shown here. Figure SEQ Figure \* ARABIC 21:Logically "splitting" a query using a NO_MERGE hintThe recommendation here does not contradict the well established policy of consolidating logic into a single SQL (Kyte 2007), but it is rather a caveat for queries with more than 10-15 tables. Rapid Confidence Deterioration REF _Ref377730939 \h Figure 22 shows a query with a different cardinality confidence pattern. The level of confidence for most of the steps is significantly lower compared to the previous query. There are significant jumps of the maximum relative error at step 18 and 31. This behavior illustrates that the number of joins is only one factor, usually not the most important, for confidence degradation. The complexity of the filters and predicates we use is the most important reason for suboptimal execution plans. Figure SEQ Figure \* ARABIC 22:Rapid confidence deterioration REF _Ref377730988 \h Figure 23 shows the filters that cause the biggest increase in maximum error for the query. The filter for step 31 contains two constructs that require the CBO to make a wild guess – MEMBER OF and LIKE. Step 18 contains SQL functions that are classified as complex by XPLAN_CONFIDENCE. Even though the filter does not have a high-error clause, the large number of mid-error clauses still decreases the cardinality confidence significantly. Figure SEQ Figure \* ARABIC 23: Problem filtersFortunately, there are number of ways to deal with clauses that are challenging the Oracle CBO.Dynamic sampling is a well established technique for gathering ad-hoc statistics. By default, it is triggered when objects do not have proper statistics. It also can be used to get the correct cardinalities when the filter clause would have forced the CBO to guess. This can be done by either increasing the dynamic sample level or by forcing dynamic sampling on a specific table using hints. Oracle 11g release 2 could trigger dynamic sampling without explicit instructions if it decides it would be beneficial (MOS 2012b).Another strategy is to provide as much information as possible about a predicate. Extended statistics and virtual columns are simple and effective ways to do that. ASSOCIATE STATISTICS is valuable mechanism for supplying statistics for PL/SQL and pipelined functions (Senegacnik 2010) (Billington 2009), but it requires more effort. Replacing the offending filter clause with semantically identical filter clause that uses simpler predicates is also a great option. For instance the following clauseand col1 <= nvl(col2,to_timestamp( '12-31-9999','mm-dd-yyyy'))that uses a “complex” predicate can be replaced with this simple clauseand (col1 <= col2 or col2 is NULL)The CBO can get the selectivity of simple predicates, such as <= and is NULL, with a small margin of error, but it has to guess the selectivity of most SQL functions.In a query where there is at least one selective predicate, in addition to the “problem predicates, we can push the “problem” predicates as late in the execution plan as possible. That way, the damage they would do would be minimal. REF _Ref347327618 \h REF _Ref377731054 \h Figure 24 illustrates that point. By moving block B to the end of the execution, the CBO can work with minimal errors almost until the very end of the execution plan, greatly reducing the chance of generating a sub-optimal plan.Figure SEQ Figure \* ARABIC 24:Pushing the problem block/predicate REF _Ref377731131 \h Figure 25 shows an example of such a query. In addition to the “LIKE” predicates on table A that require lots of guesswork, it also has a selective predicate on table F. Figure SEQ Figure \* ARABIC 25:Pushing the problem block/predicate - example queryWe can fix this query with adding a LEADING hint that would force the “problem” predicates to be at the end of the execution plan. Figure show how the distrubution of the maximum error for the original query and the optimized one. The original query maintains high maximum error thoughout the query, while ther optimized query has low maximum error until the last step. The error in the last step generelly does not matter because no actions depend on it.Figure SEQ Figure \* ARABIC 26:Pushing the problem block/predicate - max. relative errorIn REF _Ref377731203 \h Figure 26 we can also see the difference between the estimated and actual number of records for two queries. The original query has a huge discrepancy between the estimated number and the actual number of records, which is a result of a failed guess. The estimated number and the actual number of records for the optimized query are quite close, because the Oracle CBO did not have to make guesses.Dynamic sampling is a powerful method for dealing with predicates whose selectivity cannot be reliable assessed. It is the preferred solution for queries that lack selective predicates other than the “problem” predicates. REF _Ref377731287 \h Figure 27 illustrates the approach. Running dynamic sampling on table A and its predicates eliminates any guesswork, so the cardinality estimates after that are spot on. Figure SEQ Figure \* ARABIC 27:Dynamic sampling REF _Ref377731330 \h Figure 28shows an example of such a query. Except for the “LIKE” predicates on table A, there are no other selective predicates.Figure SEQ Figure \* ARABIC 28:Dynamic sampling - exampleWe can fix the query with a DYNAMIC_SAMPLING hint. While the dynamic sampling process would consume some resources, the benefits it would bring in terms of correct cardinality estimates, and consequently a better execution plan, are well worth it in this situation. Figure SEQ Figure \* ARABIC 29:Dynamic sampling - execution planIn REF _Ref377731396 \h Figure 29 we can see that the discrepancy between actual and estimated row counts is significantly larger in the original query compared to the optimized one. The last option is splitting the problem SQL into two or more SQL statements. That is a radical option that has its drawbacks. The major one is that we need to guarantee that all SQLs work on data at the same point in time, something that is automatic when only one SQL statement is evolved. If we are to split a query, we should do it immediately after a filter with high error is applied. Also, the statistics for the intermediate table must be gathered, either explicitly or via dynamic sampling. REF _Ref347389985 \h REF _Ref347389985 \h Figure 30 shows an example of splitting a table. By creating an explicit temporary result and gathering statistics on it, we make sure that the cardinality error stays in check.Figure SEQ Figure \* ARABIC 30: Splitting a queryConclusionAsk not what the optimizer can do for you - ask what you can do for the optimizer… The Oracle CBO is responsible for generating an execution plan for every syntactically valid SQL statement. It is a very difficult, yet extremely important job. It is critical to us to understand, at least at high level, how the optimizer works. We need to know what structures and predicates are reliably handled by the CBO and what structures and predicates confuse it. Armed with that knowledge, we need to provide the best environment for it to succeed. We can get the best results from the CBO by supplying it with as much relevant information as possible, limiting use of structures and predicates that force it to make unnecessary guesses and mitigating the effects of the inevitable guesses it has to make. Huge SQL statements are not the solution to our problem, huge SQL statements are the problem... We should be aware that the more tables get involved in a SQL statement, the higher the chance of a suboptimal plan. While for most cases the recommendation to issue as few SQL statements as possible is still valid, there are situations where we need to help the CBO with the inevitable burdens of processing large statements.Cool new features – trust, but verify… As an industry leader, the Oracle database regularly introduces new features. As great and innovative as those features are, they need to be vetted by a DBA to ensure that the Oracle CBO would handle them without issues.ReferencesBAAG (n.d). BAAG party - battle against any guess. Retrieved from , C (2009). Can We Speak Confidentially? Exposing Explain Confidence Levels. Retrieved from A., (2009). Setting cardinality for pipelined and table functions. Retrieved from , W. (n.d.). Tuning by cardinality feedback method and examples. Retrieved from Y. And S. Christodoulakis (1991), On the propagation of errors in the size of join results. ACM SIGMOD '91. Retrieved from , I. (2012). Advanced Methods for Managing Statistics of Volatile Tables in Oracle. Hotsos 2012. Retrieved from Kyte, T. (2007). On Cursors, SQL, and Analytics. Retrieved from, J. (2006). Cost-Based Oracle Fundamentals. New York, NY:APRESSLewis, J. (2012). Oracle Discussion Forums. Retrieved from , J. (2012). Oracle Discussion Forums. Retrieved from Oracle Support. (2012 a). Cost Based Optimizer (CBO) Overview [ID 10626.1].Retrieved from Oracle Support. (2012 b).Different Level for Dynamic Sampling used than the Specified Level [ID 1102413.1].Retrieved from Oracle Support. (2011). Limitations of the Oracle Cost Based Optimizer [ID 212809.1].Retrieved from Oracle Support. (2004). Affect of Number of Tables on Join Order Permutations [ID 73489.1] Retrieved from , J. (2010) Expert Oracle Practices , New York, NY , APRESS ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download