DBA_TAB_STATISTICS Reports Stale Statistics Despite The Statistics Are Gathered (Doc ID 2530605.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• DBA_TAB_STATISTICS reports STALE statistics for the objects for which the statistics are collected recently.
• No change in COUNT(*) from the table and NUM_ROWS so that the statistics are NOT really STALE.
• DBA_TAB_MODIFICATIONS however shows the entries with modification information for the offending tables.
• Delayed flushing of monitoring information to DBA_TAB_MODIFICATIONS table.
• This happens for both partitioned and non-partitioned tables.
• Concurrent statistics are not enabled.
• Incremental option is not enabled for partitioned tables.
• Regathering the statistics changes the STALE_STATS flag to NO for the tables in DBA_TAB_STATISTICS.

Example: The last_analyzed of the below table shows Mar 27th.

It is clearly shown that the DMLs (inserts) happened on table XYZ on Mar 25th and the recent statistics are gathered on Mar 27th. However, the STALE_STATS flag is marked YES.

Changes

NA

Cause

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