11.5. Resource Plan Directives

Resource plan directives are the key element in creating complex resource plans. As you saw earlier in this chapter, a resource plan by itself does very little, until it has resource plan directives assigned to it. Resource plan directives assign consumer groups to resource plans and define the resource allocations for each. In addition to consumer groups, plan directives can allocate resources to sub-plans.

Resource plan directives work by specifying the owning resource plan, the target consumer group or sub-plan, and the resource allocations assigned to the target. Resources are allocated to the target by setting parameters for the various resource allocation methods.

Resource allocation methods are predefined by Oracle and, as such, are not modifiable. These represent the various methods available to DRM to allocate resources. The following methods are available:

CPU The CPU method specifies how CPU resources are to be allocated among consumer groups or sub-plans. Up to eight levels can be defined, allowing for the prioritization of CPU resources. For example, level 2 gets CPU only if level 1 is unable to utilize all of its allocated CPU. Therefore, level 1 has the highest priority, while level 8 has the lowest priority.

Active session pool with queuing The active session pool with queuing method limits the number of concurrent active sessions available to a consumer group. If the allocated number of sessions is reached, new session requests will be placed in a queue until an active session completes.

Degree of parallelism limit The degree of parallelism limit method specifies the maximum parallel degree for any operation within a consumer group. If a higher degree is specified, it will automatically be altered down to the value specified for this parameter.

Automatic consumer group switching The automatic consumer group switching method allows sessions exceeding certain execution time criteria to be dynamically switched to a different group. For example, if a session exceeds the defined execution time threshold, it can be automatically switched to a lower priority group. This method can also be used to automatically cancel the operation or even kill the offending session.

Canceling SQL and terminating sessions This method specifies that long-running queries or long-running sessions will be automatically terminated if the execution time threshold is exceeded.

Execution time limit The execution time limit method specifies the maximum estimated execution time allowed for any operation. If Oracle estimates that an operation will exceed the specified execution time, it will terminate the operation and return an error. It does this before actual execution begins.

Undo pool The undo pool method specifies the amount of undo that can be generated by a consumer group. If the group exceeds the allocated amount, the current DML statement is terminated and no other group members may perform data manipulation until undo space is freed.

Idle time limit The idle time limit method specifies the maximum amount of time that a session can remain idle. If this limit is exceeded, the session will automatically be terminated. This method can also be limited to terminating only idle sessions that are blocking other sessions.

Resource plan directives can set levels for one or more of these methods for each consumer group or sub-plan. However, only CPU methods may be defined for sub-plans. The other methods are invalid for assigning resources to sub-plans.

In the following sections, you will learn how to create the various types of resource plan directives. You'll also learn how plan directives can be used to monitor and manage long-running operations. Finally, you'll learn to update and delete plan directives.

11.5.1. Creating Resource Plan Directives

To create a resource plan directive, the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure is used. Table 11.17 describes the interface for this procedure.

