Oracle Database Resource Management (DRM)

Oracle Database Resource Manager is a pretty useful utility for DBAs. It simplifies distribution of resources(CPU time, idle time…) between users.

Three main elements are included in DRM:

1. Resource Consumer Groups
Resource Consumer Groups are groups to which user sessions can be assigned.
2. Resource Plans
It consists of Resource Plan Directives which decide how resources can be allocated for Resource Consumer Groups(same as user sessions).
3. Resource Plan Directives
As I said previously, it decides how resources should be distributed between users.

Resources are:
CPU usage, idle time, process execution time, number of sessions, etc.

After a brief summary about what DRM is, let’s use it in the example.

0. Privilege Needed for to use DRM

You must have ADMINISTER_RESOURCE_MANAGER system privilege to use it. This privilege is the default for DBA role.

You know how to grant privileges to users, by the following way:

grant privilege to user;

But this privilege(ADMINISTER_RESOURCE_MANAGER) can’t be granted by this way, you must use the following syntax:

begin
 dbms_resource_manager_privs.grant_system_privilege(
    grantee_name   => 'hr',
    privilege_name => 'administer_resource_manager',
    admin_option   => false);
end;

1. Creating Pending Area

First step is to create a Pending Area. This is like a room in which resource management objects are created and validated before their activation.

begin
 dbms_resource_manager.create_pending_area();
end;

2. Creating Resource Consumer Groups

For the first point you must define unique group name and the description.

begin
 dbms_resource_manager.create_consumer_group(
    consumer_group => 'group1',
    comment => 'This is the first resource consumer group');
end;

begin
 dbms_resource_manager.create_consumer_group(
    consumer_group => 'group2',
    comment => 'This is the second resource consumer group');
end;

–To see what resource consumer groups are in your database, query the following:

select *
from dba_rsrc_consumer_groups

The result may surprise you, because there will be some additional groups. By default there are four resource consumer groups:

DEFAULT_CONSUMER_GROUP – Users who are not assigned to some initial group, this will be default.

OTHER_GROUPS – Users who are assigned to groups, that are not part of the currently active plan, will become a member of this group. You can not assign users to this group manually.

SYS_GROUP – It is used by Oracle-provided SYSTEM_PLAN.

LOW_GROUP – It is used by Oracle-provided SYSTEM_PLAN.

3. Creating Resource Plans

begin
 dbms_resource_manager.create_plan(
    plan=> 'plan1',
    comment => 'This is the first resource plan');
end;

begin
 dbms_resource_manager.create_plan(
    plan=> 'plan2',
    comment => 'This is the second resource plan',

    cpu_mth=> 'ratio');
end;

Note:
cpu_mth is a CPU allocation method, the valid values are:

EMPASIS -Allocated amount is considered as a percentage(the default).

RATIO     -Allocated amount is considered as a ration of total CPU.

4. Creating Resource Plans Directives

–Creating Resource Plan Directives for plan1

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan1',
    group_or_subplan => 'sys_group',
    comment => 'Alocating CPU resource for sys_group',
    cpu_p1 =>90 );
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan1',
    group_or_subplan => 'group1',
    comment => 'Alocating CPU resource for group1',
    cpu_p1 =>10 );
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan1',
    group_or_subplan => 'other_groups',
    comment => 'Alocating CPU resource for other_groups',
    cpu_p2 =>100 );
end;

Note: 90%  of CPU resources is allocated on level 1 for sys_group, and 10% for group1.
100% of CPU resources is allocated on level 2 for other_group.

Groups at level 2 will share CPU resources not used by level 1 groups. And groups at level 3 will receive CPU time after level 1 and level 2.

–Creating Resource Plan Directives for plan2

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan2',
    group_or_subplan => 'sys_group',
    comment => 'Alocating CPU resource for sys_group',
    cpu_p1 =>4);
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan2',
    group_or_subplan => 'group2',
    comment => 'Alocating CPU resource for group2',
    cpu_p1 =>4 );
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan2',
    group_or_subplan => 'other_groups',
    comment => 'Alocating CPU resource for other_groups',
    cpu_p2 =>1 );
end;

Note: CPU allocation ratio for plan2 will be 4:4:1.

5. Validating the Pending Area

Validation ensures that:
1.Objects defined in a pending area exist,
2.All plan directives refer to either plans or resource groups(Plan directive can refer to plan(this plan is considered as a sub-plan) or consumer group).
3.Allocation percentage for a single leven can not exceed to 100%.
4. No deletion is allowed for top plans being used by an active instance.
5.An active plan schema can contain no more than 32 resource consumer groups.
6. Plan names cannot conflict with group names.
7.There must be a plan directive for OTHER_GROUPS.
8.No plan schema can contain loop.

If any of them are violated, error will occur and validating procedure fails.

begin
 dbms_resource_manager.validate_pending_area;
end;

6. Submitting the Pending Area

Submitting the pending area does the following steps: validates,submits and clears pending area. We validated it before submitting, because it is the preferable choice to check that everything is OK, before submit it(Move it to the data dictionary).
After submitting pending area, created plans are not activated! It just prepares plans for activation.

begin
 dbms_resource_manager.submit_pending_area;
end;

7. Activating the Resource Plans

There are two ways, to activate plan:

1. ALTER SYSTEM
2. RESOURCE_MANAGER_PLAN

The first method:

alter system set resource_manager_plan='plan1' [scope=both];

Note: [scope=both] is optional, you can choose it if you want to make changes permanent. The option both means memory and spfile(database must be started by spfile).

The second method:

In the init.ora file add the following:

RESOURCE_MANAGER_PLAN=plan1

8. Switching between Resource Plans

alter system set resource_manager_plan='plan2' [scope=both];

System altered. 

alter system set resource_manager_plan='plan1' [scope=both]; 

System alered.