SQL Performance (MOSC)

MOSC Banner

gather_schema_stats slow in production and fast in development?

edited May 10, 2018 6:09PM in SQL Performance (MOSC) 13 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center