Table Statistics Tips

How to check for tables which have stale statistics:
select owner,table_name,last_analyzed,sample_size from dba_tables;

Ex: SQL> select owner,table_name,last_analyzed,sample_size from
dba_tables where table_name in ('EMP','DEPT');

How to gather statistics for a schema:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
estimate_percent=>20, cascade=>TRUE);

How to gather statistics for a particular table:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>20, cascade=>TRUE);
( or )
Ex: exec dbms_stats.gather_table_stats(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>NULL,
method_opt=>'FOR ALL INDEXED COLUMNS', degree=>5,
GRANULARITY => 'ALL', CASCADE=>TRUE);

Note: The above gather statistics command gives a more detailed
statistics

To check when a particular table was last analyzed:
Ex: SQL> select table_name, column_name, num_distinct nd,
num_nulls nn, density, last_analyzed from dba_tab_columns
where table_name in ( 'EMP' ) order by table_name, column_name;