Previously I talked about the importance of historical perspective to server performance tuning, but it applies to many areas of working with databases. For the last part of this mini-series, I thought I’d pull together a few more examples.
Execution Plans and Optimiser Statistics
I’ve written several blogs recently about the value of keeping a history of optimiser statistics. Even if you don’t suffer problems with changing execution plans most of the time and your system performs well, there’s always the possibility that execution plans could change when you refresh the statistics on database objects.
Once an execution plan changes and all you have is the current execution plan and the current statistics you might be able to work on the plan to improve it, but it’s much more difficult than being able to compare the old ‘good’ plan with the ‘bad’ plan and work out what’s changed and why. If you have the old statistics, you should be able to generate the old plan for comparison.
That’s why the introduction of automatic optimiser stats history in 10g is so welcome.
Space Usage
One of the most important ongoing DBA tasks is working out when we’re going to run out of space. As well as the immediate need to monitor any tablespaces which are close to filling so that emergency action can be taken, longer term projections are normally required to make sure that sufficient storage is allocated before it’s needed. That might include the need to provision more hardware, which can take time.
The key word in the previous paragraph is ‘projections’. How can we project how things are going to look in the future if we don’t know how they looked before today?
System Load
There’s one consequence of my insistence on running Statspack snapshots by default that’s repaid itself many times over the years. Imagine a scenario where a server is running at over 90% CPU load over long periods of the day. Response times are plummeting, the business users are going crazy and want to know what’s going on.
Maybe there is some problem with the performance of the server, maybe an application release has back-fired or maybe, just maybe, there concurrent user population has doubled over the past couple of months? But how would you know for sure? It might be that the system just doesn’t have enough capacity to cope with the increasing workload. The only way to measure ‘increasing’ is to retain some history and, let’s face it, a database is an ideal place to retain it!
Looking back at these few examples, they’re all really examples of capacity planning. Whilst we can put together capacity plans based on calculated projections and estimated loads, projections based on past history have a real-world element to them that I’ve found is likely to make them more reliable.
I hope it’s clear by now that, if you don’t know how things used to look in the past, it’s much more difficult to analyse the present and predict the future.