DBA_TAB_STATISTICS Reports Stale Statistics Despite The Statistics Are Gathered
(Doc ID 2530605.1)
Last updated on JULY 15, 2021
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.2 to 12.2]
Information in this document applies to any platform.
- 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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document