Oracle Corner

This is the list of actions that can be taken to stabilize the 10g database execution plans:

• Switch off the bind variable peeking
• Bind variable peeking means that when a cursor is compiled, the optimizer will "peek" to see the real literal values. It will use the statistics for those values to create a plan. Of course, if the first literal values are "odd" ones, you will get a plan that does not suit the rest of the executions.
• I have not yet seen a 10g database that benefited from this feature. I have raised a service enhancement request with Oracle to expand the peeking to all executions and change the explain plan only if required (or spawn a new plan if required).
  
• This parameter is "TRUE" by default in 10g.
  
• alter system set "_optim_peek_user_binds"=FALSE;
• Switch off the density recalculation in 10g
• By default, Oracle 10g recalculates the computed density when the cursor is reloaded. This can cause the plans to change even if no stats have been gathered in the meantime.
• alter system set event='10139 trace name context forever' scope=spfile
• Check whether cursor_sharing=similar is causing the frequent change in execution
• This parameter can cause issues if your histograms are not correct. Check them.
  
• Re-gather the histograms with either SKEWONLY or SIZE 255
• Gather the histograms for ALL columns, not just indexed. Histograms can be used for join columns and for the predicate columns without indexes.
  
• If you regularly re-analyze, check whether your statistics are good
  
• I know this is a bit of work, but there are few bugs that produce the wrong statistics.
• It is always worth browsing the Metalink bug database for the bugs and fixes for your version.
  
• Temporarily stop analyzing, if you can afford it
• Create a "snapshot" of your plans once when you stop analyzing
• Create a "snapshot" of your statistics, with dbms_stats.export_*_stats
  
• Once when you have the stable system, slowly start re-analyzing
• Manipulate your statistics
  
• At the end, statistics are just the numbers that give you certain plans at a certain time.
  
• If you need to manipulate the statistics to produce the good results and you know what you are doing, go for it.
• Decrease the dynamic sampling
• The sampling helps Oracle "improve" the non-existing or stale statistics
• Try switching it off on the session level to see whether it changes the plan
  
• Keep the history of your execution plans and statistics, for a comparison
  
• Oracle 10g keeps the plan history in AWR table WRH$_SQL_PLAN
  
• Create the outlines for your statements
• Export the statistics with dbms_stats.export_schema_stats (or similar)
• Keep the different STATIDs to compare
  
• Take 10053 traces for the SQLs you know are problematic

References:

• Metalink note 338113.1 and bug 4567767

Keywords:
"unstable plans" "plan changed" "stabilize execution plans" "execution plan changed" "changed SQL plans" "changed execution plans"