Statistics for All Tables Getting Stale With No Data Change in 12.2 (Doc ID 2448781.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Data has not changed but many objects have stale statistics
• Noticed that SYS.GATHER_STATS_PROG (Schedule Name ORA$AT_xxxx_OS) started running at that exact time
• METHOD_OPT is specified to disable histogram creation
• Histogram is automatically created on some columns
• The table is partitioned
• Following testcase shows this:
  Testcase in 12.2:

  SQL> select count(*) from dba_tab_statistics where stale_stats = 'YES';

  COUNT(*)
  ----------
  125

  SQL> EXEC SYS.DBMS_STATS.GATHER_DICTIONARY_STATS (no_invalidate => FALSE);

  PL/SQL procedure successfully completed.

  SQL> EXEC SYS.DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (no_invalidate => FALSE);

  PL/SQL procedure successfully completed.

  SQL> select count(*) from dba_tab_statistics where stale_stats = 'YES';

  COUNT(*)
  ----------
  19

  After 2 hours, even after no changes done in database.

  SQL> select count(*) from dba_tab_statistics where stale_stats = 'YES';

  COUNT(*)
  ----------
  47

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.