Oracle SQL Optimizer parameters Oracle Tips by Burleson Consulting

The Oracle SQL optimizer is heavily influenced by it's parameters and statistics (schema statistics and system statistics).

For complete details on Oracle CBO and SQL tuning, see the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle SQL tuning scripts.

• optimizer_mode - This is the most important optimizer parameter, and the default value of all_rows may not be right for online systems.
   
• optimizer_index_cost_adj -  This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the lower the cost of index access.
   
• optimizer_index_caching - This parameter tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching affects the CBO's decision to use an index for a table join (nested loops) or to favor a full-table scan.
   
• optimizer_dynamic_sampling - The optimizer_dynamic_sampling initialization parameter controls the number of blocks read by the dynamic sampling query.  A value of 0 means dynamic sampling will not be performed. Increasing the value of the parameter results in more aggressive dynamic sampling, in terms of both the type of tables sampled (analyzed or un-analyzed) and the amount of I/O spent on sampling.
   
• db_file_multiblock_read_count - When this parameter is set to a high value, the CBO recognizes that scattered (multiblock) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans.
   
• hash_area_size - The setting for hash_area_size parameter governs the propensity of the CBO to favor hash joins over nested loop and sort merge table joins. Note restrictions when using pga_aggregate_target.
   
• sort_area_size - The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over presorted index retrieval.
   

• pga_aggregate_target - This value governs the SQL optimizers propensity to choose hash joins.

We also see these lesser optimizer parameter:

active_instance_count bitmap_merge_area_size cpu_count cursor_sharing flashback_table_rpi hash_area_size hash_join_enabled hash_multiblock_io_count optimizer_dynamic_sampling optimizer_features_enable optimizer_features_hinted optimizer_mode_hinted optimizer_percent_parallel optimizer_search_limit optimizer_secure_view_merging parallel_broadcast_enabled parallel_ddl_forced_degree parallel_ddl_forced_instances parallel_ddl_mode parallel_dml_forced_dop

parallel_dml_mode parallel_execution_enabled parallel_query_forced_dop parallel_query_mode parallel_threads_per_cpu partition_view_enabled query_rewrite_enabled query_rewrite_expression query_rewrite_integrity skip_unusable_indexes sort_area_retained_size sqlstat_enabled star_transformation_enabled statistics_level workarea_size_policy

And also, hidden Oracle optimizer parameters.

2007 PGA Update:  Oracle technology is constantly changing, so don't miss my new notes on updates to Oracle PGA behavior.   Also see these important notes on over-riding the Oracle PGA defaults.

Here are my notes on PGA parameters and SQL tuning:

• Oracle PGA advice Utility for Oracle

• PGA memory content explained

• Optimizing Oracle RAM SGA PGA

• Oracle PGA sort hash _smm_max_size & manual workarea

• Oracle PGA scripts

• Oracle hidden memory manager PGA advice parameters