1

How to know the statistics are up to date, is there any way to find when the last statistics were calculated?

Do I need to just look at the stale_stats and last_analyzed column of the dba_tab_statistics view?

general46
  • 800
  • 6
  • 16
  • Yes that's the usual way to proceed (`last_analyzed` can also be found in `all_tables`). Are you disatisfied with the information that you are seeing in there? – GMB Oct 22 '19 at 21:10
  • I'm not sure whether it is the only way or not. @GMB – general46 Oct 22 '19 at 21:17
  • 1
    Since this is tagged 12c, unless you've changed the default configuration, Oracle has a job that automatically gathers statistics overnight on any tables it judges to have stale statistics. Unless you have tables whose usage patterns don't work with that job (i.e. a staging table that is empty overnight when stats are gathered that gets loaded with millions of rows as part of a daytime ETL run), you probably don't need to be overly concerned with this. – Justin Cave Oct 22 '19 at 21:50

1 Answers1

1

You can find this in the XXX_TABLES views (e.g. DBA_TABLES, USER_TABLES, ALL_TABLES). The column you're looking for is LAST_ANALYZED, so for example

SELECT OWNER, TABLE_NAME, LAST_ANALYZED
  FROM USER_TABLES

dbfiddle here

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110