Table 11.17. CREATE_PLAN_DIRECTIVE Procedure Parameters
ParameterDescription
PLANThe name of the resource plan to which this directive belongs.
GROUP_OR_SUBPLANThe name of the consumer group or sub-plan being allocated resources by this directive.
COMMENTComment or description of plan directive.
CPU_P1This parameter represents either the CPU allocated at level 1 or the ratio weight for CPU resources, depending on the allocation method defined for the resource plan. If the resource plan uses the EMPHASIS allocation method for CPU resources, this parameter defines the percentage of CPU allocated at level 1 for the group/sub-plan. If the plan uses the RATIO allocation method for CPU resources, this parameter defines the weight of CPU usage for the group/sub-plan. The default is NULL, which provides no allocation of CPU resources.
CPU_P2The percentage of CPU allocated at level 2 for the group/sub-plan (if the plan uses the EMPHASIS method). Not applicable for the RATIO method. The default is NULL.
CPU_P3The percentage of CPU allocated at level 3 for the group/sub-plan (if the plan uses the EMPHASIS method). Not applicable for the RATIO method. The default is NULL.
CPU_P4The percentage of CPU allocated at level 4 for the group/sub-plan (if the plan uses the EMPHASIS method). Not applicable for the RATIO method. The default is NULL.
CPU_P5The percentage of CPU allocated at level 5 for the group/sub-plan (if the plan uses the EMPHASIS method). Not applicable for the RATIO method. The default is NULL.
CPU_P6The percentage of CPU allocated at level 6 for the group/sub-plan (if the plan uses the EMPHASIS method). Not applicable for the RATIO method. The default is NULL.
CPU_P7The percentage of CPU allocated at level 7 for the group/sub-plan (if the plan uses the EMPHASIS method). Not applicable for the RATIO method. The default is NULL.
CPU_P8The percentage of CPU allocated at level 8 for the group/sub-plan (if the plan uses the EMPHASIS method). Not applicable for the RATIO method. The default is NULL.
ACTIVE_SESS_POOL_P1Specifies the maximum number of concurrently active sessions for a consumer group or sub-plan. The default is NULL, which means unlimited.
QUEUEING_P1The number of seconds before a job in the inactive session queue times out. The default is NULL, meaning that queued jobs will never time out.
PARALLEL_DEGREE_LIMIT_P1The maximum degree of parallelism that can be defined for any operation. The default is NULL, meaning that no limit is imposed.
SWITCH_GROUPThe consumer group to which this session will be switched if the switch criteria is met. The default is NULL. Other options are CANCEL_SQL, which will kill the query when switch criteria is met, and KILL_SESSION, which will kill the session when the switch criteria is met.
SWITCH_TIMEThe number of seconds that a session can execute an operation before a group switch will occur. The default is NULL, meaning that there is no limit on execution time. After the operation is complete, the session remains in the new consumer group, rather than reverting to its original consumer group.
SWITCH_ESTIMATEDirects Oracle to estimate the execution time for an operation before execution begins. If the estimated time exceeds the value set for SWITCH_TIME, Oracle will perform the switch before execution of the query begins. Valid settings are TRUE or FALSE. The default is FALSE.
MAX_EST_EXEC_TIMEDirects Oracle to estimate the execution time for an operation before execution begins. If the estimated time exceeds the number of seconds defined in this parameter, the operation is not started and an ORA-07455 error is issued. The default is NULL, meaning that no estimate limit is imposed.
UNDO_POOLMaximum kilobytes (KB) of undo that can be generated by the consumer group/sub-plan. The default is NULL, meaning that no limit is imposed.
MAX_IDLE_TIMEThe number of seconds that a session can remain idle before the session is killed. The default is NULL, meaning that no idle time limit is imposed.
MAX_IDLE_BLOCKER_TIMEThe number of seconds that a blocking session can remain idle before the session is killed. (A blocking session is a session that is locking a resource that is needed by another session.) The default is NULL, meaning that no idle time limit is imposed.
SWITCH_TIME_IN_CALLThe number of seconds that a session can execute before a group switch will occur. When the top call has completed, the session will revert to its original consumer group. The default is NULL, meaning that no limit is imposed.

NOTE

Both SWITCH_TIME_IN_CALL and SWITCH_TIME cannot be specified in the same resource directive, because they represent conflicting actions.

The following example creates a resource plan directive for the DAY plan, which limits the parallel degree settings for the DEVELOPERS group:

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',
    COMMENT => 'DEVELOPERS DAY PLAN',
    GROUP_OR_SUBPLAN => 'DEVELOPERS',
    PARALLEL_DEGREE_LIMIT_P1 => '4'),
  end;
SQL> /

PL/SQL procedure successfully completed.

In the following sections, you will learn to create directives, which define sub-plans. You'll also learn to create directives that create multi-level plans. Finally, you'll learn to create plans that use the consumer group switching method to manage long-running operations.

11.5.1.1. Creating Sub-Plan Directives

To create a sub-plan directive, a plan directive is created, which allocates CPU resources to another plan (which is then referred to as a sub-plan). The sub-plan still retains all of its original functionality. However, the total CPU resources it can allocate are limited to those it receives from the top-level plan.

