DB version: 11.2.0.4
OS : Oracle Linux 6.4
I noticed that , in one of my critical DB's schema I have 68,357 segments with dba_tab_statistics.STALE_STATS value is set to NULL .
Any idea why ? I selected few tables/partitions in random and took the row count. They all are empty.
So, Is it safe to assume dba_tab_statistics.STALE_STATS value for a segment will be set to NULL , when that partition/table is empty and oracle doesn't bother to collect the stats ?
SQL> set NULL <<>>
SQL> select stale_Stats, COUNT(*) frOD dba_tab_statistics where owner = 'TFX_EMS' GROUP BY stale_Stats;
STALE_STATS COUNT(*)
------------------------- ----------
<<>> 68357
NO 314265
YES 17404
SQL> select table_name, PARTITION_NAME, OBJECT_TYPE, LAST_ANALYZED, num_rows From dba_tab_statistics where owner = 'TFX_EMS' and STALE_STATS is null and rownum < 9 ;
TABLE_NAME PARTITION_NAME OBJECT_TYPE LAST_ANALY NUM_ROWS
------------------------------ ------------------------------ ------------ ---------- ----------
OD_ORDER_SK_ARCHIVE_HIST <<>> TABLE <<>> <<>>
OD_ORDER_NODE_ANCESTRY_TMP <<>> TABLE <<>> <<>>
OD_RELATED_ORDER_TMP <<>> TABLE <<>> <<>>
OD_ACTIVE_NOTIFICATION SYS_P14162684 PARTITION <<>> <<>>
OD_ACTIVE_NOTIFICATION SYS_P14162685 PARTITION <<>> <<>>
OD_ACTIVE_NOTIFICATION SYS_P14162686 PARTITION <<>> <<>>
OD_ACTIVE_NOTIFICATION SYS_P14162687 PARTITION <<>> <<>>
OD_ACTIVE_NOTIFICATION SYS_P14162688 PARTITION <<>> <<>>
SQL> select count(*) from TFX_EMS.OD_RELATED_ORDER_TMP;
COUNT(*)
----------
0
SQL> select count(*) from TFX_EMS.OD_ACTIVE_NOTIFICATION partition(SYS_P14162688);
COUNT(*)
----------
0
According to the documentation (mentioned below) , dba_tab_statistics.STALE_STATS can have only YES or NO.No word about value set to NULL
https://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_2114.htm#i1591660