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....
Subscribe to:
Post Comments (Atom)
7 comments:
That's a great idea, I'd start with my test/dev systems first. Once you have some demonstrable improvement, it will be much easier to transition to production!
I agree. It is tempting to come up with a business case to turn on statistics_level in Production, but we all, somehow, inherently know that is just a "bad idea". =)
Are we suffering from "Compulsive Tuning Disorder"?
Asif: *grin* I kinda do not think so. But just to be completely forthcoming, what do you think?
Me, I do not have any particular thing I am trying to tune. I am merely curious; I like to see how things work.
Besides, isn't 11g basically going to do this for us?
> I like to see how things work.
Well, I agree, this is how we get to know more about database and its behavior.
As long as we are not suffering from CTD, everythings fine. After all, curiousity is the mother of invention. ;)
Charles, this is an off the topic comment and I apologize. Did you ever get to implementing the model/regr_slope query that you were toying about? Reference being: http://rwijk.blogspot.com/2007/10/sql-model-clause-tutorial-part-two.html
If yes, would you be able to share it?
thanks!
Manny
I apologize for the long delay!
I got really close. I let it slip off my LRU list and have not touched it in a while. But I would love to resurrect it at some point. The MODEL clause is a bit confusing to me, still; I think I have some of the rudiments down, but there is still a bit left for me to swallow.
If you want a copy of my latest draft, email me privately.
Post a Comment