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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.