How can I check the last time stats was run on Oracle without using OEM

Question

I want to check the last time stats was run on my Oracle 10g server. I would normally do this via OEM, but for unrelated reasons OEM is down. Is there some way I can check this using just sqlplus? It would be extra helpful if the output was reasonably formatted.

Answer 1

SELECT LAST_START_DATE 
 FROM DBA_SCHEDULER_JOBS 
WHERE job_name='GATHER_STATS_JOB';

You may have to tweak the date format depending on your SQLPLUS/NLS Settings.

Answer 2

All of the following data dictionary tables have a LAST_ANALYZED column (replace * with USER/ALL/DBA as appropriate:

*_TABLES
*_TAB_PARTITIONS
*_TAB_SUBPARTITIONS
*_INDEXES
*_IND_PARTITIONS
*_IND_SUBPARTITIONS

(There's lots more in the histograms fields, but I'm not going that deep.)

Conversely, ALL_TAB_MODIFICATIONS shows rows inserted/updated/deleted (or the timestamp on which a table/partition/subpartition was truncated) since it had optimizer statistics gathered.