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