【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:

  >

       >   

   

    

   

• 
  <><> 
• 
    
• 
   

   

   

   

   

   

   
   
   

   

•  

• 
  <>