RE: Monitoring CBO degrading Switches in xplans?

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       

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