When I first started blogging, most of the material came from issues that I’d run into and how they were solved but, as I’ve spent most of the last 7 months not logging in to anything much ;-), it occurred to me that another area which I haven’t used for a long time is what I’ll call recurring conversations. <\/p>\n
Even though my blogging has slowed to a crawl, I still spend a lot of time having similar conversations with multiple people and on work chat channels on the same topics which indicates to me that those topics are not well understood. Because I work with a lot of smart people, it’s typically not the details that they struggle with. They’ve read detailed technical blog posts and have performed multiple web searches so have read the most detailed material available and yet somehow they’re missing the *point*. Maybe that’s the problem with learning everything via blogs and white papers? That it’s no substitute for someone explaining the fundamental concepts and design of features? I could probably rephrase that as, maybe there’s no substitute for actually reading a book or attending a course occasionally? I appreciate how out-of-date that view might be though.<\/p>\n
I’m sure some will have already realised that Recurring Conversations could probably be called Frequently Asked Questions, so let me begin this first post with <\/p>\n
‘Why are my Global Statistics taking longer to gather when I use Oracle’s snazzy 11g Incremental Global Statistics feature than when I don’t?’<\/p>\n
This has baffled a lot of people I know because I’m not sure they understand fully why the feature was introduced. They want to convert one of their existing partitioned tables to use Incremental Global Statistics and so they test the performance by doing something like this.<\/p>\n
1)\u00a0Delete all of the stats on a large partitioned table.
2)\u00a0Set INCREMENTAL to FALSE and then gather table stats using GRANULARITY =>’GLOBAL ‘
3)\u00a0Set INCREMENTAL to TRUE and then gather table stats using GRANULARITY =>’GLOBAL ‘<\/p>\n
When they time this they find that 3 takes just as long as 2 and, in fact, it takes a little longer! This is useless? What is the point of this new feature if it doesn’t speed up the gathering of Global stats? <\/p>\n
First I want to look at what we asked Oracle to do in steps 2) and 3) above.<\/p>\n
2) Visited all of the partitions of the table to gather information and then update the Global stats on the table.
3) Visited all of the partitions of the table to gather information, update the Global stats on the table and generate synopses for future use.<\/p>\n
On that basis, why *wouldn’t* option 3 take longer than option 2? They do more or less the same thing but 3) has to do a little additional work. <\/p>\n
So if it isn’t quicker to gather Global Stats using Incremental Global Statistics, why would you use it?<\/p>\n
The benefits don’t come from the initial gathering of Global Stats but when you gather stats on new Partitions and *don’t* need to gather Global Stats any more. Instead Oracle uses those handy synopses to update them which is a much quicker operation! The Real World cycle of use then looks like this.<\/p>\n
1)\u00a0Delete all of your existing table stats.
2)\u00a0Set INCREMENTAL to TRUE.
3)\u00a0Gather table stats using GRANULARITY =>’GLOBAL\u00a0 AND PARTITION’ without supplying\u00a0 a PARTNAME. This will re-gather all of your Global and Partition stats across the table and build the initial synopses. Note that at this stage you have achieved no reductions in stats collection times.
4)\u00a0As you load partitions with new data or the data changes and you need to update your stats, use one of a number of options but the one I tend to use is to gather the stats on each specific partition using GRANULARITY=>’ GLOBAL AND PARTITION’\u00a0 with PARTNAME set to the name of the partition we’ve just loaded. Oracle will now gather Partition stats on just the one Partition and update the Global Stats and the synopses based on the new data that’s been introduced. <\/p>\n
Bingo \u2013 you’ve just maintained accurate Global Stats without having to trawl through the entire table again! <\/p>\n
That’s the point. <\/p>\n
It’s about *not* gathering Global Stats but also not letting them drift hopelessly out of whack with the contents of the table. Measuring the performance of a full Global Stats gathering operation doesn’t illustrate the performance benefits.<\/p>\n","protected":false},"excerpt":{"rendered":"