Does your tables need updated statistics for the optimizer? Here’s a couple of views that could be useful in the gathering of information.

First – the obvious place to check is user_tab_statistics:

select *
from user_tab_statistics
where stale_stats = 'YES';

It will give information down to the (sub)partition level if the table is partitioned or subpartitioned.

If I have many partitions or subpartitions a quick group by gives me an easier summary:

select table_name, count(*) sub_part
from user_tab_statistics
where stale_stats = 'YES'
group by table_name
order by table_name;

A similar view exists for indexes, user_ind_statistics:

select *
from user_ind_statistics
where stale_stats != 'NO';

Or – the same summary as for tables:

select table_name, index_name, count(*)
from user_ind_statistics
where stale_stats != 'NO'
group by table_name, index_name
order by table_name, index_name;

Typically the number of rows returned will vary depending on the activity in your system. Small tables or partitions with lots of DML will be more likely to get tagged as having stale statistics.

If you want more info on the activity in your system, the view user_tab_modifications can help:

select *
from user_tab_modifications;

It shows tables/partitions with DML activity, and the contents of the view will be affected by the statistics gathering.

Without digging further into this, it’s not unlikely that this data is one of the factors deciding whether the value for stale_stats is listed as YES or NO.

And – the summary edition of user_tab_modifications:

select table_name, sum(inserts) i, sum(updates) u, sum(deletes) d, count(*) sub_part
from user_tab_modifications
group by table_name
order by 1 ;