Friday, March 23, 2012

Want to know when Statistics on table where gathered Last time and its history?

DBA_TAB_STATS_HISTORY is view which will tell you the time when gather stats job updated the satistics on table. This keep all history for this activity and is very usefull piece of information.

SQL> select to_char(STATS_UPDATE_TIME,'yyyy/mm/dd HH:MI') from DBA_TAB_STATS_HIS
TORY where table_name='T1';
TO_CHAR(STATS_UP
---------------------------------------------------------------------------
2012/02/29 10:01

SQL> exec dbms_stats.gather_table_stats('TEST','T1');

SQL> select to_char(STATS_UPDATE_TIME,'yyyy/mm/dd HH:MI') from DBA_TAB_STATS_HIS
TORY where table_name='T1';
TO_CHAR(STATS_UP
----------------
2012/03/23 12:13
2012/02/29 10:01

SQL> exec dbms_stats.gather_table_stats('TEST','T1');

PL/SQL procedure successfully completed.

SQL> select to_char(STATS_UPDATE_TIME,'yyyy/mm/dd HH:MI') from DBA_TAB_STATS_HIS
TORY where table_name='T1';

TO_CHAR(STATS_UP
----------------
2012/03/23 12:13
2012/03/23 12:15
2012/02/29 10:01


No comments:

Post a Comment