Drop User Take Long Time And Some Time Just Hanging

Hello,

Drop user was taking long time in one of my 12.2 databases. From the 10046 Trace , Below Internal queries were taking long time :

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

DELETE FROM SYS.MVREF$_RUN_STATS
WHERE
REFRESH_ID = :B1

SELECT NUM_MVS_CURRENT
FROM
SYS.MVREF$_RUN_STATS WHERE REFRESH_ID = :B1

As a Workaround, You can perform the below steps.

— If refresh-stats are not needed, it is fine to truncate the following tables:
truncate table mvref$_stats;
truncate table mvref$_run_stats;
truncate table mvref$_change_stats;
truncate table mvref$_stmt_stats;

— To turn off refresh-stats collection in 12.2 database:
exec dbms_mview_stats.set_system_default(‘COLLECTION_LEVEL’, ‘NONE’);
select * from user_mvref_stats_sys_defaults;

Advertisement