You Asked

I am trying to find all stale objects. As I understand there are two ways and both should return same result.

Before starting I first did a flush monitoring

begin  
    dbms_stats.flush_database_monitoring_info;  
    end;  
    /

Then I used the first method that is to query *_tab_statistics table

select * from ALL_TAB_STATISTICS a where STALE_STATS = 'YES'

Then I used second method that is to pass "LIST STALE" in gather_schema_stat proc

declare  
lv_object_list dbms_stats.objecttab;  
p_filter_list dbms_stats.objecttab := dbms_stats.objecttab();  
begin  
dbms_stats.gather_schema_stats(ownname => 'XXXXX',  
                                     options => 'LIST STALE',  
                                     objlist => lv_object_list,  
                                     force => TRUE);  
FOR i IN 1 .. lv_object_list.count  
LOOP  
  dbms_output.put_line(lv_object_list(i).objName);  
END LOOP;                                       
end;  
/

and Chris said...

To get the up-to-date information in *_tab_statistics, you need to flush the stats as you've done. Note that calling dbms_stats does this internally :

create table t (
  x int primary key, y int
);

insert into t values (0, 0);
commit;
exec dbms_stats.gather_table_stats(user, 't');

insert into t
  select rownum, 1 from dual connect by level <= 100;
commit;

select table_name from user_TAB_STATISTICS a where STALE_STATS = 'YES' ; 

TABLE_NAME                    
------------------------------
PEOPLE

declare  
lv_object_list dbms_stats.objecttab;  
p_filter_list dbms_stats.objecttab := dbms_stats.objecttab();  
begin  
dbms_stats.gather_schema_stats(ownname => 'chris',  
                                     options => 'LIST STALE',  
                                     objlist => lv_object_list,  
                                     force => TRUE);  
FOR i IN 1 .. lv_object_list.count  
LOOP  
  dbms_output.put_line(lv_object_list(i).objName);  
END LOOP;                                       
end;  
/

PEOPLE
T

select table_name from user_TAB_STATISTICS a where STALE_STATS = 'YES' ; 

TABLE_NAME                    
------------------------------
PEOPLE
T

So assuming you have flushed the stats, are you encountering a case where these return different results?

Rating

(1 rating)

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

Comments

A reader, July 12, 2017 - 6:15 pm UTC

Yes I did flush the stats using dbms_stats.flush_database_monitoring_info. we do see difference even after this.

Then we manually gathered stats for a table listed by *_tab_statistics, after which we are not seeing that table as stale in *_tab_statistics

July 13, 2017 - 9:49 am UTC

If you've gathered stats for a table, then it isn't stale anymore! So it won't appear in *_tab_statistics.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

DBMS_STATS

Difference between stale object result from *_tab_statistics and gather_schema_stat with "LIST STALE"

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

DBMS_STATS