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.
Table monitoring is by default activated if
STATISTICS_LEVEL system parameter is set to
Oracle keeps table monitoring data in memory, then flush it into the dictionary when
FLUSH_DATABASE_MONITORING_INFO is called. These datas are mainly used to determine if objects need new statistics.
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.