Oracle 19.26
I'm (still) moving a set of tables from being partitioned by custom code to using Oracle's built in partitioning.
The query performance seems to be improved by the move, but I'm getting a MASSIVE performance hit on gathering statistics.
The old world had a single table with 5 indexes, and a DBMS_STATS.GATHER_TABLE_STATS( user, old_single_month_table ) with default parameters takes around 50s.
INCREMENTAL statistics are enabled, INCREMENTAL_LEVEL is PARTITION, GRANULARITY is AUTO and INCREMENTAL_STALENESSis USE_STALE_PERCENT.
The new structure has the same 5 indexes on the partitioned table, all created as LOCAL indexes, and yet gathering the statistics on the same block of data using GATHER_TABLE_STATS( user, partitioned_table, single_month_partition ) is taking 14 MINUTES, so a performance hit of 18 times.
Performing the same operations with CASCADE = FALSE completes in 15 and 4 seconds respectively, so a 3 fold improvement.
Does anyone have any experience of speeding up INDEX statistics gathering?
Most of the info in blog posts is about parallelising etc in order to throw more resource at the problem, but this would obviously also speed up the legacy situation, so it's not an answer to why there is such a massive performance hit from partitioning, and how that can be resolved.