Table stats not updated immediately after "analyze table xxx compute statistics"

Problem
The "analyze table xxx compute statistics" command doesn't update the user_tab_statistics view immediately. 


Cause
In 10g and above, the recommended way to collect table statistics is using "dbms_stats.gather_table_stats(xxx)".  When "dbms_stats.gather_*_stats procedures are executed, they internally flush monitoring information to *_TAB_MODIFICATIONS, *_TAB_STATISTICS, and *_IND_STATISTICS views.  


Solution
1 use "dbms_stats.gather_table_stats(xxx)" to gather latest table stats.
or
2 use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to manually flush memory stats information to the above 3 views. 


Note
1 Since 10g, table level monitoring is enabled by default, it's controlled by STATISTIC_LEVEL system parameter.  If STATISTICS_LEVEL is set to BASIC, then table monitoring is disabled, otherwise it's enabled.
2 You can use following command to check if a table stats is being locked or not:

– shows when stats is not locked the value of stattype_locked is NULL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–

Related Links

1. DBA_TAB_MODIFICATIONS
2. Time of last DML
3. How to lock/unlock statistics on a table?
4. DBA_TAB_MODIFICATIONS - Amin Jaffer
5. Automatic Statistics Gathering
6. Automatic Statistics Gathering - DEV's Weblog
7. oracle dbms_stat与analyze 获取有效的统计信息(3)
8. Custom Statistics Gathering Package for 9i, 10g
9. Gather Statistics with DBMS_STATS by Jeff
10. “dbms_stats.gather_table_stats” vs “analyze table compute stats”