By user702295-Oracle on Nov 19, 2013
Hello! Have you implemented partitions on a large Demantra table? Are you using RDBMS version 11gR2? Then consider implementing incremental statistics.
For all 11gr2 environments with large partitioned or subpartitioned objects turn on incremental statistics using this command:
Once this is set for any given table we should gather statistics on that table using the normal tool (fnd_stats in ebs or dbms_stats elsewhere). This first gather after turning it on will take longer than previous analyzes. Then going forward we will see the following :
1 – the normal dbms_stats (or fnd_stats) will only gather statistics on lower level partitions if the statistics on that partition are stale. This is a significant change. That is going forward using the default options of a gather command may in fact perform no reanalyze on the actual data if the modifications to the table don’t warrant it.
2 – if a subpartition is stale the normal stats will ONLY gather statistics on that subpartition. The partition for that subpartition will be re-derived as will the global statistics – no other statistics will be gathered.
Making this change promises to reduce gather stats by hours in some cases.