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:
Post a Comment