Oracle Corner

Before the cost optimizer, the optimizer used the heuristic rules to rewrite the query. Rule optimizer used to perform simple transformation: like indistinct merging of the sub-queries into the outer query, removing the redundant groups from views if influenced by the outer predicate and similar.

Cost optimizer compares the costs of different transformations and opts for the lowest one.
It makes more complex transformation. This is the list of the cost transformations introduced in 10g+ databases:

Sub-query unnesting

• Transformed as inline views
  

Join and predicate transformation

• Join predicate can be pushed into the view
• Groups can be pulled out of sub-query
• Group can be pushed into the sub-query
  
• Expensive predicates are pulled up
  
• (11g+) Early evaluation of the group by operators
  
• (11g+) Pull out common joins in similar UNION ALL

For more details, please read "Cost-Based Query Transformation in Oracle" by Rafi Ahmed, Allison Lee, Andrew Witkowski, Sinesh Das, Hong Su, Mohamed Zait and Thierry Cruanes

I was interested to find out which instance parameters will change if, on a 10g database, we set optimizer_features_enable=9.2.0 as opposed to the 10.2.0.x . As I couldn't find any such reference via Google, here it is:

Name	10.2	9.2
_bloom_filter_enabled	TRUE	FALSE
_dimension_skip_null	TRUE	FALSE
_gby_hash_aggregation_enabled	TRUE	FALSE
_local_communication_costing_enabled	TRUE	FALSE
_mmv_query_rewrite_enabled	TRUE	FALSE
_optimizer_better_inlist_costing	ALL	OFF
_optimizer_cbqt_no_size_restriction	TRUE	FALSE
_optimizer_complex_pred_selectivity	TRUE	FALSE
_optimizer_compute_index_stats	TRUE	FALSE
_optimizer_correct_sq_selectivity	TRUE	FALSE
_optimizer_cost_based_transformation	LINEAR	OFF
_optimizer_cost_hjsmj_multimatch	TRUE	FALSE
_optimizer_dim_subq_join_sel	TRUE	FALSE
_optimizer_enhanced_filter_push	TRUE	FALSE
_optimizer_extended_cursor_sharing	UDO	NONE
_optimizer_filter_pred_pullup	TRUE	FALSE
_optimizer_join_elimination_enabled	TRUE	FALSE
_optimizer_join_order_control	3	0
_optimizer_join_sel_sanity_check	TRUE	FALSE
_optimizer_or_expansion	DEPTH	BREADTH
_optimizer_order_by_elimination_enabled	TRUE	FALSE
_optimizer_outer_to_anti_enabled	TRUE	FALSE
_optimizer_push_pred_cost_based	TRUE	FALSE
_optimizer_rownum_pred_based_fkr	TRUE	FALSE
_optimizer_squ_bottomup	TRUE	FALSE
_optimizer_star_tran_in_with_clause	TRUE	FALSE
_optimizer_transitivity_retain	TRUE	FALSE
_optimizer_undo_cost_change	10.2.0.1	9.2.0
_partition_view_enabled	TRUE	FALSE
_push_join_union_view2	TRUE	FALSE
_px_pwg_enabled	TRUE	FALSE
_query_rewrite_setopgrw_enable	TRUE	FALSE
_remove_aggr_subquery	TRUE	FALSE
_right_outer_hash_enable	TRUE	FALSE
_selfjoin_mv_duplicates	TRUE	FALSE
_sql_model_unfold_forloops	RUN_TIME	COMPILE_TIME
optimizer_dynamic_sampling	2	1
optimizer_features_enable	10.2.0.1	9.2.0
query_rewrite_enabled	TRUE	FALSE
skip_unusable_indexes	TRUE	FALSE

Keywords: Oracle 10.2.0.1 "optimizer_features_enable=9.2.0" 10g "alter system set optimizer_features_enable=9.2.0" "alter session set optimizer_features_enable=9.2.0"