11.4. Resource Plans

DRM allocates resources among resource consumer groups based on a resource plan. A resource plan consists of directives specifying how resources should be distributed among resource consumer groups or other resource plans.

Resource plans prioritize resource allocation through the use of levels, with level 1 being the highest priority and level 8 being the lowest.

Simple resource plans are limited to allocating only CPU resources to a small number of consumer groups. However, they are very simple to set up and represent a good starting place if you're new to DRM. Simple resource plans define the resource plan, resource plan directives, and resource consumer groups all with one procedure, whereas complex plans define each separately. Simple resource plans are also classified as single-level resource plans because there are no sub-plans involved.

Complex resource plans can use any of Oracle's predefined resource allocation methods and can include up to 32 consumer groups. Complex resource plans can also contain sub-plans. If sub-plans are defined, the plan would be classified as a multi-level resource plan.

Resource plans have two options regarding the CPU allocation method—EMPHASIS and RATIO—as described in Table 11.10.

The EMPHASIS method is used most often and can be used for either single- or multi-level plans. Under the EMPHASIS method, CPU resource allocations are expressed as percentages in the plan directives.

The RATIO method can be used only on single-level plans (plans that contain directives that allocate CPU resources at level 1 only). Under the RATIO method, the CPU resource allocations are expressed as a weight in the plan directives.

For example, assume a plan containing plan directives for the PAYROLL, MARKETING, and OTHER_GROUPS consumer groups. The plan is defined to use the RATIO method for CPU allocation. Assume that the directives contain the allocations listed in Table 11.11.

The result of these directives will allocate CPU resources using a 10:2:1 ratio. The MARKETING group will get only two CPU cycles for every 10 that the PAYROLL group receives. The OTHER_GROUPS group will get one cycle for every for every two that the MARKETING group receives.

In the following sections, you will learn how to create both simple and complex resource plans. You'll also learn how to update and delete resource plans.

11.4.1. Creating Simple Resource Plans

Simple resource plans, though limited in their abilities and scope, offer an adequate solution for environments with only basic resource management needs. They are distinct from complex plans in that they create a resource plan, resource plan directives, and resource consumer groups in one simple procedure.

Simple resource plans are limited to using only the CPU resource plan directive. This means that the only resource that can be allocated is the CPU. Simple plans also limit the total number of resource groups to eight.

To create a simple resource plan, Oracle provides the DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN procedure, whose parameters are described in Table 11.12.

This procedure allows for the creation of up to eight consumer groups, along with their CPU allocations.

Simple resource plans always use the EMPHASIS CPU resource allocation policy. This means that the value entered for the CPU allocations will be interpreted as a percentage of total CPU. For example, if you want to implement the specifications shown in Table 11.13, a simple resource plan can be created in the example that follows.

SQL> begin
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(

SIMPLE_PLAN => 'DEPARTMENTS',
  CONSUMER_GROUP1 => 'PAYROLL',
  GROUP1_CPU => 50,
  CONSUMER_GROUP2 => 'SALES',
  GROUP2_CPU => 25,
  CONSUMER_GROUP3 => 'MARKETING',
  GROUP3_CPU => 25);
end;
SQL> /

PL/SQL procedure successfully completed.

When a simple plan is created, the results might be somewhat surprising. Table 11.14 shows the finished plan, and you can see that Oracle has added two additional consumer groups to it: SYS_GROUP and OTHER_GROUPS.

SYS_GROUP represents the users SYS and SYSTEM.

OTHER_GROUPS is a required group that must be included in any resource plan. It ensures that users who are not assigned to any group in the active resource plan will still have resources allocated.

Notice also that the final plan is a multi-level plan and the elements that you defined are assigned to the second level. This ensures that members of the SYS_GROUP (at level 1) will have no CPU restrictions. Groups at level 2 will share CPU resources not used by level 1 groups. Likewise, users not assigned to any group in the plan (at level 3) will receive CPU time only after levels 1 and 2 have satisfied their requirements.

11.4.2. Creating Complex Resource Plans

