When to gather tables / Index stats?
Applies to Oracle Server - Enterprise Edition - above Version: 10.1.x.x ( 10g/ 11g/ 12c)
Information in this document applies to any platform.
Drilling
Find stale stats Tables / Indexes in Oracle Database:
SELECT * FROM ALL_TAB_STATISTICS
WHERE OWNER in('HR','PAYROLL','CRM','BILLING')
AND stale_stats='YES' and global_stats='TRUE';
Note: Use your required schema.
If the above query returns any table name, then statistics should be gathered immediatly to use plan to minimize the performance.
To gather statistics with indexes (all together):
select 'exec dbms_stats.gather_table_stats('''||owner||''''||','||''''||table_name||''''||','||'cascade => TRUE);'
from ALL_TAB_STATISTICS
where OWNER in('HR','PAYROLL','CRM','BILLING')
and global_stats='YES' and stale_stats='YES';
Find stale stats Index:
select * from all_ind_statistics
WHERE OWNER in('HR','PAYROLL','CRM','BILLING')
AND stale_stats='YES' and global_stats='TRUE';
If any row will come, gather the respective table as per the follwoing example,
sql> exec dbms_stats.gather_table_stats('owner','table_name',cascade => TRUE);
