DB-hub Technology Oracle How to identify STALE Statistics on a table?

How to identify STALE Statistics on a table?

Check dba_tables last analyzed column.

select owner,table_name,last_analyzed From dba_Tables;

Check dba_tab_statistics

select table_name, stale_stats, last_analyzed 
from dba_tab_statistics 
where stale_stats='YES';

Call DBMS_STATS.GATHER_SCHEMA_STATS with option

LIST STALE==>Works similar to option 2. Returns list of stale objects by query dba_tab_modifications table).

LIST EMPTY==>Returns list of objects with no stats

Query DBA_TAB_COL_STATISTICS to check for stale column statistics.
Check for NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,NUM_NULLS and compare it with actual data from table. NUM_DISTINCT can be validated by query SELECT COUNT(DISTINCT COLUMN_NAME) FROM TABLE_NAME. LOW/MIN_VALUE can be validated with SELECT MIN(COLUMN_NAME),MAX(COLUMN_NAME).

select * from DBA_TAB_COL_STATISTICS where owner ='SCHEMA' and table_name='TABLE_NAME';

Leave a Reply

您的邮箱地址不会被公开。 必填项已用 * 标注

Related Post