Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,457,266 Views

Determining when statistics were last collected

Posted by decipherinfosys on April 26, 2007

Oracle:

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’
4 /

TABLE_NAME LAST_ANAL
—————————— ———
NEW_TEST 07-MAR-07
PADTEST 08-MAR-07
TEST_COMPUTE 02-APR-07
INPT_CITY 09-APR-07
TEST_AC 06-MAR-07

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’
5 /

INDEX_NAME LAST_ANAL
—————————— ———
PK_NEW_TEST 07-MAR-07
PK_TEST_AC 06-MAR-07
PK_INPT_CITY 09-APR-07

MS SQLServer:

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%’
GO

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.

 
<span>%d</span> bloggers like this: