Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 


Oracle 11g hidden undocumented parameters

Oracle Tips by Burleson Consulting

November 20,  2011

 
Starting in Oracle 11g, we have a heap of new hidden initialization parameters, many of which control important new features such as the adaptive cursor sharing feature.

Key Hidden Parameters for Oracle

With the Oracle release, there are many new hidden parameters that maintain a great deal of the functionality for the myriad of new features that come with as shown below. For example, the following hidden parameters are new to Oracle and support many new features:

  • diag_adr_enabled
  • diag_adr_auto_purge
  • prop_old_enabled
  • diag_hm_rc_enabled

New Features for and Hidden Parameters

Every new feature within Oracle has a hidden and undocumented parameter associated with it that contains the critical settings for the internal operations of the functionality within the Oracle database. The Oracle Automatic Diagnostic Repository (ADR) contains the following key hidden parameters:

  • _diag_adr_test_param                 
  • _diag_adr_enabled
  • _diag_adr_auto_purge          

The diag_adr_test_param is a hidden parameter that serves as a testing parameter to verify operation of the ADR diagnostic features.

Another key hidden parameter for the ADR new feature with Oracle is diag_adr_enabled. This parameter either enables or disables the operation of the ADR feature.

Finally, there is the diag_adr_auto_purge parameter for Oracle which allows one to enable or disable the ADR Memory Monitorbackground process behavior with respect to the ADR operations.

Oracle Hidden Parameters for Data Recovery Advisor (DRA)

The Oracle Data Recovery Advisor (DRA) is an exciting new feature that is new to for backup and recovery functionality. The following hidden parameters perform the behind the scenes add-on functions as part of the DRA with Oracle :

  • dra_enable_offline_dictionary
  • dra_bmr_number_threshold
  • dra_bmr_percent_threshold

The hidden parameter dra_enable_offline_dictionaryenables the periodic creation for offline dictionary objects associated with the Data Recovery Advisor. The other two hidden parameters pertain to block media recovery(BMR) tasks as performed with database backup and recovery operations by the Data Recovery Advisor within Oracle. As for the hidden parameter dra_bmr_number_threshold, this parameter relates to the maximum amount of block media recovery tasks that can be performed on a file during recovery. The other hidden parameter for the Data Recovery Advisor, dra_bmr_percent_threshold, measures the largest percentage of blocks in a file during recovery tasks performed by the DRA for Oracle.      

Hang Detection Parameters for the Health Monitor (HM) System

As part of the self-healing and tuning new features for, Oracle has provided the health monitor (HM) suite of tools and hidden parameters listed next.

  • _hang_detection
  • _hang_resolution
  • _hm_analysis_output_disk
  • _hm_analysis_oradebug_node_dump_level
  • _hm_analysis_oradebug_sys_dump_level
  • _diag_hm_rc_enabled
  • _diag_hm_tc_enabled

The hidden parameter hang_detection provides for an interval to detect hang conditions as part of the health monitor tasks. For hang resolution, there is the hidden parameter hang_resolution.

The hm_analysis_output_disk is a hidden parameter which generates a return code of TRUE if the hang manager outputs hangs within the Oracle database. For Oradebug dump activity, there are also two hidden parameters. The first hidden parameter that coordinates Oradebug node dump level output for hang manager analysis is hm_analysis_oradebug_node_dump_level. 

Another hidden parameter for related tasks with conjunction to Oradebug system level traces is the hm_analysis_oradebug_sys_dump_level parameter which allows the hang manager to output system state level information with the Oradebug utility.

For the health monitor (HM) checks performed by the Oracle health checkers, there are a couple of new undocumented database parameters: diag_hm_rc_enabled and diag_hm_tc_enabled. The parameter diag_hm_rc_enabled performs the task of either enabling or disabling the health monitor checks for diagnosis of database health conditions. There is also a partner hidden parameter called diag_hm_tc_enabled that allows one to either enable or disable the test or dummy checks performed by the health monitor system within Oracle.

 
 
For more on 11g new features, see the book "Oracle 11g New Features" authored by John Garmany, with Oracle ACE's Steve Karam, Lutz Hartmann, V.J. Jain and Brian Carr.
 
Note:  Hidden and undocumented parameters are very powerful and should ONLY be set with the express consent of Oracle technical support. 
 
