Randolf Geist on 11g Incremental Statistics

Well it wasn't the post I planned to return to technical matters with.

Lots of readers here have asked me when I'm going to get round to writing about 11g Incremental Statistics as part of the stats series. Although Incrementals are on my To Do list, I wanted to finish off the stats copying posts first. In any case, Randolf Geist got there already so I'll cross it off my list and point you towards his post instead.

Yes, I know there have been a lot of Incrementals posts already by people like Robin Moffat and John Hallas, but Randolf's post maps most closely on to the post I planned, which is an overview of Incrementals that highlights some of the practicalities of using them in "The Real World". I'd particularly draw attention to a couple of aspects which I think people keep misunderstanding.

1. The first time you implement Incrementals on a table, Oracle will have to trawl through the entire table in order to build the initial synposes. This has always seemed obvious to me - how can you incrementally build on synposes that haven't been created yet? But the long duration initial gather seems to surprise people and they decide that Incrementals are 'slow'.
   
   
2. Incrementals are a replacement for GRANULARITY=>'GLOBAL AND PARTITION' and not 'PARTITION'! Expecting an option which gathers Partition stats and then goes around updating synposes to perform as well as a simple partition gather is unrealistic^*. Any performance improvement needs to be measured against both gathering the Partition stats and maintaining the Global stats. Incrementals will almost definitely be quicker than that! I prefer to think of Incrementals not so much as a performance improvement (because most people probably didn't regather Global statistics every time they gathered individual Partition statistics because they didn't have the time on an active system), but an improvement to the quality of your Global stats because you can now afford to maintain them with the same frequency as your Partition stats, rather than scheduling an out-of-hours Global stats gather or depending on the inaccurate NDVs that result from the previous aggregation process.
   

Good post, anyway. Thanks Randolf!

^* However, it's fair to say that Oracle have continued trying to improve the performance of synposis maintenance.