Search notes:
Oracle: use EXPLAIN PLAN to find objects whose statistics weren't taken for a certain amount of time
explain plan
set statement_id = 'abc'
for
select
…
from
…;
select
count(*) cnt,
pln.object_owner,
pln.object_name,
pln.object_type,
pln.optimizer,
coalesce(tas.last_analyzed, ins.last_analyzed) last_analyzed,
coalesce(tas.stale_stats , ins.stale_stats ) stale_stats ,
coalesce(tas.user_stats , ins.user_stats ) user_stats ,
coalesce(tas.num_rows , ins.num_rows ) num_rows ,
coalesce(tas.sample_size , ins.sample_size ) sample_size ,
tas.blocks,
tas.empty_blocks,
tas.chain_cnt,
ins.blevel,
ins.leaf_blocks,
ins.distinct_keys,
ins.avg_leaf_blocks_per_key,
ins.table_name table_of_index
from
plan_table pln left join
all_tab_statistics tas on pln.object_type ='TABLE' and
tas.table_name = pln.object_name and
tas.owner = pln.object_owner and
tas.partition_name is null left join
all_ind_statistics ins on pln.object_type ='INDEX' and
ins.index_name = pln.object_name and
ins.owner = pln.object_owner and
ins.partition_name is null
where
pln.object_type is not null and
pln.object_type not in ('VIEW') and
statement_id = 'abc'
group by
pln.object_owner,
pln.object_name,
pln.object_type,
pln.optimizer,
coalesce(tas.last_analyzed, ins.last_analyzed),
coalesce(tas.stale_stats , ins.stale_stats ),
coalesce(tas.user_stats , ins.user_stats ),
coalesce(tas.num_rows , ins.num_rows ),
coalesce(tas.sample_size , ins.sample_size ),
tas.blocks,
tas.empty_blocks,
tas.chain_cnt,
ins.blevel,
ins.leaf_blocks,
ins.distinct_keys,
ins.avg_leaf_blocks_per_key,
ins.table_name
order by
coalesce(tas.last_analyzed, ins.last_analyzed)
;