【MOS】Limitations of the Oracle Cost Based Optimizer (文檔 ID 212809.1) 【MOS】Limitations of the Oracle Cost Based Optimizer (文檔 ID 212809.1)
【MOS】Limitations of the Oracle Cost Based Optimizer (文檔 ID 212809.1)
APPLIES TO:
Oracle Database - Personal Edition - Version 7.1.4.0 and later
Oracle Database - Enterprise Edition - Version 6.0.0.0 and later
Oracle Database - Standard Edition - Version 7.0.16.0 and later
Information in this document applies to any platform.
PURPOSE
This Document outlines some of the limitations that may be encountered when using the Cost Based Optimizer.
SCOPE
Cost Based Optimizer Limitations
The Cost Based Optimizer (CBO) uses a complex and comprehensive model to choose the plan with the lowest cost overall. In most cases the model picks the best access methods for accessing the data in the most efficient manner. However, even with computed statistics and column histograms it is possible for the Cost Based Optimizer to choose a sub-optimal plan. There are limitations to the Cost model that can affect queries in some circumstances. Some of these are listed and explained below:
DETAILS
Potential for incorrect estimation of intermediate result set cardinality
Cardinality is the CBO estimate of the number of rows produced by a row source or combination of row sources. In some cases, the cardinality of result sets can be miscalculated. This is most common with complex predicates where the statistics do not accurately reflect the data or where predicate values are correlated. The following is an illustration of a statement featuring correlated predicates:
select ename from emp where sal >= 25000 and job = 'PRESIDENT';
In this example there is a hidden correlation between the data values in the sal and job columns. Only the president earns more than $25,000. There are no employees with a "sal >= 25000" who are not 'PRESIDENT'. The optimizer has no way of determining that such a correlation exists and would treat the 2 columns as though their values were independent of each other. This would result in an underestimation of the selectivity of the predicates and thus an inaccurate cardinality estimate.
NOTE: 11g and above addresses this issue by providing the facility to create extended statistics in the form of column groups . See:
Note:452883.1 MultiColumn/Column Group Statistics Examples (Doc ID 452883.1)
This only addresses cases where there is a correlation between columns in the same table. Correlation in different tables is not resolved by this.
Assumption that all statements run standalone
Since the CBO assumes that all statements run standalone, it may underestimate the volume of data that has been cached as a result of other statements running previously or concurrently. This can result in significant over estimation of the cost of index accesses which can read from cached data rather than having to retrieve the data from disk. The parameters <Parameter:OPTIMIZER_INDEX_CACHING> and <Parameter:OPTIMIZER_INDEX_COST_ADJ> can be used to modify these costs to reflect the characteristics of the system in question.
Another example of the situation not taken into account by the optimizer is block contention, which occurs at times of heavy concurrent access to the same blocks from SQLs running in other sessions. Such contention may be seen for example as 'buffer busy waits' and/or 'latch free' waits for 'cache buffers chains' latches. At this stage of the CBO evolution it is not clear how the 'run time' database statistics can be accounted by the CBO and whether at all it may be useful.
Histogram bucket limitations
Histograms are limited to 254 buckets so if there are more than 254 distinct values and there is no single value that dominates the column's dataset then histograms may not provide helpful statistics. With large numbers of distinct values, histogram usage can be further impacted because differences in non popular values cannot be recorded. The choice of 254 buckets per column histogram was a balance between accuracy of the statistics and the speed of histogram collection/amount of space required to store the information.
As of 12c, the maximum possible number of buckets in a histogram increases from 254 to 2,048.
Limitations of Histogram on Character Columns
Histograms only store the first 32 characters of a character string (5 characters pre 8.1.6 See Bug:598799 ). If histograms are gathered on character columns that have data that is longer than 31 characters and the first 31 characters are identical then column histograms may not reflect the cardinality of these columns correctly as these values will all be treated as if they are identical. There are also similar limits with numeric data which is normalised to 15 digits in histogram endpoints.
As of 12c, the maximum possible number of characters considered for a histogram on a string column increases from thirty-two to sixty-four.
Bind Variables
Bind variables are recommended for situations where cursors are frequently executed with different column values and would otherwise cause shared pool fragmentation and contention.
Historically, the optimizer generated a single access path for an identical shared query featuring bind variables.
Prior to 9i, the optimizer did not know the value of the bind variable when the plan was determined which could result in a sub-optimal plan, since the 'average' statistic may not reflect the actuality of the data in all cases.
Since Bind variable peeking was introduced in Oracle 9i, the plan is based upon the first value that is bound to the variable. However, where column data is non-uniform a non-representative value can deliver inconsistent performance for queries using different bind variable values.
From the optimizer point of view, it is recommended to use literal values where there is the possibility of significant performance differences associated with using different bind variable values and applying the same plan for all executions. In these cases the choice of a good plan usually significantly outweighs the potential usage of shared pool space (assuming that the shared pool usage is not excessive). See:
Note:70075.1 Use of bind variables in queries
In later versions, features such as Adaptive Cursor Sharing and SQL Plan Management can be used to manage queries utilizing bind variables in a more flexible manner and make better decisions on what execution plans should and should not be shared. See:
Note:1359841.1 Plan Stability Features (Including SPM) Start Point
Note:1115994.1 Introduction to Adaptive Cursor Sharing concepts in 11G and mutimedia demo [Video]
Note:836256.1 Adaptive Cursor Sharing in 11G
Note:740052.1 Adaptive Cursor Sharing Overview
Subquery Unnesting and View Merging
Oracle exhaustively attempts to transform statements containing subqueries and views in to simpler statements. The goal of this activity is to make more access paths accessible and find the optimal plan. However, Subquery Unnesting and View Merging is a heuristic process. Subqueries are unnested and views are merged based upon a set of rules. No costs are generated and compared back to the unchanged statement. This could mean that the transformed statement does not perform as well as the untransformed one.
N.B. Oracle 10G introduces Costed Subquery Unnesting and View Merging which should go some way to alleviating the effects of this limitation
Join Permutations
The CBO evaluates each permutation of tables up to a predefined limit (<Parameter:optimizer_max_permutations>). As the number of tables involved in a query increases, so the total number of permutations increases. Soon the number that can be evaluated in a realistic timeframe is a minute proportion of the total. There are a large number of adjustments that occur within the optimization process to attempt to minimise the possibility of a good candidate being overlooked, but it is possible that the optimizer may not even consider the most optimal join order and method in large queries. The following article has more detail on optimizer permutations:
Note:73489.1 Affect of Number of Tables on Join Order Permutations
Potential workarounds for these issues
Workarounds for these issues typically include hinting the correct plan or using stored outlines. Enhancements to alleviate the affects of some of these issues are expected in future versions.
REFERENCES
NOTE:70075.1 - Use of Bind Variables in Queries (Pre-9i)
NOTE:73489.1 - Effect of Number of Tables on Join Order Permutations
NOTE:740052.1 - Adaptive Cursor Sharing: Overview
NOTE:836256.1 - Adaptive Cursor Sharing: Worked Example
BUG:598799 - INCORRECT CARDINALITY AFTER COMPUTING HISTOGRAMS
NOTE:1115994.1 - Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]
NOTE:1359841.1 - Master Note: Plan Stability Features (Including SQL Plan Management (SPM))