Complex resource plans differ from simple resource plans in how they are defined. Simple plans can create the plan, resource groups, and plan directives in one operation. For complex plans, each of these elements is defined and stored separately. This method offers more flexibility when building resource plans.

This method also allows for the nesting of plans, so one plan can act as a sub-plan of another. When plans are nested in this manner, it is referred to as a multi-level plan.

Creating a plan involves defining the name of the plan, a comment or description regarding the plan, and the methods that the plan will follow when allocating specific resources. Notice that the plan does not determine which resources it will manage. Those are predefined by Oracle. A plan defines only the method it will apply when allocating those resources.

To create a new plan, use the DBMS_RESOURCE_MANAGER.CREATE_PLAN procedure, whose parameters are described in Table 11.15.

As you can see, only the first three parameters (PLAN, COMMENT, and CPU_MTH) actually have any effect on the plan. The others (ACTIVE_SESS_POOL_MTH, PARALLEL_DEGREE_LIMIT_MTH, and QUEUEING_MTH) offer only one option, which is also the default. It is expected that future releases will expand the choices for these parameters.

Therefore, a plan can be created as follows:

SQL> begin
  dbms_resource_manager.create_plan(
    PLAN => 'DAY',
    COMMENT => 'CREATED BY TBB'),
  end;
SQL>/

PL/SQL procedure successfully completed.

To verify that the resource plan was actually created, you can use the DBA_RSRC_PLANS view:

SQL> select plan, num_plan_directives, cpu_method
  2 from dba_rsrc_plans;

PLAN                 NUM_PLAN_DIRECTIVES CPU_METHOD
-------------------- ------------------- ----------
SYSTEM_PLAN                              3 EMPHASIS
INTERNAL_QUIESCE                         2 EMPHASIS
INTERNAL_PLAN                            1 EMPHASIS
DAY                                      1 EMPHASIS

As you can see, the plan was indeed created, and in fact it already has one plan directive assigned to it. Remember that Oracle requires all plans to have a directive for the OTHER_GROUPS resource group. Therefore, Oracle automatically creates this directive for you.

11.4.3. Creating Resource Sub-Plans

A resource sub-plan is created in exactly the same manner as a resource plan. That's because there is no difference between them. A sub-plan is a plan. It only becomes a sub-plan if a higher level plan allocates resources to it (through a resource plan directive).

For example, plan A can allocate resources to consumer groups A and B, and to plan B. Plan B is now classified as a sub-plan. The difference is that a top-level plan always has 100 percent of the resources available to allocate, whereas a sub-plan can allocate only the resources that have been allocated to it by the top-level plan.

11.4.4. Modifying Resource Plans

Resource plans can be modified by using the DBMS_RESOURCE_MANAGER.UPDATE_PLAN procedure. The parameters for this procedure are described in Table 11.16.

Again, keep in mind that only the first three parameters will have any effect on resource plans because there are no other valid options for the others. To verify this, you can use any of the following views:

• V$ACTTVE_SESS_POOL_MTH

• V$PARALLEL_DEGREE_LIMIT_MTH

• V$QUEUEING_MTH

• V$RSRC_PLAN_CPU_MTH

These views display the valid values for each of the resource plan allocation methods. To change the comment on the DAY plan, see the following example:

SQL> exec dbms_resource_manager.update_plan(
  PLAN => 'DAY',
  NEW_COMMENT => 'Plan for scheduled work hours'),

PL/SQL procedure successfully completed.

11.4.5. Deleting Resource Plans

Resource plans can be deleted by using either the DBMS_RESOURCE_MANAGER.DELETE_PLAN procedure or the DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE procedure. The former removes the resource plan but leaves all subordinate objects (consumer groups, plan directives, and sub-plans) intact. The latter removes the resource plan, along with all subordinate objects.

If the DELETE_PLAN_CASCADE procedure attempts to delete a subordinate object that happens to also be part of the currently active plan, the delete will fail and the entire plan will be restored.

The only parameter accepted by these procedures is a valid resource plan name, as shown in this example:

SQL> exec dbms_resource_manager.delete_plan('DAY'),

PL/SQL procedure successfully completed.