Friday, September 18, 2009

How to: get oracle hidden (underscore) parameters


Below sql statements list all the parameters including "optim", including these hidden parameters.

x.ksppinm name,
y.ksppstvl VALUE,
2, 'STRING',
'UNKNOWN') typ,
'FALSE', 'INIT.ORA') isdefault,
decode(bitand(ksppiflg / 256, 1),
'FALSE') isses_modifiable,
decode(bitand(ksppiflg / 65536, 3),
'FALSE') issys_modifiable,
decode(bitand(ksppstvf, 7),
'FALSE') is_modified,
decode(bitand(ksppstvf, 2),
'FALSE') is_adjusted,
ksppdesc description,
ksppstcmnt update_comment
FROM x$ksppi x,
x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%optim%';

Sample output in 11gR2

---------------------------------------- ------------ ------------------------------------------------------------
_enable_NUMA_optimization TRUE Enable NUMA specific optimizations
_db_file_optimizer_read_count 8 multiblock read count for regular clients
_db_noarch_disble_optim FALSE Image redo logging (NOARCHIVEMODE)
_disable_cell_optimized_backups FALSE disable cell optimized backups
_log_max_optimize_threads 128 maximum number of threads to which log scan optimization is

plsql_optimize_level 2 PL/SQL optimize level
_plsql_nvl_optimize FALSE PL/SQL NVL optimize
_optimizer_trace none optimizer trace parameter
object_cache_optimal_size 102400 optimal size of the user session's object cache in bytes
optimizer_features_enable optimizer plan compatibility parameter
optimizer_mode ALL_ROWS optimizer mode
_optimizer_mode_force TRUE force setting of optimizer mode for user recursive SQL also
_optimizer_null_aware_antijoin TRUE null-aware antijoin parameter
_optimizer_extended_cursor_sharing UDO optimizer extended cursor sharing
_optimizer_extended_cursor_sharing_rel SIMPLE optimizer extended cursor sharing for relational operators
_optimizer_adaptive_cursor_sharing TRUE optimizer adaptive cursor sharing
_optimizer_cost_model CHOOSE optimizer cost model
_optimizer_undo_cost_change optimizer undo cost change
_optimizer_system_stats_usage TRUE system statistics usage
_optimizer_cache_stats FALSE cost with cache statistics
_optimizer_unnest_all_subqueries TRUE enables unnesting of every type of subquery
_optimizer_push_down_distinct 0 push down distinct from query block to table
_optimizer_cost_based_transformation LINEAR enables cost-based query transformation
_optimizer_squ_bottomup TRUE enables unnesting of subquery in a bottom-up manner
_optimizer_cbqt_factor 50 cost factor for cost-based query transformation
_optimizer_skip_scan_enabled TRUE enable/disable index skip scan
_optimizer_join_sel_sanity_check TRUE enable/disable sanity check for multi-column join selectivit

_optim_enhance_nnull_detection TRUE TRUE to enable index [fast] full scan more often
_optimizer_max_permutations 2000 optimizer maximum join permutations per query block
optimizer_index_cost_adj 100 optimizer index cost adjustment
optimizer_index_caching 0 optimizer percent index caching
_disable_sample_io_optim FALSE disable row sampling IO optimization
_optimizer_adjust_for_nulls TRUE adjust selectivity for null values
_optimizer_degree 0 force the optimizer to use the same degree of parallelism
_optimizer_choose_permutation 0 force the optimizer to use the specified permutation
_optim_peek_user_binds TRUE enable peeking of user binds
_optim_new_default_join_sel TRUE improves the way default equijoin selectivity are computed
_optimizer_dyn_smp_blks 32 number of blocks for optimizer dynamic sampling
optimizer_dynamic_sampling 2 optimizer dynamic sampling
_optimizer_new_join_card_computation TRUE compute join cardinality using non-rounded input values
_optim_adjust_for_part_skews TRUE adjust stats for skews across partitions
_optimizer_compute_index_stats TRUE force index stats collection on index creation/rebuild
_optimizer_autostats_job TRUE enable/disable auto stats collection job
_optimizer_push_pred_cost_based TRUE use cost-based query transformation for push pred optimizati

_optimizer_extend_jppd_view_types TRUE join pred pushdown on group-by, distinct, semi-/anti-joined

_optimizer_filter_pred_pullup TRUE use cost-based flter predicate pull up transformation
_optimizer_connect_by_cost_based TRUE use cost-based transformation for connect by
_optimizer_connect_by_combine_sw TRUE combine no filtering connect by and start with
_optimizer_connect_by_elim_dups TRUE allow connect by to eliminate duplicates from input
_optimizer_mjc_enabled TRUE enable merge join cartesian
_optimizer_sortmerge_join_enabled TRUE enable/disable sort-merge join method
_optimizer_undo_changes FALSE undo changes to query optimizer
_optimizer_percent_parallel 101 optimizer percent parallel
_optimizer_search_limit 5 optimizer search limit
_optimizer_block_size 8192 standard block size used by optimizer
_optimizer_save_stats TRUE enable/disable saving old versions of optimizer stats
_optimizer_cost_filter_pred FALSE enables costing of filter predicates in IO cost model
_optimizer_correct_sq_selectivity TRUE force correct computation of subquery selectivity
_optim_dict_stats_at_db_cr_upg TRUE enable/disable dictionary stats gathering at db create/upgra

_optimizer_dim_subq_join_sel TRUE use join selectivity in choosing star transformation dimensi

