Menu
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.
638
To see if Oracle thinks the statistics on your table are stale, you want to look at the STALE_STATS column in DBA_STATISTICS. If the column returns “YES” Oracle believes that it's time to re-gather stats. However, if the column returns “NO” then Oracle thinks that the statistics are up-to-date.
The database stores optimizer statistics in the data dictionary. You can access these statistics using data dictionary views. Because objects in a database can change constantly, you must update statistics regularly so that they accurately describe these objects.
You could use ORA_ROWSCN to get the last scn for each block on the table as an *approximate* means of seeing when things happened, or the flashback query syntax to get transaction information, but you are limited to the undo_retention settings on your database.
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.
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