For example, to define a sub-plan under the DAY plan, you would set the GROUP_OR_SUBPLAN parameter to the name of the target plan, as follows:

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',
    COMMENT => 'DEPARTMENTS SUB-PLAN',
    GROUP_OR_SUBPLAN => 'DEPARTMENTS',
    CPU_P2=> 50);
  end;
SQL> /

PL/SQL procedure successfully completed.

In this example, the plan DEPARTMENTS was defined as a sub-plan of the DAY plan and limited to 50 percent of the level 2 CPU resources.

NOTE

Sub-plan directives can allocate only CPU resources to a sub-plan.

11.5.1.2. Creating Multi-Level Plan Directives

Multi-level plan directives are used to prioritize CPU allocation for consumer groups and sub-plans. When a plan directive is created, the parameters CPU_P1 through CPU_P8 determine the level at which the CPU resources will be allocated to the specified group or sub-plan. The total of resources allocated at any one level cannot exceed 100 percent.

Up to eight levels can be specified, with level 1 being the highest priority and level 8 being the lowest. Level 1 recipients share the total available CPU based on their respective CPU_P1 parameter value. Level 2 recipients share only the CPU resources that are not consumed at level 1, and so on.

Consider this example:

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',
    COMMENT => 'SYSTEM USERS',
    GROUP_OR_SUBPLAN => 'SYS_GROUP',
    CPU_P1=> 100);
  end;
SQL> /

PL/SQL procedure successfully completed.

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',

COMMENT => 'DEPARTMENTS SUB-PLAN',
    GROUP_OR_SUBPLAN => 'DEPARTMENTS',
    CPU_P2=> 50);
  end;
SQL> /

PL/SQL procedure successfully completed.

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',

    COMMENT => 'DEVELOPERS GROUP CPU ALLOCATION',
    GROUP_OR_SUBPLAN => 'DEVELOPERS',
    CPU_P2=> 50);
  end;
SQL> /

PL/SQL procedure successfully completed.

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',
    COMMENT => 'OTHER_GROUPS CPU ALLOCATION',
    GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
    CPU_P3=> 100);
  end;
SQL> /

PL/SQL procedure successfully completed.

In this example, four directives are created for the DAY plan. The first directive allocates 100 percent of level 1 CPU resources to the SYS_GROUP group. The second directive allocates 50 percent of level 2 CPU resources to the DEPARTMENTS sub-plan. The third directive allocates the other 50 percent of level 2 CPU resources to the DEVELOPERS consumer group. Finally, the fourth directive allocates 100 percent of level 3 CPU resources to the OTHER_GROUPS group. Figure 11.1 shows a representation of the multi-level DAY plan.

11.5.1.3. Creating Automatic Consumer Group Switching Directives

Plan directives can include options for automatically switching consumer resource groups for sessions that exceed defined thresholds. For example, a directive can dictate that any session that has an operation executing for more than 10 minutes should automatically be switched into a lower priority group. They can also dictate that Oracle will automatically kill the query or even the session when switching thresholds are exceeded.

The key parameters in defining automatic consumer group switching are as follows:

SWITCH_TIME The SWITCH_TIME parameter sets the maximum execution time (in seconds) allowed for any operation. A session violating this threshold is automatically switched to the group defined by the SWITCH_GROUP parameter.

The switch group is generally a group with lower priority so that the long-running operation will be allocated fewer resources. However, the switch group can also be set to the Oracle constants KILL_SESSION or CANCEL_SQL, which would result in the offending session being killed or the offending SQL operation being cancelled.

Once a session has been switched to another group using this method, it will not switch back to its original consumer group, even after the offending operation has completed.

Figure 11.1. Multi-level resource plan

SWITCH_TIME_IN_CALL The SWITCH_TIME_IN_CALL parameter works similarly to the SWITCH_TIME parameter, except that the offending session remains in the switch group only until the operation has completed. After the operation completes, the session reverts to its original consumer group.

NOTE

The SWITCH_TIME and SWITCH_TIME_IN_CALL methods are mutually exclusive. Only one method may be defined in a plan directive.

