When to gather tables / Index stats?

~~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
sql >
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);

Hope sure this document will help and you will have good performance in your production database.

DBAshhh...................njy............