Hi, We moved from 11.2.0.4 to version 19.11.0.0.0 of oracle for this
database. And we are seeing for one of the table the stats gather has been
taking longer. It was used to take ~5-6minutes vs now its taking
~30-40minutes. Dont have enough AWR history to verify the exact run time
from ASH/AWR. But we can see it from the application log which notes the
start and end time of the stats gather.
There does exists 5 indexes in this table but when i am seeing ,
DBA_OPTSTAT_OPERATION_TASKS its showing ~1 minutes for all the index stats
gather combinely. Its the table/column gather which takes longer.We are not
able to test it as we dont have a 11.2 lower environment with us now though.
The table size is 25Gb and is non partitioned one holding ~13million rows.
Its having 111 columns. Its having histograms on ~36 columns but all of
them are still showing height-balanced and frequency. I was expecting those
to be hybrid , but its didnt happen, may be because of the way the stats
getting gathered on this table i.e. with hard coded 50% sample i.e as
below.
exec DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => NULL, TABNAME => table_name,
ESTIMATE_PERCENT => 50, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS
SIZE AUTO', DEGREE => 4);
So my question is what must be the reason that same stats gather with ~50%
sample size now taking ~30minutes more on 19C as compared to 11.2 where it
was finishing in ~5minutes?
