Below sql statements list all the parameters including "optim", including these hidden parameters.
SELECT
x.ksppinm name,
y.ksppstvl VALUE,
decode(ksppity,
1, 'BOOLEAN',
2, 'STRING',
3, 'INTEGER',
4, 'PARAMETER FILE',
5, 'RESERVED',
6, 'BIG INTEGER',
'UNKNOWN') typ,
decode(ksppstdf,
'TRUE', 'DEFAULT VALUE',
'FALSE', 'INIT.ORA') isdefault,
decode(bitand(ksppiflg / 256, 1),
1, 'IS_SESS_MOD(TRUE)',
'FALSE') isses_modifiable,
decode(bitand(ksppiflg / 65536, 3),
1, 'MODSYS(NONDEFERED)',
2, 'MODSYS(DEFERED)',
3, 'MODSYS(*NONDEFERED*)',
'FALSE') issys_modifiable,
decode(bitand(ksppstvf, 7),
1, 'MODIFIED_BY(SESSION)',
4, 'MODIFIED_BY(SYSTEM)',
'FALSE') is_modified,
decode(bitand(ksppstvf, 2),
2, 'ORA_STARTUP_MOD(TRUE)',
'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
NAME VALUE DESCRIPTION
---------------------------------------- ------------ ------------------------------------------------------------
_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
applied
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 11.2.0.1 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 11.2.0.1 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
y
_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
on
_optimizer_extend_jppd_view_types TRUE join pred pushdown on group-by, distinct, semi-/anti-joined
view
_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
de
_optimizer_dim_subq_join_sel TRUE use join selectivity in choosing star transformation dimensi
ons
_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
s
_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
on
optimizer_secure_view_merging TRUE optimizer secure view merging and predicate pushdown/movearo
und
_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
vity
_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
on
_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
ments
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
n
_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
set
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
izer
_optimizer_multiple_cenv_report result control what to report in trace file when run in multi-plan
mode
_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.
No comments:
Post a Comment