There are a few expressions in the industry that irritate me - not necessarily for good reason but simply because they sound like the extremes of pretentiousness and marketing put together. (Pretentious, moi !)
For example: “root cause analysis”, “holistic methods” and so on – it’s all those little ways of saying “we’re doing just the same as everybody else, but we’re trying to sound as if we’re doing something better and more meaningful.”
When offered fluff like this, I always like to restate it from the opposite perspective to see what impression it makes:
“We have a holistic approach to tuning” = “Other people will only look at a little bit of your system”.
“We focus on root cause analysis” = “Other people don’t try to find out what the problem really is”.
Put like this, the (lack of) added value, and the hand-waving attempt at deception in statements of this type become that little bit clearer.
Nevertheless, I recently came across a brilliant piece of root cause analysis. It went like this:
Question: “Why have we started to see such a big increase in log file sync wait time ?”
Answer:”It’s the economy, stupid.”
Really – that was the root cause (although the pejorative is only there because I wanted to use the quotation attributed to Bill Clinton’s 1992 election campaign).
Here’s how it came about.
The housing market is slowing down (it’s the economy), and banks are less keen to offer mortgages (large scale loans based on property) to home-buyers**. This means they are also slower to process applications for mortgages.
So there’s this system which deals with mortgage applications that could have one of four possible states: new, processing, granted, rejected. Suddenly there are a lot more applications stalled in the ‘processing’ state.
Since most of the office work during the day focuses on current (processing) applications, most of the SQL run during the day now addresses a larger working data set, so uses more CPU to run. When the CPU load goes up, the time taken for a log file sync round trip can increase even when the number of log file syncs and the volume of redo log generated doesn’t change.
So – when the housing market slows down, the log file sync waits go up. That’s root cause analysis for you.
Footnote – this is not a true and accurate description of the system that prompted this little note but it is true that changes in the economy may affect your database performance.
** A recent story in The Times (UK, that is) pointed out that the banks seemed to be competing to see who could offer the worst possible deals. Presumably on the basis that they had to offer the mortgages to maintain their presence on the market, but didn’t want anyone to take them up.