Find missing or stale statistics

Optimizer Statistics are  for the optimizer to choose the best execution plan possible. Sometimes you encounter a sub-optimal plan and need to find out if the tables involved in the statement are up to date. This query will help you find out which tables have been modified significantly since the last statistics gathering.

Script to find missing or stale statistics

select 	m.TABLE_OWNER,
		m.TABLE_NAME,
		m.INSERTS,
		m.UPDATES,
		m.DELETES,
		m.TRUNCATED,
		m.TIMESTAMP as LAST_MODIFIED,		
		round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
		t.num_rows as last_known_rows_number,
		t.last_analyzed
From 	dba_tab_modifications m,
		dba_tables t
where 	m.table_owner=t.owner
and	m.table_name=t.table_name
and 	table_owner not in ('SYS','SYSTEM')
and 	((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;

By default, I filter the tables using the rule of the 10% modified, but you can change this percentage and adapt it to your needs.
Note that this view is not updated in real time for performance reasons, if you want to have the last statistics available, use the following command before executing the query:
Exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

select 	m.TABLE_OWNER,
		'NO' as IS_PARTITION,
		m.TABLE_NAME as NAME,		
		m.INSERTS,
		m.UPDATES,
		m.DELETES,
		m.TRUNCATED,
		m.TIMESTAMP as LAST_MODIFIED,		
		round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
		t.num_rows as last_known_rows_number,
		t.last_analyzed
From 	dba_tab_modifications m,
		dba_tables t		
where 	m.table_owner=t.owner
and		m.table_name=t.table_name
and 	m.table_owner not in ('SYS','SYSTEM')
and 	((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
union
select 	m.TABLE_OWNER,
		'YES' as IS_PARTITION,		
		m.PARTITION_NAME as NAME,		
		m.INSERTS,
		m.UPDATES,
		m.DELETES,
		m.TRUNCATED,
		m.TIMESTAMP as LAST_MODIFIED,		
		round((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0),2) as EST_PCT_MODIFIED,
		p.num_rows as last_known_rows_number,
		p.last_analyzed		
From 	dba_tab_modifications m,		
		dba_tab_partitions p
where 	m.table_owner=p.table_owner
and		m.table_name=p.table_name
and 	m.PARTITION_NAME = p.PARTITION_NAME
and 	m.table_owner not in ('SYS','SYSTEM')
and 	((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0) > 10 or p.last_analyzed is null)
order by 8 desc;

-- To gather statistics with indexes (all together)

select 'exec dbms_stats.gather_table_stats('''||owner||''''||','||''''||table_name||''''||','||'cascade => TRUE);'
from ALL_TAB_STATISTICS
where OWNER in('OWNER')
and global_stats='YES' and stale_stats='YES';

-- Find stale stats Index
select * from all_ind_statistics
WHERE OWNER in('OWNER')
AND stale_stats='YES' and global_stats='TRUE';

If any row will come, gather the respective table as per the follwoing example,

sql> exec dbms_stats.gather_table_stats('owner','table_name',cascade => TRUE);