At RMOUG, I "attended" the session by Mogens Norgaard "You Probably Don't Tune Right...And Never Have." Mogens stated that if you take two people of the same skill level, gave them the same statspack report and put them in separate rooms, they would come up with different conclusions. The issue is not skill, it is the lack of a real method for interpreting the data. Without a method, you are still "guessing". If you do not have a method you can explain to another person, that you can repeat multiple times and reach the same conclusion with the same data, that accurately identifies the root cause and recommends the correct plan of action...you really do not have a method...you have a bunch of guesses.
Software is the implementation of a method or process. It is a set of coded decisions. If condition A, then perform action 1, else perform action 2. It is consistent, it is repeatable (unless there are bugs of course). With the same set of inputs, you will (should) receive the same set of outputs. Codified tools are software...if you can't document a repeatable method or process...you don't have a real software so you don't have a real tool. If a tool can't take a 10046 trace as an input and return the correct optimization recommendation, if a tool can't examine session statistics and return the same correct optimization recommendation, if a tool can't examine system metrics and return the same correct optimization recommendation...IT'S NOT A COMPLETE OPTIMIZATION TOOL! It certainly provides valuable data, but that data still needs interpretation ("numbers are numbers until you interpret them" Jonathan Lewis).
Ah...but what about all the tools that are out there? What about the self-managing, self-tuning Oracle database? That is the stuff of marketing, not of technology. If there truly was a self-managing, self-tuning Oracle database...do you think there would be any market for DBAs and performance specialists? A team of DBAs costs a company hundreds of thousands (perhaps millions) of dollars each and every year...if there were real tools out there that could replace them, it would be a tremendous cost savings to a company.
As a performance specialist, I need to start putting together diagnostic routines that I can test and adapt. More importantly, diagnostic routines that I can share with others. And that, my faithful readers, is for another post and another project.
Saturday, February 16, 2008
Subscribe to:
Post Comments (Atom)
Followers
11 comments:
I agree wholeheartedly. There is a serious need for a repeatable methodology. Even before routines, there is a need for something that probably would best be expressed in a flowchart. I'm glad that you are going to take on the challenge.
>> If there truly was a self-managing, self-tuning Oracle database...do you think there would be any market for DBAs and performance specialists?
But there is hope thatthe well-structured tuning acxtivities can be automated (finding "missing" indexes", AMM), but I agree, tuning will always be a semi-structured task that requires human intuition.
Tuning can't be a structured, well defined task that can be put into a software tool until what is being tuned is also deterministic.
In current Oracle, it isn't. Witness the optimizer that changes its plans depending on the contents of bind variables - aka "bind-variable peeking".
One of the most crazy ideas from the Oracle optimizer folks, quite frankly: it virtually assures single-handed that no execution of a given SQL will be the same across different samples of data.
EXACTLY and PRECISELY what any production dba loathes: a system that changes its behaviour in a random pattern!
Apparently, it hasn't downed on the ORacle folks yet that production environments want STABLE and PREDICTABLE performance, not random performance.
Sorry, folks, but bind-variable peeking in its current format is dead wrong and an enemy of ANY predictable tuning. Good idea in theory, just not practical in its current format.
Noons is wrong; the CBO is not formally random. It would be more accurate to define it as non-linear or chaotic
.
The changes in behaviour (eg caused by bind peeking) are certainly deterministic but can have massive (and non-intuitive) impact on system performance; equally, the use of time-dependent tuning advisors, stats gathering jobs etc means there can be plenty of undamped feedback into the system which can lead to wild instability.
So all the more black magic is required to understand what's going on....
Cheers Nigel
Nice post, Dan. I was going to comment here, but it grew a little long ;-)
http://oracledoug.com/serendipity/index.php?/archives/1382-How-useful-are-diagnosticoptimization-tools-Another-View.html
Cheers,
Doug
Erm, let me try that link again. Apologies.
http://oracledoug.com/serendipity/index.php?/archives/1382-How-useful-are-diagnosticoptimization-tools-Another-View.html
Oh, it appears to be converting the link. Oh, well, hope you see the post anyway and thanks for raising this debate!
Nigel, I never said the CBO is random. Please don't twist what I said.
But I agree entirely that the END RESULT of its execution can indeed be best described as chaotic! :-)
Noons,
You rightly said that dba want predictable performance, not execution plans. And CBO, with bind peeking, tries to give you the first, it may fail, but that's what it has in "mind".
There never has been a "right" answer, just degrees of correctness, or the best possible solution to a problem, at a given time.
If we can't build perfect software, why would we be able to correct it to perfection?
There is no argument here, really, is there?
Noons:
Well, you did use the word "random."
Twice.
Post a Comment