Limiting Parallel Degree at User Level with Oracle Resource Manager

18 January 2022 0 Comment

Oracle Database Resource Manager allows you to manage multiple workloads within a database that compete for system and database resources.

The Resource Manager helps overcome these issues by allowing the database to gain greater control over how hardware resources are allocated. In an environment with multiple concurrent user sessions running jobs with different priorities, not all sessions should be treated equally. Resource Manager allows you to group sessions based on session attributes and then allocate resources to these groups in a way that optimizes hardware usage for your application environment.

Database IDE: Toad

Steps :

Creating Consumer Group
Adding Consumer Group to Resource Plan (if not exists it must be created)
Alter Resource Plan
Alter User and Add Inıtıal Group
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;
/