Determining when statistics were last collected
Posted by decipherinfosys on April 26, 2007
Oracle has last_analyzed column in user_tables and user_indexes table which is accessible to schema owner. One can check for both tables and indexes when statistics was last collected.
SQL> SELECT table_name, last_analyzed
2 FROM user_tables
3 WHERE temporary = ‘N’
Similarly, we can also check when statistics was collected for indexes. You can order the results whichever way you want – by last collected date or by table/index.
SQL> SELECT ui.index_name, ui.last_analyzed
2 FROM user_indexes ui
3 INNER JOIN user_tables ut ON ut.table_name = ui.table_name
4 WHERE ut.temporary = ‘N’
In SQLServer one can use nondeterministic system function STATS_DATE to see when was the last time statistics was updated for specific index.
STATS_DATE function takes two arguments table_id and index_id. Following is the SQL followed by output. You can run it from query analyzer or management studio.
SELECT t.table_name, i.name as Index_Name,
STATS_DATE(i.id, i.indid) as Statistics_last_collected
FROM information_Schema.tables t
INNER JOIN sysindexes i ON object_id(t.table_name) = i.id
WHERE t.table_type = ‘BASE TABLE’
AND i.Name is NOT NULL
AND i.Name not like ‘_WA_Sys%’
Table_Name Index_Name Last_Stat_Date
———- ————- ———————–
TEST_GROUP PK_TEST_GROUP 2007-04-25 12:29:17.150
TEST_USER PK_TEST_USER 2007-04-25 12:29:17.150
Sorry, the comment form is closed at this time.