gather_schema_stats slow in production and fast in development?
We have the been running the command "EXEC dbms_stats.gather_schema_stats('CISADM',estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE);" daily for many years in our production environment.
Normally, it would take 3 or 4 hours to complete this job on a 250 GB schema in production.
Over the last 4 months, the time has slowly increased from 3 or 4 hours to 7 hours?
When I run this command and other variations of this command in a development environment it only takes 2 hours?
SQL> set timing on;
SQL> EXEC dbms_stats.gather_schema_stats('CISADM', cascade=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 01:57:16.85