Optimizer is a mechanism which will make the execution plan of an sql statement.
RBO(Rule Based Optimizer) CBO(Cost Based Optimizer)
Basically the RBO used a set of rules to determine how to execute a query. If an index was available on a table the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query.
CBO uses statistics and math to make an educated guess at the lowest cost.
CBO processes multiple iterations of explain plans (called permutations). CBO picks the one with the overall lowest cost.
CBO. CBO uses statistics and maths to make an educated guess at the lowest cost.
CBO processes multiple iterations of explain plans (called permutations). CBO picks the one with the overall lowest cost.
Set the optimizer mode Collect the statistics of an object
RBO
The optimizer generates several valid physical query plans.All the physical query plans are potential execution plans.
The optimizer generates several physical query plans that are potential execution plans.
The optimizer then chooses among them by estimating the cost of each possible physical plan based on the table and index statistics available to it,and selecting the plan with the lowest estimated cost.
This evaluation of the possible physical query plans is called cost-based query optimization.
To find the optimal/best plan to execute our DML statements such as SELECT,INSERT,UPDATE and DELETE.Or acle uses CBO to help determine efficient methods to execute queries.
optimizer_mode - This is the most important optimizer parameter, and the default value of all_rows may not be right for online systems.
optimizer_index_cost_adj - This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the lower the cost of index access.
optimizer_index_caching - This parameter tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching affects the CBO's decision to use an index for a table join (nested loops) or to favor a full-table scan.
optimizer_dynamic_sampling - The optimizer_dynamic_sampling initialization parameter controls the number of blocks read by the dynamic sampling query. A value of 0 means dynamic sampling will not be performed. Increasing the value of the parameter results in more aggressive dynamic sampling, in terms of both the type of tables sampled (analyzed or un-analyzed) and the amount of I/O spent on sampling.
db_file_multiblock_read_count - When this parameter is set to a high value, the CBO recognizes that scattered (multiblock) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans.
hash_area_size - The setting for hash_area_size parameter governs the propensity of the CBO to favor hash joins over nested loop and sort merge table joins. Note restrictions when using pga_aggregate_target.
sort_area_size - The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over presorted index retrieval.
pga_aggregate_target - This value governs the SQL optimizers propensity to choose hash joins.
