11 Replies Latest reply: Mar 19, 2013 6:31 PM by Cherif bh RSS

    Hidden parameters appears after Oracle 11gR2 upgrade

    Sivaprasad S
      Platform: HP-UX B.11.31 U ia64

      Upgrading from: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

      Upgrading to : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      Issue : There are around 130 hidden parameter appear after Oracle 11gR2 upgrade.
      Before upgrade I have commented the hidden parameters on the PFILE. PFILE only to start the database for the Oracle 11gR2 upgrade.


      Before upgrade, checking the hidden parameter:

      SQL> SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
      optimizerextended_cursor_sharing_rel
      optimizer extended cursor sharing for relational operators

      allowlevel_without_connect_by
      allow level without connect by

      I am using client parameter file ( PFILE ) commented the hidden parameters highlighted above.


      However after successful upgrade to 11gR2, creating SPFILE from the PFILE ( commented the hidden parameters) now i am getting 132 hidden parameters.

      SQL> select count(1) from sys.V$PARAMETER where name like '\_%' escape '\';

      COUNT(1)
      ----------
      132

      SQL> select name from sys.V$PARAMETER where name like '\_%' escape '\';

      name
      ----------
      aggregationoptimization_settings
      alwaysanti_join
      alwayssemi_join
      andpruning_enabled
      btree_bitmap_plans
      bloomfilter_enabled
      bloomfolding_enabled
      bloompruning_enabled
      complexview_merging
      compressioncompatibility
      connectby_use_union_all
      convertset_to_join
      costequality_semi_join
      cputo_io
      dimensionskip_null
      eliminatecommon_subexpr
      enabletype_dep_selectivity
      fastfull_scan_enabled
      firstk_rows_dynamic_proration
      gbyhash_aggregation_enabled
      generalizedpruning_enabled
      globalindexpnum_filter_enabled
      gsanti_semi_join_allowed
      improvedouterjoin_card
      improvedrow_length_enabled
      indexjoin_enabled
      ksbrestart_policy_times
      leftnested_loops_random
      localcommunication_costing_enabled
      minimalstats_aggregation
      mmvquery_rewrite_enabled
      newinitial_join_orders
      newsort_cost_estimate
      nljbatching_enabled
      optimadjust_for_part_skews
      optimenhance_nnull_detection
      optimnew_default_join_sel
      optimpeek_user_binds
      optimizeradaptive_cursor_sharing
      optimizerbetter_inlist_costing
      optimizercbqt_no_size_restriction
      optimizercoalesce_subqueries
      optimizercomplex_pred_selectivity
      optimizercompute_index_stats
      optimizerconnect_by_combine_sw
      optimizerconnect_by_cost_based
      optimizerconnect_by_elim_dups
      optimizercorrect_sq_selectivity
      optimizercost_based_transformation
      optimizercost_hjsmj_multimatch
      optimizercost_model
      optimizerdim_subq_join_sel
      optimizerdistinct_agg_transform
      optimizerdistinct_elimination
      optimizerdistinct_placement
      optimizereliminate_filtering_join
      optimizerenable_density_improvements
      optimizerenable_extended_stats
      optimizerenable_table_lookup_by_nl
      optimizerenhanced_filter_push
      optimizerextend_jppd_view_types
      optimizerextended_cursor_sharing
      optimizerextended_cursor_sharing_rel
      optimizerextended_stats_usage_control
      optimizerfalse_filter_pred_pullup
      optimizerfast_access_pred_analysis
      optimizerfast_pred_transitivity
      optimizerfilter_pred_pullup
      optimizerfkr_index_cost_bias
      optimizerfull_outer_join_to_outer
      optimizergroup_by_placement
      optimizerimprove_selectivity
      optimizerinterleave_jppd
      optimizerjoin_elimination_enabled
      optimizerjoin_factorization
      optimizerjoin_order_control
      optimizerjoin_sel_sanity_check
      optimizermax_permutations
      optimizermode_force
      optimizermulti_level_push_pred
      optimizernative_full_outer_join
      optimizernew_join_card_computation
      optimizernull_aware_antijoin
      optimizeror_expansion
      optimizerorder_by_elimination_enabled
      optimizerouter_join_to_inner
      optimizerouter_to_anti_enabled
      optimizerpush_down_distinct
      optimizerpush_pred_cost_based
      optimizerrownum_bind_default
      optimizerrownum_pred_based_fkr
      optimizerskip_scan_enabled
      optimizersortmerge_join_inequality
      optimizersqu_bottomup
      optimizerstar_tran_in_with_clause
      optimizersystem_stats_usage
      optimizertable_expansion
      optimizertransitivity_retain
      optimizertry_st_before_jppd
      optimizerundo_cost_change
      optimizerunnest_corr_set_subq
      optimizerunnest_disjunctive_subq
      optimizeruse_cbqt_star_transformation
      optimizeruse_feedback
      orexpand_nvl_predicate
      orderednested_loop
      parallelbroadcast_enabled
      partitionview_enabled
      pivotimplementation_method
      prerewrite_push_pred
      predmove_around
      pushjoin_predicate
      pushjoin_union_view
      pushjoin_union_view2
      pxminus_intersect
      pxpartition_scan_enabled
      pxpwg_enabled
      pxual_serial_input
      queryrewrite_setopgrw_enable
      removeaggr_subquery
      replacevirtual_columns
      resourcemanager_plan
      rightouter_hash_enable
      selfjoinmv_duplicates
      sqlmodel_unfold_forloops
      sqltunecategory_parsed
      subquerypruning_enabled
      subquerypruning_mv_enabled
      tablescan_cost_plus_one
      unionrewrite_for_gs
      unnestsubquery
      usecolumn_stats_for_function