UxOra - Unix and Oracle tech notes
Welcome to UxOra,
This website publishes technical notes on Unix and Oracle (=UxOra) especially as a DBA point of view.
Please read the writing conventions here first.
I hope it may help you.
DBMS_STATS.GATHER_TABLE_STATS collects statistics on a partitioned table, generally it does so at the partition and table (global) level (the default behavior can be modified by changing the
GRANULARITY parameter). This is done in two steps:
- First, partition level stats are gathered by scanning the partition(s) that have stale or empty stats,
- Then a full table scan is executed to gather the global statistics.
As more partitions are added to a given table, the longer the execution time for
GATHER_TABLE_STATS, due to the full table scan requited for global stats.