List tables with stale statistics

This morning we were digging into the query performance issue, which was running fine until we created an index. As a quick fix we created a baseline from the good execution plan we found out in the historical stored plans.

Later on, when we reviewed last analyzed date for the tables in questions we thought scheduler job should have collected stats on this table.

We used following procedure to list out any table with the stale stats:

stale_lst dbms_stats.ObjectTab;
OBJLIST => stale_lst);
if ( stale_lst.count> 0 ) then
dbms_output.put_line('Number of objects with Stale Statistics: ' || stale_lst.count);
for i in stale_lst.First .. stale_lst.Last
dbms_output.put_line(rpad(stale_lst(i).ownname, 30, ' ') || stale_lst(i).objname );
end loop;
dbms_output.put_line('There are no table with the stale statistics.');
end if;

Surprisingly none of the tables showed up in the result set. We reviewed dba_tab_modifications table and found out that table has been truncated since last stats collection, so why statistics has not been gathered on this table. We used following query to find out whether statistics are locked on this table:

SELECT table_name, stattype_locked FROM dba_tab_statistics WHERE owner = ‘APP_XXXXX’ and table_name = ‘APP_tab_name’ order by STATTYPE_LOCKED desc nulls last;

Guess what!!! Application administrator who had password for the application schema had locked the statistics for the table. We unlocked the stats. Tables with locked statistics are not being reported as stale.