INCREMENTAL stats. collection on large table takes long time after new column
Hi Guys
Version details : Oracle 12.2 running on Linux.
We have tables that are in terabytes which are partitioned by date and then sub-partitioned. They are have basic/advanced compression. There are no indexes. They hold more than seven years of data.
The table in question has its preferences set to CASCADE=>TRUE, GRANULARITY=>AUTO, INCREMENTAL=>TRUE, INCREMENTAL_STALENESS=>USE_STALE_PERCENT, USE_LOCKED_STATS, METHOD_OPT=>FOR ALL COLUMNS SIZE 1
Lets say if load 5 years of data in an empty table and collect stats using
dbms_stats.gather_table_stats(ownname => user,tabname => TABLE_NAME,granularity => 'AUTO',cascade => TRUE, degree => 8, method_opt => 'for all columns size repeat');