DBMS_STATS and LAST_ANALYZED question
Particulars:
Oracle 11gR1 (v 11.1.0.7)
Redhat 5.7
Not using partitioning
Trying to force an update on stats for some tables. Using DBMS_STATS i was thinking it would be easy, but for some reason it is not proving to be so.
SQL> exec dbms_stats.gather_table_stats( ownname=>'SCOTT', estimate_percent=>null, cascade=>true, tabname=>'NEW_TABLE');
SQL> select last_analyzed, stale_stats from dba_tab_statistics where owner='SCOTT' and table_name = 'NEW_TABLE';
LAST_ANAL STA
--------- ---
01-NOV-10 NO
I can call dbms_stats.delete_table_stats and the Last_analyzed and Stale_stats columns go away... I would think that using dbms_stats.gather_table_stats would then populate those columns, but they remain empty.
SQL> exec dbms_stats.gather_table_stats( ownname=>'SCOTT', estimate_percent=>null, cascade=>true, tabname=>'NEW_TABLE');
SQL> select last_analyzed, stale_stats from dba_tab_statistics where owner='SCOTT' and table_name = 'NEW_TABLE';
LAST_ANAL STA
--------- ---
{NULL} {NULL}
The ONLY thing I've found that has filled those columns after issuing the delete_table_stats, is to:
SQL> analyze table SCOTT.NEW_TABLE compute statistics;
SQL> select last_analyzed, stale_stats from dba_tab_statistics where owner='SCOTT' and table_name = 'NEW_TABLE';
LAST_ANAL STA
--------- ---
06-FEB-12 NO
From all I know, this is contrary to how my Oracle world should operate... I thought DBMS_STATS was the new way to gather stats, but now am finding that package doesn't seem to be working right. Am I missing something?