how to change a 11.2.0.1 database to a 11.1.0.7 database

PARAMETERS TO CHANGE 11.2.0.1 TO 11.1.0.7 [ID 1096377.1]

 

Applies to:

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

Purpose

This note shows 11.2.0.1 parameter settings to return optimizer back to 11.1.0.7

Questions and Answers

These are the parameters that are changed when setting optimizer_features_enable=11.1.0.7 in 11.2.0.1 database. These values represent OFE=11.1.0.7

alter session set "_optimizer_coalesce_subqueries" = false ;
alter session set "_optimizer_distinct_placement"       = false ;    
alter session set "_optimizer_unnest_disjunctive_subq"  = false  ;
alter session set "_optimizer_unnest_corr_set_subq"     = false;
alter session set "_optimizer_distinct_agg_transform"   = false;
alter session set "_optimizer_fast_pred_transitivity"   = false ;
alter session set "_optimizer_fast_access_pred_analysis" = false;
alter session set "_aggregation_optimization_settings"  = 32   ;
alter session set "_optimizer_eliminate_filtering_join" = false;
alter session set "_optimizer_join_factorization"       = false ;  
alter session set "_optimizer_use_cbqt_star_transformation" = false ;
alter session set "_optimizer_connect_by_elim_dups"     = false ;
alter session set "_connect_by_use_union_all"           = old_plan_mode;
alter session set "_optimizer_table_expansion"          = false;      
alter session set "_and_pruning_enabled"                = false ;    
alter session set "_optimizer_use_feedback"             = false ;    
alter session set "_optimizer_try_st_before_jppd"       = false ;
alter session set "_optimizer_undo_cost_change'         = '11.1.0.7' ;