Limiting Parallel Degree at User Level with Resource Manager

Resource Manager is an enterprise edition feature which will restrict sessions consuming high resources.
We can assign shares to sessions depends on priority of session.
We can restrict degree of parallelism for group of users using high amount of resources.
We can terminate long running SQL statements by using resource manager.

In the following example scenario, we want to add a Resource Manger consumer group for parallel constraint on some users. We want these restrictions to apply to the users we assign to this consumer group.

Steps  :

1. Creating Consumer Group
2. Adding Consumer Group to Resource Plan (if not exists it must be created)
3. Alter Resource Plan
4. Alter User and Add Inıtıal Group
5. Activating Resource Plan &Testing

1 – Creating Consumer Group

— SQL command

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_consumer_group(
    CONSUMER_GROUP => 'LIMIT_PARALLELISM',
    COMMENT        => 'Limiting specific user parallel degree',
    MGMT_MTH       => 'ROUND-ROBIN',
    CATEGORY        => 'OTHER');
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;

2- Adding Consumer Group to Resource Plan

— SQL command

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_plan_directive(
   plan                       => 'DEFAULT_PLAN',
   group_or_subplan           => 'LIMIT_PARALLELISM',
   comment                    => '');
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;

• Verify that you should see in Groups and Subplans newly created item “LIMIT_PARALLELISM”

3- Alter Resource Plan

— SQL command

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.update_plan_directive(
     plan => 'DEFAULT_PLAN',
     group_or_subplan => 'LIMIT_PARALLELISM',
     new_parallel_server_limit => 2,
     new_parallel_degree_limit_p1 => 2);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;

** You can edit parallesim degree regarding to your requirements.

4- Alter User and Add Inıtıal Group

— SQL command

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   'TEST1', 'LIMIT_PARALLELISM', false);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

BEGIN
SYS.DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP (
   'TEST1', 'LIMIT_PARALLELISM');
END;
/

5- Activating Resource Plan & Testing

Before activating resource plan you can see in session browser that there is no limitation for parallelism.

• Activate Resource Plan

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN' scope=both sid='*';

Re-test scenario after activating resource plan , you should see that TEST1 user has limitation in parallelism.

• If you want to deactivate Resource Plan you should execute this command : 

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' scope=both sid='*';