RE: Monitoring CBO degrading Switches in xplans?

  • From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
  • To: free <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 19 Jun 2009 01:08:25 -0700 (PDT)

One of the ways to monitor it is by plan_hash_value and get the corresponding 
bind_data from awr; this will not only tell you when the plan changed and 
possibly why also.

If the data distribution is uniform for the most part, i.e., not much skewness 
in the data, and the change in data size is in general not going to influence 
data distribution or relative to each other table changes....then you can

1) turn off auto stats gathering job
2) turn off bind peeking (but this can generate suboptimal execution plan for 
LIKE operations in join queries or even for index look ups due to default bind 
value assumptions made by optimizer, make sure to test your queries before you 
make this change)
3) turn off optimizer_dynamic_sampling (again make sure that statistics were 
gathered for all objects)
4) gather stats only if the data distribution pattern is changed (including 
system statistics)

We did all of the above and never have any problems with execution plan; for us 
consistency and predictability is far more important than tiny change in run 
time performance.

Thanks,
 Sai
http://sai-oracle.blogspot.com



      
--
//www.freelists.org/webpage/oracle-l


Other related posts: