STATS of table is STALE after gathering stats in Oracle

 STATS of table is STALE after gathering stats in Oracle

I tried to gather the stats of the table but after that gather still its in STALE state.

Solution

To Overcome the STALE state, we need to change the option of TABLE from gather Auto to gather stats

1. Check the option of the table from gather stats package.

select dbms_stats.get_prefs('OPTIONS', 'HR', 'EMPLOYEES') OPTIONS from dual;
OPTIONS
-----------
GATHER AUTO

2. Change the option from GATHER AUTO to GATHER

exec dbms_stats.set_table_prefs('HR', 'EMPLOYEES', 'OPTIONS', 'GATHER');

3. Check the option of the table from gather stats package.

select dbms_stats.get_prefs('OPTIONS', 'HR', 'EMPLOYEES') OPTIONS from dual;
OPTIONS
-----------
GATHER