Poor performance when gathering partitions sta ...

Being busy at work is both a blessing and a curse to blogging activity. On the one hand, the more that's going on, the more technical issues there are likely to be to blog about. On the other, when the pace is frantic and problems are coming thick and fast, taking good notes isn't a priority (although it's probably even more important). So this blog post has been stuck in the same place for weeks because when the problem occurred, I didn't take enough notes and I have struggled to recreate it in my own environment. It was sucking up so much time that I almost decided not to post it but I'll post what few details I remember in case it helps some desperate Google-scavenger one day.

We were migrating one of our main databases to version 11.2.0.1 and restructuring a large partitioned table at the same time. However, when we started trying to gather opitimiser statistics on the partitions of the table, performance was atrocious. Our approach was to gather the statistics at the Partition level, one partition at a time so that when all partitions had stats, they would be aggregated up to the table level as approximated global stats. At this point you might wonder why we didn't just use 11g new features and gather stats at the default granularity and sample size. We did try that several times, but gave up after several hours of running. The intention of the project was to simply port the existing system to 11.2.0.1 with the minimum number of changes and look at exploiting new features later. As usual, that was largely driven by our deadlines

Although I don't have the exact timings to hand, I recall it was taking about 3 minutes to gather stats on each partition where we'd normally expect it to take a few seconds. We spent days trying to work out what was going on and the two main symptoms were

Many executions of the following statement.

SELECT /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size,
       MINIMUM, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln
FROM hist_head$
WHERE obj# = :1 AND intcol# = :2

As we became more desperate to solve the problem and the deadline approached, I fell back on an old, basic but often successful technique. Compare what was different on the new database to the old one and noticed that the new database was missing Global Stats on the table. That made sense, though, because our intention was to use Aggregated Global Stats and as we hadn't gathered stats on all of the partitions, Global Stats shouldn't exist at that stage. But it still bugged me because that was the only difference I could see (apart from one database being on 10.2.0.3 and the other on 11.2.0.1 of course).

Rooting around Metalink, I also came upon bug number 282598.1 which was inappropriate because it was for a completely different version, had been fixed and the problem description was different, but it looked like it was in the same area of functionality so it was perhaps another little pointer to what might be going on. 

To cut a long story short (one that included a brief email exchange with Greg Rahn), eventually I suggested we gather Global Stats just to reassure myself that wasn't the problem. As soon as we did, the partition stats gathering performance went back to what we'd expect. Obviously we needed to regather Global Stats once all the Partition stats were in place and then we'd have to implement something to keep the Global Stats up to date.

As I suggested at the start, that's not as much detail as I'd expect to include normally but hopefully it's another example of why you avoid gathering Global Stats and using default Oracle functionality at your peril.