Drop Materialized View takes a long time

Recently I had to drop a couple of large Materialized View.
And dropping them was taking a long time, as it tries to drop the data in both source and destination DB. In Source DB it tries to purge the mview log and at destination mview itself.
To accelerate the process I tried truncating the mview tables at destination and also the mview log table at source.

At destination (mview site):

truncate table mview_to_drop;

At source (mview log site):

select master,log_table from dba_mview_logs where master='MVIEW_TO_DROP';
LOG_OWNER MASTER LOG_TABLE
------------ ------------------------------ ------------------------------
SCOTT MVIEW_TO_DROP MLOG$_MVIEW_TO_DROP

truncate table SCOTT.MLOG$_MVIEW_TO_DROP;



Now back at destination site:

drop materialized view SCOTT.MVIEW_TO_DROP;

Materialized view dropped.



This is the fastest way I could find, please let me know if anyone else has any ideas.

How to Setup Resource Manager to Control DB Resources

Step By Step Setup:

Step 1: Create a work area for creation/modification of resource manager objects.

SQL> exec dbms_resource_manager.create_pending_area();

Step 2: Create Resource Manager PLAN

SQL> exec dbms_resource_manager.create_plan(plan => 'PLAN_NAME', comment => 'Comments abt the plan');
e.g.
SQL> exec dbms_resource_manager.create_plan( plan => 'ETL_PLAN', comment => 'PLAN for ETL processes');

Step 3: Create a consumer group which to which the PLAN details would be assigned

SQL> exec dbms_resource_manager.create_consumer_group ( consumer_group => 'COUNSUMER_GROUP_NAME' , comment =>'Comments abt the consumer_group');

e.g.
SQL> exec dbms_resource_manager.create_consumer_group ( consumer_group => 'ETL_GROUP' , comment =>'Check Max Degree of parallelism for ETL process');

Step 4: Assign the Consumer group to the Resource Plan created and define the resource limits

SQL> exec dbms_resource_manager.create_plan_directive( plan => 'ETL_PLAN',
group_or_subplan => 'ETL_GROUP',
comment => '',
cpu_p1 => NULL,
cpu_p2 => NULL,
cpu_p3 => NULL,
cpu_p4 => NULL,
cpu_p5 => NULL,
cpu_p6 => NULL,
cpu_p7 => NULL,
cpu_p8 => NULL,
parallel_degree_limit_p1 => 1,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => NULL,
switch_time => NULL,
switch_estimate => false,
max_est_exec_time => NULL,
undo_pool => NULL,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL);

Examples:
1.) Limit max parallelism

SQL> exec dbms_resource_manager.create_plan_directive ( plan => 'ETL_PLAN',
group_or_subplan =>'ETL_GROUP',
comment => 'Limit CPU resource',
parallel_degree_limit_p1 => 4 );

2.) Disconnect Idle Users after a Predetermined time

SQL> exec dbms_resource_manager.create_plan_directive(plan => 'ETL_PLAN',
group_or_subplan => 'ETL_GROUP',
comment => 'Limit idle time',
max_idle_time => 300);

3.) Control CPU Resources

SQL> exec dbms_resource_manager.create_plan_directive ( plan => 'ETL_PLAN',
group_or_subplan =>'ETL_GROUP',
comment => 'Limit CPU resource',
cpu_p1 => 80 );

4.) Limit max number of concurrently active sessions

SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( plan => 'ETL_PLAN',
group_or_subplan =>'ETL_GROUP',
comment => 'Max 10 Active Sessions with 20 in queue, rest to timeout',
active_sess_pool_p1=>10,
queueing_p1=>20);

Step 5: Validate the PLAN setting.

SQL> exec dbms_resource_manager.validate_pending_area();

You will encounter the below error:

ERROR at line 1:
ORA-29382: validation of pending area failed
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan ETL_PLAN
ORA-06512: at "SYS.DBMS_RMIN", line 437
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 798
ORA-06512: at line 2

Basically for each plan we need to define the plan directives for the OTHER_GROUPS also, what that means is determine limits for all other sessions other than those bound by the consumer group ETL_GROUP.

SQL> exec dbms_resource_manager.create_plan_directive ( plan => 'ETL_PLAN',
group_or_subplan =>'OTHER_GROUPS',
comment => 'Limit CPU resource',
parallel_degree_limit_p1 => 2);

SQL> exec dbms_resource_manager.validate_pending_area();

Step 6: Submit the changes/pending area that was created

SQL> exec dbms_resource_manager.submit_pending_area();

Step 7: Switch the consumer group of the schema/user

SQL> exec dbms_resource_manager.create_pending_area();
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'SCOTT',consumer_group=>'ETL_GROUP',grant_option=>FALSE);

Make is the default/initial consumer group for the user.
SQL> exec dbms_resource_manager.set_initial_consumer_group(user => 'SCOTT',consumer_group =>'ETL_GROUP');

SQL> exec dbms_resource_manager.submit_pending_area();


Others:
Modify Consumer Group:

exec dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'ETL_PLAN', group_or_subplan => 'ETL_GROUP', new_comment => 'ETL_GROUP Change the DOP to 8', new_parallel_degree_limit_p1 => 8);