Oracle Hinder Parameter _optimizer_cost_based_transformation by yvtong


									8/8/12                                                                 Document

          Optimizer Cost Based Query Transformation [ID 1082127.1]
         Modified: Nov 2, 2010       Type: BULLETIN          Status: PUBLISHED          Priority: 3

         In this Document
          Scope and Application
          Optimizer Cost Based Query Transformation
             Cost Based Query Transformation (CBQT)

         Applies to:

         Oracle Server - Enterprise Edition - Version: to - Release: 10.1 to 11.2
         Information in this document applies to any platform.


         Outlines what Cost Based Query Transformation is

         Scope and Application


         Optimizer Cost Based Query Transformation

         Cost Based Query Transformation (CBQT)

         CBQT is a technology that modifies queries and decides on the best modification based upon the relative cost of the
         various options. In this process it considers several semantically equivalent query forms, and chooses lowest cost

         Because each transformation requires a copy of the query to be made and then that copy needs to be costed,
         transformation is a relatively expensive operation in terms of memory usage and of CPU which may in turn increase the
         time taken for the optimizer to generate a plan.

         The scope of transformations has increased over the various Oracle versions and 11g attempts costed transformations
         on a long list of operations. Earlier versions used heuristic (or rule-based) transformations based upon the structural
         properties of the query but without regard to the costs of these operations. The assumption was that these
         transformations would always be more performant than the original, which is not necessarily the case.

         In 11g the following transformations are attempted as costed transformations (not exhaustive):

                Aggregate Subquery Elimination
                Common subexpression elimination
                Complex View Merging
                Filter predicate push-down in a view - Note:1082122.1
                Group by placement
                Intersect conversion into join
                Join Elimination
                Join predicate push down - Note:1082106.1
                Materialized view rewrite
                Minus conversion into anti-join
                Native Full Outer Join- Note:1082125.1
                OR expansion
                Order by Elimination                              1/2
8/8/12                                                                 Document

                Outer to inner join conversion
                Predicate move-around
                Star transformation
                Subquery unnesting
                       IN / EXISTS -> SEMI JOIN
                       NOT IN / NOT EXISTS -> ANTI JOIN
                       Null-Aware and Single Null-Aware ANTI JOIN - Note:1082123.1
                Transitive Predicates - Note:1082133.1

         The transformations are controlled overall by the parameter _OPTIMIZER_COST_BASED_TRANSFORMATION set to
         any of:
         "exhaustive", "iterative", "linear", "on", "off"
         giving some control over how much effort is given to costing various transformations.



         NOTE:567354.1 - Init.ora Parameter "_OPTIMIZER_COST_BASED_TRANSFORMATION" [Hidden] Reference Note                  2/2

To top