Here are the hidden initialization parameters in Oracle11g:
 
 
__db_cache_size=1000M
__java_pool_size=200M
__large_pool_size=16M
__oracle_base=’/u01/app/oracle’
__pga_aggregate_target=50M
__sga_target=3800M
__shared_io_pool_size=0
__shared_pool_size=60M
__streams_pool_size=0
_always_anti_join=’CHOOSE’
_always_semi_join=’CHOOSE’
_b_tree_bitmap_plans=TRUE
_bloom_filter_enabled=TRUE
_bloom_pruning_enabled=TRUE
_complex_view_merging=TRUE
_convert_set_to_join=FALSE
_cost_equality_semi_join=TRUE
_cpu_to_io=0
_dimension_skip_null=TRUE
_eliminate_common_subexpr=TRUE
_enable_type_dep_selectivity=TRUE
_fast_full_scan_enabled=TRUE
_first_k_rows_dynamic_proration=TRUE
_gby_hash_aggregation_enabled=TRUE
_generalized_pruning_enabled=TRUE
_globalindex_pnum_filter_enabled=TRUE
_gs_anti_semi_join_allowed=TRUE
_improved_outerjoin_card=TRUE
_improved_row_length_enabled=TRUE
_index_join_enabled=TRUE
_ksb_restart_policy_times=’0′
_ksb_restart_policy_times=’60′
_ksb_restart_policy_times=’120′
_ksb_restart_policy_times=’240′ # internal update to set default
_left_nested_loops_random=TRUE
_local_communication_costing_enabled=TRUE
_minimal_stats_aggregation=TRUE
_mmv_query_rewrite_enabled=TRUE
_new_initial_join_orders=TRUE
_new_sort_cost_estimate=TRUE
_nlj_batching_enabled=1
_optim_adjust_for_part_skews=TRUE
_optim_enhance_nnull_detection=TRUE
_optim_new_default_join_sel=TRUE
_optim_peek_user_binds=TRUE
_optimizer_adaptive_cursor_sharing=TRUE
_optimizer_better_inlist_costing=’ALL’
_optimizer_cbqt_no_size_restriction=TRUE
_optimizer_complex_pred_selectivity=TRUE
_optimizer_compute_index_stats=TRUE
_optimizer_connect_by_combine_sw=TRUE
_optimizer_connect_by_cost_based=TRUE
_optimizer_correct_sq_selectivity=TRUE
_optimizer_cost_based_transformation=’LINEAR’
_optimizer_cost_hjsmj_multimatch=TRUE
_optimizer_cost_model=’CHOOSE’
_optimizer_dim_subq_join_sel=TRUE
_optimizer_distinct_elimination=TRUE
_optimizer_enable_density_improvements=TRUE
_optimizer_enable_extended_stats=TRUE
_optimizer_enhanced_filter_push=TRUE
_optimizer_extend_jppd_view_types=TRUE
_optimizer_extended_cursor_sharing=’UDO’
_optimizer_extended_cursor_sharing_rel=’SIMPLE’
_optimizer_extended_stats_usage_control=240
_optimizer_filter_pred_pullup=TRUE
_optimizer_fkr_index_cost_bias=10
_optimizer_group_by_placement=TRUE
_optimizer_improve_selectivity=TRUE
_optimizer_join_elimination_enabled=TRUE
_optimizer_join_order_control=3
_optimizer_join_sel_sanity_check=TRUE
_optimizer_max_permutations=2000
_optimizer_mode_force=TRUE
_optimizer_multi_level_push_pred=TRUE
_optimizer_native_full_outer_join=’FORCE’
_optimizer_new_join_card_computation=TRUE
_optimizer_null_aware_antijoin=TRUE
_optimizer_or_expansion=’DEPTH’
_optimizer_order_by_elimination_enabled=TRUE
_optimizer_outer_to_anti_enabled=TRUE
_optimizer_push_down_distinct=0
_optimizer_push_pred_cost_based=TRUE
_optimizer_rownum_bind_default=10
_optimizer_rownum_pred_based_fkr=TRUE
_optimizer_skip_scan_enabled=TRUE
_optimizer_sortmerge_join_inequality=TRUE
_optimizer_squ_bottomup=TRUE
_optimizer_star_tran_in_with_clause=TRUE
_optimizer_system_stats_usage=TRUE
_optimizer_transitivity_retain=TRUE
_optimizer_undo_cost_change=’11.1.0.6′
_or_expand_nvl_predicate=TRUE
_ordered_nested_loop=TRUE
_parallel_broadcast_enabled=TRUE
_partition_view_enabled=TRUE
_pivot_implementation_method=’CHOOSE’
_pre_rewrite_push_pred=TRUE
_pred_move_around=TRUE
_push_join_predicate=TRUE
_push_join_union_view=TRUE
_push_join_union_view2=TRUE
_px_minus_intersect=TRUE
_px_pwg_enabled=TRUE
_px_ual_serial_input=TRUE
_query_rewrite_setopgrw_enable=TRUE
_remove_aggr_subquery=TRUE
_right_outer_hash_enable=TRUE
_selfjoin_mv_duplicates=TRUE
_sql_model_unfold_forloops=’RUN_TIME’
_sqltune_category_parsed=’DEFAULT’
_subquery_pruning_enabled=TRUE
_subquery_pruning_mv_enabled=FALSE
_table_scan_cost_plus_one=TRUE
_union_rewrite_for_gs=’YES_GSET_MVS’
_unnest_subquery=TRUE
_use_column_stats_for_function=TRUE


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.