Incremental statistics taking long time suddenly
On Oracle 19c we have a table that is very large and is partitioned on business date and sub-partitioned by location (lets say, NYK, LON, SYD and PAR) . This table has been there for three years and no structural changes has happened in the last few months.
Below please find the table preferences:
CASCADE : TRUE
GRANULARITY : AUTO
INCREMENTAL : TRUE
INCREMENTAL_STALENESS : USE_STALE_PERCENT, USE_LOCKED_STATS
METHOD_OPT : FOR ALL COLUMNS SIZE 1
Total partitions : 500, total sub-partitions : 12571, Size : 186GB, Advanced Compressed
In the last two weeks the stats collection has got struck three times. At session level we could see that it was going through all the partitions instead of just one. Instead of taking couple of minutes it took more than 4 hours to complete. Later we had to stop the stats collection, delete all statistics and recollect them again. It worked for few days and then repeated the same behaviour.