_optimizer_disable_strans_sanity_checks 0 disable star transformation sanity checks
_optimizer_ignore_hints FALSE enables the embedded hints to be ignored
_optimizer_random_plan 0 optimizer seed value for random plans
_optimizer_ceil_cost TRUE CEIL cost in CBO
_optimizer_invalidation_period 18000 time window for invalidation of cursors of analyzed objects
_optimizer_join_order_control 3 controls the optimizer join order search algorithm
_optimizer_cartesian_enabled TRUE optimizer cartesian join enabled
_optimizer_starplan_enabled TRUE optimizer star plan enabled
_optimizer_join_elimination_enabled TRUE optimizer join elimination enabled
_drop_table_optimization_enabled TRUE reduce SGA memory use during drop of a partitioned table
_optimizer_multiple_cenv generate and run plans using several compilation environment

_optimizer_cost_hjsmj_multimatch TRUE add cost of generating result set when #rows per key > 1
_optimizer_transitivity_retain TRUE retain equi-join pred upon transitive equality pred generati

optimizer_secure_view_merging TRUE optimizer secure view merging and predicate pushdown/movearo

_optimizer_cbqt_no_size_restriction TRUE disable cost based transformation query size restriction
_windowfunc_optimization_settings 0 settings for window function optimizations
_truncate_optimization_enabled TRUE do truncate optimization if set to TRUE
_optimizer_enhanced_filter_push TRUE push filters before trying cost-based query transformation
_optimizer_rownum_pred_based_fkr TRUE enable the use of first K rows due to rownum predicate
_optimizer_better_inlist_costing ALL enable improved costing of index access using in-list(s)
_optimizer_self_induced_cache_cost FALSE account for self-induced caching
_optimizer_min_cache_blocks 10 set minimum cached blocks
_optimizer_or_expansion DEPTH control or expansion approach used
_optimizer_outer_to_anti_enabled TRUE Enable transformation of outer-join to anti-join if possible
_optimizer_order_by_elimination_enabled TRUE Eliminates order bys from views before query transformation
_optimizer_star_tran_in_with_clause TRUE enable/disable star transformation in with clause queries
_optimizer_sortmerge_join_inequality TRUE enable/disable sort-merge join using inequality predicates
_optimizer_complex_pred_selectivity TRUE enable selectivity estimation for builtin functions
_optimizer_aw_stats_enabled TRUE Enables statistcs on AW olap_table table function
_optimizer_skip_scan_guess FALSE consider index skip scan for predicates with guessed selecti

_optimizer_distinct_elimination TRUE Eliminates redundant SELECT DISTNCT's
_add_col_optim_enabled TRUE Allows new add column optimization
_optimizer_multi_level_push_pred TRUE consider join-predicate pushdown that requires multi-level p
ushdown to base table

_optimizer_group_by_placement TRUE consider group-by placement optimization
_optimizer_coalesce_subqueries TRUE consider coalescing of subqueries optimization
_optimizer_enable_density_improvements TRUE use improved density computation for selectivity estimation
_optimizer_rownum_bind_default 10 Default value to use for rownum bind
optimizer_use_pending_statistics FALSE Control whether to use optimizer pending statistics
_optimizer_improve_selectivity TRUE improve table and partial overlap join selectivity computati

_optimizer_aw_join_push_enabled TRUE Enables AW Join Push optimization
optimizer_capture_sql_plan_baselines FALSE automatic capture of SQL plan baselines for repeatable state

optimizer_use_sql_plan_baselines TRUE use of SQL plan baselines for captured sql statements
_optimizer_native_full_outer_join FORCE execute full outer join using native implementaion
_optimizer_enable_extended_stats TRUE use extended statistics for selectivity estimation
_optimizer_free_transformation_heap TRUE free transformation subheap after each transformation
_optimizer_use_subheap TRUE Enables physical optimizer subheap
_optimizer_or_expansion_subheap TRUE Use subheap for optimizer or-expansion
_optimizer_star_trans_min_cost 0 optimizer star transformation minimum cost
_optimizer_star_trans_min_ratio 0 optimizer star transformation minimum ratio
_optimizer_reuse_cost_annotations TRUE reuse cost annotations during cost-based query transformatio

_optimizer_fkr_index_cost_bias 10 Optimizer index bias over FTS/IFFS under first K rows mode
_optimizer_nested_rollup_for_gset 100 number of groups above which we use nested rollup exec for g

optimizer_use_invisible_indexes FALSE Usage of invisible indexes (TRUE/FALSE)
_optimizer_extended_stats_usage_control 224 controls the optimizer usage of extended stats
_optimizer_fast_pred_transitivity TRUE use fast algorithm to generate transitive predicates
_optimizer_fast_access_pred_analysis TRUE use fast algorithm to traverse predicates for physical optim

_optimizer_multiple_cenv_report result control what to report in trace file when run in multi-plan

_optimizer_instance_count 0 force the optimizer to use the specified number of instances
_parallel_optimization_phase_for_local FALSE parallel optimization phase when all slaves are local
_optimizer_unnest_disjunctive_subq TRUE Unnesting of disjunctive subqueries (TRUE/FALSE)
_optimizer_unnest_corr_set_subq TRUE Unnesting of correlated set subqueries (TRUE/FALSE)
_optimizer_distinct_agg_transform TRUE Transforms Distinct Aggregates to non-distinct aggregates
_aggregation_optimization_settings 0 settings for aggregation optimizations
_optimizer_eliminate_filtering_join TRUE optimizer filtering join elimination enabled
_optimizer_connect_by_cb_whr_only FALSE use cost-based transformation for whr clause in connect by
_optimizer_join_factorization TRUE use join factorization transformation
_optimizer_force_CBQT force CBQT transformation regardless of cost
_optimizer_use_cbqt_star_transformation FALSE use rewritten star transformation using cbqt framework
_optimizer_table_expansion TRUE consider table expansion transformation
_xsolapi_sql_optimize TRUE OLAP API enable optimization
_xsolapi_optimize_suppression TRUE OLAP API optimizes suppressions?

131 rows selected.