My Oracle Support Banner

dba_tab_modification show num_rows=0 although the stats is gathered for the table (Doc ID 1364285.1)

Last updated on MARCH 12, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

GATHER_STATS_JOB is scheduled to run at 22:00, duration of 8 hours. There are tables with num_rows=0 although there are records in the DB.
Further checking shows that GATHER_STATS_JOB was successful, and there is no indication of an error.

- dba_tab_modification show num_rows=0 although the stats is gathered for the table

SQL> select count(*) from ABC;

 COUNT(*)
----------
       39

SQL> select log_date, operation, job_name from all_scheduler_job_log order by job_name , log_date;

LOG_DATE                                                                    OPERATION                      JOB_NAME
--------------------------------------------------------------------------- ------------------------------ -----------------------------------------------------------------
……

06-SEP-11 10.16.13.547874 PM +03:00                                         RUN                            GATHER_STATS_JOB
07-SEP-11 10.13.41.045155 PM +03:00                                         RUN                            GATHER_STATS_JOB
08-SEP-11 10.12.13.457540 PM +03:00                                         RUN                            GATHER_STATS_JOB
09-SEP-11 10.25.09.964891 PM +03:00                                         RUN                            GATHER_STATS_JOB
10-SEP-11 06.07.14.961615 AM +03:00                                         RUN                            GATHER_STATS_JOB

 

 SQL> Select table_name,to_char(last_analyzed,'yyyy-mm-dd hh24:mi') as last_analyzed, num_rows, stattype_locked , stale_stats 

          from dba_tab_statistics where owner='ABC' and num_rows=0;

TABLE_NAME          LAST_ANALYZED      NUM_ROWS     STATTYPE_LOCKED  STALE_STATS

------------------------ ---------------- ---------- ----- -----------------------------------------------------------------

EXC                         2011-09-03 06:07          0                                                               YES

XYZ                        2009-12-30 06:51          0                                                                NO

EDP                         2011-09-03 06:07          0                                                               YES    

ABC                        2011-09-03 06:07          0                                                                YES   

The stale is yes which means that within the next stats window, statistics should be collected.

Cause

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
Symptoms
Cause
Solution
References


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