SWITCH_ESTIMATE The SWITCH_ESTIMATE parameter specifies that the Oracle optimizer should estimate the execution time of an operation before actually executing it. If the estimated time exceeds the value set in the SWITCH_TIME or SWITCH_TIME_IN_CALL parameter, then the consumer group switch will occur prior to execution of the operation.

When a session is switched using this method, it will not revert to its original consumer group if the SWITCH_TIME parameter is set. It will revert to its original consumer group if the SWITCH_TIME_IN_CALL parameter is set.

To create a plan directive that automatically cancels operations that execute for more than one hour, see the following example:

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',

COMMENT => 'LIMIT DEVELOPERS EXECUTION TIME',
    GROUP_OR_SUBPLAN => 'DEVELOPERS',
    SWITCH_GROUP => 'CANCEL_SQL',
    SWITCH_TIME => 3600);
  end;
SQL> /

PL/SQL procedure successfully completed.

To create a plan directive that temporarily moves DEVELOPERS sessions to a lower priority group whenever Oracle estimates that an operation will execute for more than 15 minutes, see this example:

SQL> begin
  dbms_resource_manager.create_plan_directive(
    PLAN => 'DAY',
    COMMENT => 'SWITCH DEVELOPERS TEMPORARILY',
    GROUP_OR_SUBPLAN => 'DEVELOPERS',
    SWITCH_TIME_IN_CALL => 900,
    SWITCH_GROUP => 'LOW_GROUP',
    SWITCH_ESTIMATE => TRUE);
  end;
SQL> /

PL/SQL procedure successfully completed.

This example switches the session to the LOW_GROUP consumer group prior to execution of any operation that Oracle estimates will exceed 15 minutes (900 seconds). When the operation has completed, the session will revert to the DEVELOPERS group.

11.5.1.4. Updating Resource Plan Directives

Resource plan directives can be updated using the DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE procedure. The parameters for this procedure are identical to the parameters for the CREATE_PLAN_DIRECTIVE procedure, except that the prefix NEW_ has been added to all of the modifiable parameters (for example, NEW_COMMENT, NEW_CPU_P1, and so on).

The only parameters that cannot be modified are the PLAN and GROUP_OR_SUBPLAN parameters. All of the others can be updated.

Consider the following example:

SQL> begin
  dbms_resource_manager.update_plan_directive(
    PLAN => 'DAY',
    GROUP_OR_SUBPLAN => 'DEVELOPERS',

NEW_SWITCH_ESTIMATE => FALSE);
 end;
SQL>/

PL/SQL procedure successfully completed.

In this example, the SWITCH_ESTIMATE setting is updated to a value of FALSE. Notice that the parameter used is NEW_SWITCH_ESTIMATE rather than SWITCH_ESTIMATE.

Real World Scenario: Runaway Processes

In my current job, my team administers (among other things) a data warehouse totaling approximately five billion rows. Due to the size of many of the tables, parallel queries drastically reduce runtime for most queries. However, we seem to encounter our share of Oracle bugs, resulting in runaway parallel processes.

For example, a query will spawn eight parallel processes and proceed normally until very near the end of the processing. Then, one process will slowly start spinning CPU cycles. If not caught early, it will eventually consume all CPU and bring the system grinding to a halt.

We've applied several patches that seem to fix the problem, but in reality we just encounter the bug (or a different one with similar effects) less often.

By using Database Resource Monitor, we were able to devise a relatively simple plan that killed sessions if they reached a very high CPU percentage threshold. Now the runaway processes are automatically killed, and the beauty of it is that no DBA involvement is required.


11.5.1.5. Deleting Resource Plan Directives

Resource plan directives can be deleted using the DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE procedure. The only parameters required are the PLAN and GROUP_OR_SUBPLAN parameters, as shown here:

SQL> begin
  dbms_resource_manager.delete_plan_directive(
    PLAN => 'DAY',
    GROUP_OR_SUBPLAN => 'DEVELOPERS'),
  end;
SQL>/

PL/SQL procedure successfully completed.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset