While Dropping a Materialized View – Wait on SYS.MVREF$_CHANGE_STATS

While trying to drop a materialized view, the session waits on the following query:

DELETE FROM SYS.MVREF$_CHANGE_STATS
WHERE
REFRESH_ID = :B2 AND MV_OBJ# = :B1;

This wait is due to usage tracking enabled for MVIEW operations is implemented starting from 12.2 and the delete statement on table “SYS.MVREF$_CHANGE_STATS” goes for a FULL table scan upon the MV is being dropped.

SOLUTION:

As a workaround, we can truncate the following outgrown tables

SQL> truncate table mvref$_stats;
SQL> truncate table mvref$_run_stats;
SQL> truncate table mvref$_change_stats;
SQL> truncate table mvref$_stmt_stats;

To disable refresh-stats collection :

SQL> exec dbms_mview_stats.set_system_default('COLLECTION_LEVEL', 'NONE');
SQL> select * from user_mvref_stats_sys_defaults;

UPDATE on the POST:

Apparently, there is another bug (Doc ID: 2562155.1) causing the MVIEW Stats keep increasing the row count in the mvref$_stats even though the collection level is set to NONE.

The fix is to apply the one-off patch: 30083002

The workaround is to truncate those stats tables before performing a drop operation on the materialized views.

SQL> truncate table mvref$_stats;
SQL> truncate table mvref$_run_stats;
SQL> truncate table mvref$_change_stats;
SQL> truncate table mvref$_stmt_stats;