Check update statistics time in Oracle

Table and index statistics should be up to date to enable the database optimizer to choose optimal query plans. When in doubt, SQLs can be used to check the last update statistics collection time.

Symptoms

For tables, the following information is retrieved:
  • table_name: Name of table
  • num_rows: number of rows in table
  • sample_size: what is the detail level of statistics collected
  • last_analyzed: last update statistics timestamps
and in addition for indexes:
  • index_name: name of index
  • table_name: table on which index is defined
  • distinct_keys: number of different keys
  • status: index status
    Note: The value should show “VALID”, otherwise it is an indicator that index needs to be rebuild

To check table statistics use:

select owner,
table_name,
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tables where owner='<SCHEMANAME>'
order by owner

To check for index statistics use:

select index_name,
table_name,
num_rows,
sample_size,
distinct_keys,
last_analyzed,
status
from user_indexes where table_owner='<SCHEMANAME>'
order by table_owner;