Oracle DBA – A lifelong learning experience

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.

2 Responses to “How does statistic gathering fit in with ITIL Change Management processes”

  1. James Gardner said

    We typically record it as a change so it is visible in case somebody comes back weeks later and says what changed on this date but would not necessarily put it through fully fledged change control (with business approval) unless we expected negative impact. gathering system stats, dictionary stats etc. The ability to backout and replace the old stats which i think are now held in sysaux means the change can be regressed pretty easily.

    • John Hallas said

      Great to hear from you James. Sent you a mail. I agree you can regress changes but you don’t always know what has changed or have captured the stats beforehand. Really it is just one of my pet peeves that I thought I would mention. Ho we have rigorous change control for the most trivial of things and yet new stats can make a massive difference and they are allowed to be applied more or less unhindered. Not at my current site particularly but in general.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: