Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Josef Anwar.

Asked: January 18, 2018 - 4:28 pm UTC

Last updated: January 19, 2018 - 3:22 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

What could be the possible reason(s) why the table statistics (DBA_TAB_STATISTICS) got null or empty i.e. num_rows, last_analyzed columns?

At certain date DBA_TAB_STATISTICS num_rows, last_analyzed columns have values (not empty/null), and after week those statistics were gone. The num_rows and last_analyzed columns were empty or null.


Note:
1. dbms_stats.delete_table_stats was not performed in the table
2. Table was not re-created




and Chris said...

I'm not aware of a way to do this other using dbms_stats or re-creating the table

How certain are you no one ran some form of dbms_stats delete method? e.g. delete_schema:

set null <null>
create table t as 
  select * from dual
  connect by level <= 100;
  
exec dbms_stats.gather_table_stats(user, 't');
select num_rows, last_analyzed from user_tables 
where  table_name = 'T';

NUM_ROWS   LAST_ANALYZED          
       100 18-JAN-2018 09:03:25   

exec dbms_stats.delete_schema_stats(user);

select num_rows, last_analyzed from user_tables 
where  table_name = 'T';

NUM_ROWS   LAST_ANALYZED   
    <null> <null>

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Josef Anwar Panerio, January 18, 2018 - 5:08 pm UTC

I checked the DBA_AUDIT_TRAILS and I have not found any traces the dbms_stats.delete_table_stats was executed, same as the table was re-created.

Josef Anwar Panerio, January 18, 2018 - 5:13 pm UTC

... the extracted data from audit trail was from the period there was values in num_rows and last_analyzed columns, to the date it was noticed it became null.

I also asked the application DBA if they did such commands, and said they have not performed dbms_stats.delete_table_stats, and the table was not re-created.

the only thing they did was dbms_stats.gather_table_stats but they manually terminated it as it took too long, as the table was too big.
Chris Saxon
January 19, 2018 - 11:56 am UTC

but they manually terminated it as it took too long

...sounds like we have a prime suspect for the cause

Josef Anwar Panerio, January 19, 2018 - 2:11 pm UTC

... does manually terminating the gather stats (dbms_stats) can cause the dba_tab_statistics num_rows and last_analyzed columns empty?

I simulate on my local machine (windows 7; DB version 11.2.0.4) and manually terminating the gather stats and it did not empty or null the previous table statistics.
Chris Saxon
January 19, 2018 - 3:22 pm UTC

I didn't think it would either. But it seems the most likely cause given other known reasons have been ruled out...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.