 |
|
Oracle Tips by Burleson |
SQL Optimizer Parameters
The Oracle SQL optimizer is one of the most
sophisticated components in Oracle, and has the most hidden
parameters. These parameters control virtually every aspect of
the SQL optimizer, and there are many important parameters in this
area. Changing these undocumented parameters at the
session-level can be a great aid when tuning stubborn SQL statements.
If you carefully inspect the parameters below
you will get some insight into the behavior of the SQL optimizer and
the parameters that are used for the optimizer to always choose the
“best” execution plan.
SQL Optimization
List of undocumented Oracle parameters
Parameter
Current Instance
Name
Value Value
---------------------------------------- ----------
--------------------
Parameter
Description
------------------------------------------------------------------------------_abort_recovery_on_join
FALSE FALSE
if TRUE, abort recovery on join reconfigurations
_always_anti_join
CHOOSE CHOOSE
always use this method for anti-join when possible
_always_semi_join
CHOOSE CHOOSE
always use this method for semi-join when possible
_close_cached_open_cursors
FALSE FALSE
close cursors cached by PL/SQL at each commit
_cost_equality_semi_join
TRUE TRUE
enables costing of equality semi-join
_cursor_db_buffers_pinned
17 17
additional number of buffers a cursor can pin at once
_cursor_plan_enabled
TRUE TRUE
enable collection and display of cursor plans
_domain_index_batch_size
2000 2000
maximum number of rows from one call to domain index fetch routi
_domain_index_dml_batch_size
200 200
maximum number of rows for one call to domain index dml routines
_dynamic_stats_threshold
30 30
delay threshold (in seconds) between sending statistics messages
_explain_rewrite_mode
FALSE FALSE
allow additional messages to be generated during explain rewrite
_full_pwise_join_enabled
TRUE TRUE
enable full partition-wise join when TRUE
_groupby_nopushdown_cut_ratio
3 3
groupby nopushdown cut ratio
_groupby_orderby_combine
5000 5000
groupby/orderby don't combine threshold
_gs_anti_semi_join_allowed
TRUE TRUE
enable anti/semi join for the GS query
_idl_conventional_index_maintenance
TRUE TRUE
enable conventional index maintenance for insert direct load
_ignore_desc_in_index
FALSE FALSE
ignore DESC in indexes, sort those columns ascending anyhow
_improved_outerjoin_card
TRUE TRUE
improved outer-join cardinality calculation
_index_join_enabled
TRUE TRUE
enable the use of index joins
_index_prefetch_factor
100 100
index prefetching factor
_left_nested_loops_random
TRUE TRUE
enable random distribution method for left of nestedloops
_lm_node_join_opt
FALSE FALSE
lock manager node join optimization in reconfig
_local_communication_costing_enabled
TRUE TRUE
enable local communication costing when TRUE
_minimal_stats_aggregation
TRUE TRUE
prohibit stats aggregation at compile/partition maintenance time
_multi_join_key_table_lookup
TRUE TRUE
TRUE iff multi-join-key table lookup prefetch is enabled
_mv_refresh_use_stats
TRUE TRUE
pass cardinality hints to refresh queries
_nested_loop_fudge
100 100
nested loop fudge
_new_initial_join_orders
TRUE TRUE
enable initial join orders based on new ordering heuristics
_new_sort_cost_estimate
TRUE TRUE
enables the use of new cost estimate for sort
_oneside_colstat_for_equijoins
TRUE TRUE
sanity check on default selectivity for like/range predicate
_optim_enhance_nnull_detection
TRUE TRUE
TRUE to enable index [fast] full scan more often
_optim_new_default_join_sel
TRUE TRUE
improves the way default equijoin selectivity are computed
_optim_peek_user_binds
TRUE TRUE
enable peeking of user binds
_optimizer_adjust_for_nulls
TRUE TRUE
adjust selectivity for null values
_optimizer_choose_permutation
0 0
force the optimizer to use the specified permutation
_optimizer_cost_model
CHOOSE CHOOSE
optimizer cost model
_optimizer_degree
0 0
force the optimizer to use the same degree of parallelism
_optimizer_mode_force
TRUE TRUE
force setting of optimizer mode for user recursive SQL also
_optimizer_new_join_card_computation
TRUE TRUE
compute join cardinality using non-rounded input values
_optimizer_new_mbio
0 0
enables the use of new costing I/O with mbio
_optimizer_percent_parallel
101 101
optimizer percent parallel
_optimizer_search_limit
5 5
optimizer search limit
_optimizer_undo_changes
FALSE FALSE
undo changes to query optimizer
_ordered_nested_loop
TRUE TRUE
enable ordered nested loop costing
_ordered_semijoin
TRUE TRUE
enable ordered semi-join subquery
_parallelism_cost_fudge_factor
350 350
set the parallelism cost fudge factor
_partial_pwise_join_enabled
TRUE TRUE
enable partial partition-wise join when TRUE
_push_join_predicate
TRUE TRUE
enable pushing join predicate inside a view
_push_join_union_view
TRUE TRUE
enable pushing join predicate inside a union view
_px_index_sampling
200 200
parallel query sampling for index create (100000 = 100%)
_query_cost_rewrite
TRUE TRUE
perform the cost based rewrite with materialized views
_reuse_index_loop
5 5
number of blocks being examine for index block reuse
_row_cache_cursors
10 10
number of cached cursors for row cache management
_smm_auto_cost_enabled
FALSE FALSE
if TRUE, use the AUTO size policy cost functions
_sort_elimination_cost_ratio
0 0
cost ratio for sort eimination under first_rows mode
_sort_multiblock_read_count
2 2
multi-block read count for sort
_sort_space_for_write_buffers
1 1
tenths of sort_area_size devoted to direct write buffers
_sortmerge_inequality_join_off
FALSE FALSE
turns off sort-merge join on inequality
_sql_connect_capability_override
0 0
SQL Connect Capability Table Override
_sqlexec_progression_cost
1000 1000
sql execution progression monitoring cost threshold
_subquery_pruning_cost_factor
20 20
subquery pruning cost factor
_subquery_pruning_enabled
TRUE TRUE
enable the use of subquery predicates to perform pruning
_subquery_pruning_mv_enabled
FALSE FALSE
enable the use of subquery predicates with MVs to perform prunin
_subquery_pruning_reduction
50 50
subquery pruning reduction factor
_system_index_caching
0 0
optimizer percent system index caching
_table_scan_cost_plus_one
FALSE FALSE
bump estimated full table scan cost by one
_unnest_notexists_sq
SINGLE SINGLE
unnest NOT EXISTS subquery with one or more tables if possible
_unnest_subquery
TRUE TRUE
enables unnesting of correlated subqueries
_use_column_stats_for_function
TRUE TRUE
enable the use of column statistics for DDP functions
_use_new_explain_plan
FALSE FALSE
if TRUE, use the AUTO size policy cost functions
_use_nosegment_indexes
FALSE FALSE
use nosegment indexes in explain plan
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
