Monday, May 23, 2011

Undocumented/Hidden Parameters in Oracle 11g

Oracle contains two type of initialization parameters i.e,

1.) Documented Initialization Parameter.
2.) Undocumented Initialization Parameter. 

Here, We will check about the  undocumented Initialization Parameter .

Undocumented Initialization parameter are also called hidden Parameter and there is no document available about these parameter . Every version of Oracle has special undocumented initialization parameters. These undocumented initialization parameters are usually only used in emergencies or with the express consent of Oracle technical support.

The X$KSPPI table contains all of the parameters that influence the oracle database in operation.  X$tables are available only when we connect to the oracle instance as the user 'SYS' .                               

The current version i.e, Oracle 11g has 2394 parameters, out of which 343 parameters are listed in the V$parameter view. This means that there are still 2051 parameters which are not listed in either V$views or Oracle Documentation . Hidden parameter starts with underscore sign  "_"  . To check all initialization  parameter fire below command.

SQL> select count(*) from X$KSPPI;
  COUNT(*)
----------
      2394

To check documented parameter fire the following comand .

SQL> select count(*) from v$parameter;
  COUNT(*)
----------
       343

Here we will discuss when to use hidden parameter , where to use them, and what tricks are involved in using them. Some issues involved with using these parameters are listed below :

1.) Undocumented parameters change from version to version (even in the same versions from patchset to patchset) without any notice. These parameters are not consistent across versions.

2.) All these parameters may not be available with all the ports. This means that they are not compatible across the operating systems. A few parameters are Operating System specific.

3.) Use of undocumented parameters without the knowledge of Oracle Support is a violation of the Support agreement with Oracle. Oracle Worldwide support has every right to refuse to support the databases affected by using these parameters.

4.) A few parameters can potentially corrupt the databases and some parameters within those few will help us in bringing up the corrupted database with minimal data loss. So these parameters are equally useful inspite of those dangers.

5.) Since no external documentation is available, it is important to test them before using these parameters in our production system. It is also suggested to have a complete backup before using these parameters.

We can obtain all the undocumented Parameter i.e, hidden Parameter by giving the following command .

SQL> select a.ksppinm name, b.ksppstvl value,b.ksppstdf deflt,
decode (a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type, a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where   a.indx = b.indx
   and
a.ksppinm like '\_%' escape '\'
order by name

We can also check the hidden and non-hidden parameter from memory by creating pfile .The command for creating the pfile is 
SQL> Create pfile='C:\pfile.ora'  from memory;

The above command  will not work in case of 10g or lower version of oracle. In my case the content of  pfile created from memory is as :

__db_cache_size=104M
__java_pool_size=4M
__large_pool_size=4M
__oracle_base='D:\oracle' # ORACLE_BASE set from environment
__pga_aggregate_target=132M
__sga_target=196M
__shared_io_pool_size=0
__shared_pool_size=76M
__streams_pool_size=0
_aggregation_optimization_settings=0
_always_anti_join='CHOOSE'
_always_semi_join='CHOOSE'
_and_pruning_enabled=TRUE
_b_tree_bitmap_plans=TRUE
_bloom_filter_enabled=TRUE
_bloom_folding_enabled=TRUE
_bloom_pruning_enabled=TRUE
_complex_view_merging=TRUE
_compression_compatibility='11.2.0.0.0'
_connect_by_use_union_all='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_coalesce_subqueries=TRUE
_optimizer_complex_pred_selectivity=TRUE
_optimizer_compute_index_stats=TRUE
_optimizer_connect_by_combine_sw=TRUE
_optimizer_connect_by_cost_based=TRUE
_optimizer_connect_by_elim_dups=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_agg_transform=TRUE
_optimizer_distinct_elimination=TRUE
_optimizer_distinct_placement=TRUE
_optimizer_eliminate_filtering_join=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=224
_optimizer_fast_access_pred_analysis=TRUE
_optimizer_fast_pred_transitivity=TRUE
_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_factorization=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_table_expansion=TRUE
_optimizer_transitivity_retain=TRUE
_optimizer_try_st_before_jppd=TRUE
_optimizer_undo_cost_change='11.2.0.1'
_optimizer_unnest_corr_set_subq=TRUE
_optimizer_unnest_disjunctive_subq=TRUE
_optimizer_use_cbqt_star_transformation=TRUE
_optimizer_use_feedback=TRUE
_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
_replace_virtual_columns=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
_system_trig_enabled=FALSE
_table_scan_cost_plus_one=TRUE
_undo_autotune=FALSE
_union_rewrite_for_gs='YES_GSET_MVS'
_unnest_subquery=TRUE
_use_column_stats_for_function=TRUE
aq_tm_processes=0
audit_file_dest='D:\ORACLE\ADMIN\INDIA\ADUMP'
audit_trail='DB'
compatible='11.2.0.0.0'
control_files='D:\ORACLE\ORADATA\INDIA\CONTROL01.CTL'
control_files='D:\ORACLE\FLASH_RECOVERY_AREA\INDIA\CONTROL02.CTL'
core_dump_dest='d:\oracle\diag\rdbms\india\india\cdump'
db_block_size=8192
db_domain=''
db_name='india'
db_recovery_file_dest='D:\oracle\flash_recovery_area'
db_recovery_file_dest_size=4977M
diagnostic_dest='D:\ORACLE'
dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'
enable_ddl_logging=FALSE
log_buffer=6029312 # log buffer update
memory_target=328M
open_cursors=300
optimizer_dynamic_sampling=2
optimizer_mode='ALL_ROWS'
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
processes=150
query_rewrite_enabled='TRUE'
recyclebin='OFF'
remote_login_passwordfile='EXCLUSIVE'
resource_manager_plan=''
result_cache_max_size=864K
skip_unusable_indexes=TRUE
undo_retention=900
undo_tablespace='UNDOTBS1' 




Enjoy   :-) 


No comments: