A Tuning experiment

As I learn more about the CBO and Banner, I start to wonder about "system tuning". I like Cary Millsap's "Method R" concept; I was thinking about how to adapt that in the narrow field of sql statements. If, for example, I turn set statistics_level = ALL, I know I can generate rowsource statistics at the cost of perhaps 5% overhead to my system. Doing this in a development environment, I could utilize Wolfgang Breitling's "Cardinality Feedback" to find sql statements that are not performing optimally, and maybe even rank them by degradation. Maybe.

Why would I want to do that? Good question. In and of itself, it is a hard sell if my only purpose is to go on a witch hunt and try to tune the system purely from the sql. However, I think the bigger advantage is to use the various sql statements as teachable moments, a way to develope real-life examples for our DBAs that demonstrate how our application(s) behave, and how to attack "slow" statements. The fortunate side-affect is that we end up making some things faster.

Just an idea. I have not been able to drill down into 11g's automatic tuner yet, but I have to believe it is using something similar to cardinality feedback. If memory serves, I believe it does spend some extra overhead reconsidering previous sql plans in an attempt to find those that are not optimal (or that crazy phrase "suboptimal"). Now I have to go back and read up on that again just to refresh myself....