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');