Oracle DBA – A lifelong learning experience

Posts Tagged ‘cbo’

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.


Posted in Oracle | Tagged: , , | 2 Comments »

Excellent Optimizer Statistics articles

Posted by John Hallas on April 12, 2012

For anybody who is interested in reading about optimizer statistics and gaining a clear understanding on what they can do and how they can be managed then I suggest reading the following two white papers

Part 1 – Understanding Optimizer Statistics

Part 2 – Best Practises for Gathering Optimizer Statistics

Part 2 contains the best, most easily understood explanation of the problems with bind variable peaking and how they were addressed by using adaptive cursor sharing that I have seen.

Overall both documents are well written with good explanations and diagrams and I think anybody who has any interest in the Oracle Database engine and the tuning of databases for both consistency and performance should make these articles a must read. 



Posted in 11g new features, Oracle | Tagged: , , , | Leave a Comment »