As you know I have just started my own blog and I realized that sometimes pretty simple things can be really handy to know. Some time ago I found out by accident that Oracle 11G database has a nice feature to find out the available underscore parameters in the pfile/spfile (there are more underscore parameters, but these are really dangerous).
=================== DISCLAIMER ===================
The usage of underscore parameters is officially prohibited. Underscore parameters should only be used after having instructions from Oracle Support. The usage from underscore parameters without having these instructions can lead to an unsupported Oracle environment. Also instability, crashes or even corruptions can be caused by usage of these parameters. So please use the information in this post with care.
================================================
These parameters can be easily obtained by giving the following command on the sql prompt as shown here:
[oracle@ghperfsuite ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 11.1.0.6.0 – Production on Sat Sep 13 05:55:42 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing optionsSQL> create pfile from memory;
File created.SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
By this statement a pfile is created in the dbs directory. The pfile contains all the set parameters including the underscore parameters (this is for 11.1.0.6 without any changed underscore parameter). See the file below:
[oracle@ghperfsuite ~]$ cd $ORACLE_HOME/dbs
[oracle@ghperfsuite dbs]$ cat initghperf.ora
# Oracle init.ora parameter file generated by instance ghperf on 10/06/2008 23:55:51
__db_cache_size=100M
__java_pool_size=12M
__large_pool_size=8M
__oracle_base=’/u01/app/oracle’ # ORACLE_BASE set from environment
__pga_aggregate_target=100M
__sga_target=304M
__shared_io_pool_size=0
__shared_pool_size=176M
__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’ # parsed sqltune_category
_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
audit_file_dest=’/u01/app/oracle/admin/ghperf/adump’
audit_trail=’DB’
compatible=’11.1.0.0.0′
control_files=’/u01/app/oracle/oradata/ghperf/control01.ctl’
control_files=’/u01/app/oracle/oradata/ghperf/control02.ctl’
control_files=’/u01/app/oracle/oradata/ghperf/control03.ctl’
core_dump_dest=’/u01/app/oracle/diag/rdbms/ghperf/ghperf/cdump’
db_block_size=8192
db_domain=’amis.local’
db_name=’ghperf’
db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
db_recovery_file_dest_size=2G
diagnostic_dest=’/u01/app/oracle’
dispatchers=’(PROTOCOL=TCP) (SERVICE=ghperfXDB)’
log_buffer=6165504 # log buffer update
open_cursors=300
optimizer_dynamic_sampling=2
optimizer_mode=’ALL_ROWS’
pga_aggregate_target=100M
plsql_warnings=’DISABLE:ALL’ # PL/SQL warnings at init.ora
processes=150
query_rewrite_enabled=’TRUE’
remote_login_passwordfile=’EXCLUSIVE’
resource_manager_plan=”
result_cache_max_size=1568K
sga_target=304M
shared_servers=5
skip_unusable_indexes=TRUE
undo_tablespace=’UNDOTBS1′
utl_file_dir=’/u01/stage/xxx/quap_nmon’
utl_file_dir=’/u01/stage/xxx/esap_nmon’
utl_file_dir=’/u01/stage/yyy/yyy100′
utl_file_dir=’/u01/stage/yyy/yyy300′
utl_file_dir=’/u01/stage/yyy/yyy200′
I hope this will from any use. I know from practice that it is sometimes very handy to have this kind of information.
I know a lot of people like to play with hidden parameters, forever in search of FAST=TRUE or BROKEN=FALSE. The reality is that 9 times out of 10, hidden parameters will only get you in trouble. In my opinion this post should be amended to warn users to NEVER, EVER mess with hidden parameters unless directed to do so by Oracle Support. What if some Jr DBA finds your blog and tries some of these parameters on their production instance? If you are going to play with hidden parameters, make sure it’s on a test system, such as a disposable virtual machine.
Hi Tyler, thanks for you comment. I personally think you are right with your remark regarding the fact that you shouldn’t use underscore parameters or only when Oracle Support is behind it. Although this is the case the above method makes it easy to spool any parameter (including underscore) on instance level, which has been set. Sometimes you encounter situations that parameters have been set you are not aware of. This is just a method to find these parameters and their settings. To make the post better I will put a disclaimer in the post.
It startled me how much hidden parameters are set nowadays…….
Hi Marco, yes that was something I was surprised of as well. The parameters spooled in the post are just the default set parameters for a 11.1.0.6 database installation. It might be interesting to further investigate what these default set underscore parameters are exactly tweaking. For now it looks like that most of them are influencing the cost based optimizer.