Tuesday, 7 August 2007

Gather Stats – the importance of granularity

I got caught out today when using dbms_stats.gather_table_stats. I specified a partition name in the statement because I just wanted to gather stats for a particular partition. I did not however specify the granularity. The default value for the parameter will also cause dbms_stats.gather_table_stats to generate the global table stats. The partition I was trying to get stats for was only 60 millions rows so it should only have taken a few hours, the full table however contained 700 million rows so gathering the stats for the partition and the global table stats took 14 hours!

For reference the table below option for granularity.

GRANULARITY

Table Global

Partition Global

Partition Statistics

Subpartition Statistics

GLOBAL

YES

NO

NO

NO

PARTITION

NO

YES

YES

NO

DEFAULT

YES

YES

YES

NO

SUBPARTITION

NO

NO

YES

YES

ALL

YES

YES

YES

YES

This was obtained from the following webpage

http://www.dbazine.com/oracle/or-articles/nanda5

No comments: