Oracle 19.26.0.0.0 Enterprise.
So the awful gathering of table stats issue has re-raised it's head in my attempt to move an old program to using partitioned tables.
Gathering stats used to take hours (on an 800G table partitioned into 40 partitions, so averaging 20G/ partition), even though data is only ever inserted into a single partition at a time, so I did a load of stuff around setting parameters, and got the DB to understand that it only needed to gather stats on the single partition, and got performance to where it should be - a gather stats on October 13th took 3.5 minutes after inserting rows into a single partition. I have made no changes to my table settings, and a gather stats call with the same parameters and after the same number of inserts took 18.5 HOURS on 3rd November, so around 700 times slower.
I didn't watch the first call explicitly, but I'm assuming that it did what it was supposed to, and gather stats on the one modified partition, and estimated the global stats.
The later call chugged through all 40 partitions, and all 5 indexes on each.
One thing I have noticed is that ‘select * from DBA_TAB_MODIFICATIONS for the table name’ gives me 2 records before the stats gathering - one for the 17million rows inserted into the partition, and one for the 80million rows that have been inserted into the whole partitioned table (all have been inserted into that same partition, over 4 or 5 processing runs).
After the gather stats, the partition row has been removed from DBA_TAB_MODIFICATIONS (as expected), but the 80million for the table is still there.
It looks to me like this record isn't being cleared, and that is screwing up the gather stats logic.
a) Has anyone else come across similar behaviour?
b) Am I looking in the right place with DBA_TAB_MODIFICATIONS, or are there some other things I need to be worrying about?
c) Any solutions?