Check Stale Statistics in Oracle

Find the Table and Index for Stale Statistics

Check the Table Stale Statistics

Column table_name for a30
select LAST_ANALYZED,STATTYPE_LOCKED,table_name from dba_tab_statistics where owner='SCOTT' and stale_stats ='YES';

OR 

select owner,table_name,STALE_STATS,STATTYPE_LOCKED
from dba_tab_statistics where owner='&SCHEMA_NAME' and
table_name='&TABLE_NAME';

Find the Index Stale Statistics

select owner,INDEX_NAME,TABLE_NAME,STALE_STATS from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and
index_name='&INDEX_NAME';

OR

select LAST_ANALYZED,index_name,STALE_STATS from dba_ind_statistics where owner='SCOTT' and stale_stats ='YES';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.