How does statistic gathering fit in with ITIL Change Management processes
Posted by John Hallas on September 16, 2014
In the (good) old days there was only one optimizer and plans were developed based on the precedence of the rules. For some reason I have always thought there were 11 rules but looking at the manual it tells me there are 15 rules
Nowadays the vast majority of databases will be using the CBO and gathering statistics either by using the overnight maintenance functions or manually gathering as required. I suppose there is always also a third option which is to load pre-saved statistics in on a newly created partition and then lock them.
The other day I identified some code that would perform better if we fixed a profile. Co-incidentally the profile would fix some statistics as the table was constantly having stale stats. I debated with myself whether that needed a change control and in the end decided it did not. That got me thinking about how we constantly allow changes to happen that can seriously change performance, for the better or worse with no change control at all. That of course happens when we gather statistics. I doubt many sites raise an ITIL change record when they manually gather stats and I would have thought nobody would do so for the scheduled maintenance activities and yet when, as DBAs, the first thing we ask when a job suddenly goes horribly wrong is – have the statistics changed?
If the issue gets escalated to a problem record then there is no change in the ITIL recording system that any change has been applied. I am not actually suggesting that we do raise change to gather stats as it would be well-nigh impossible to manage on some systems and we would also need to lock statistics to have a point to return to, which again is possible but a massive overhead.
So there is no point to my post really, just a general observation and a chance to air something that has crossed my mind a few times. I am happy to hear views on whether creating a profile should be flagged as a change – I suggest most will say yes as that is a definite action you are taking to make a change to the current operational process whereas gathering statistics is more like an engine fine tuning the ratio of air to petrol as it is running.