Gather Fixed Object Stats Hangs on X$DURABLE_SHARDED_SUBS

SYMPTOM

On 12.1.0.2 database, try to gather fixed table statistics with below command, it hangs at object “X$DURABLE_SHARDED_SUBS”.  Also hangs at select couint(*) from X$DURABLE_SHARDED_SUBS.

SQL>EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()

SQL>select count(*) from X$DURABLE_SHARDED_SUBS;

CAUSES

It is a bug for this issue, it is supposed to be fixed in 12.2.0.X.

WORKAROUND

Lock the statistics of this fixed table:

SQL>exec dbms_stats.lock_table_stats('SYS','X$DURABLE_SHARDED_SUBS');

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.