Creating Complex Resource Plans - Oracle 10g

This section describes the actions and DBMS _RESOURCE _MANAGER procedures that you can use when your situation requires that you create more complex resource plans. It contains the following sections:

• Using the Pending Area for Creating Plan Schemas
• Creating Resource Plans
• Creating Resource Consumer Groups
• Specifying Resource Plan Directives

Using the Pendin Area for Creating Plan Schemas

The first thing you must do to create or modify plan schemas is to create a pending area. This is a scratch area allowing you to stage your changes and to validate them before they are made active.

Creating a Pending Area

To create a pending area, you use the following statement:

In effect, you are making the pending area active and "loading" all existing, or active, plan schemas into the pending area so that they can be updated or new plans added. Active plan schemas are those schemas already stored in the data dictionary for use by the Database Resource Manager. If you attempt to update a plan or add a new plan without first activating (creating) the pending area, you will receive an error message notifying you that the pending area is not active. Views are available for inspecting all active resource plan schemas as well as the pending ones.

Validating Changes

At any time when you are making changes in the pending area you can call the validate procedure as shown here.

This procedure checks whether changes that have been made are valid. The following rules must be adhered to, and are checked by the validate procedure:

1. No plan schema can contain any loops.
2. All plans and resource consumer groups referred to by plan directives must exist.
3. All plans must have plan directives that point to either plans or resource consumer groups.
4. All percentages in any given level must not add up to greater than 100.
5. A plan that is currently being used as a top plan by an active instance cannot be deleted.
6. The following plan directive parameters can appear only in plan directives that refer to resource consumer groups (not other resource plans):
   • PARALLEL_DEGREE_LIMIT_P1
   • ACTIVE_SESS_POOL_P1
   • QUEUEING_P1
   • SWITCH_GROUP
   • SWITCH_TIME
   • SWITCH_ESTIMATE
   • MAX_EST_EXEC_TIME
   • UNDO_POOL
   • MAX_IDLE_TIME
   • MAX_IDLE_BLOCKER_TIME
   • SWITCH_TIME_IN_CALL
7. There can be no more than 32 resource consumer groups in any active plan schema. Also, at most, a plan can have 3 children.
8. Plans and resource consumer groups cannot have the same name.
9. There must be a plan directive for OTHER _GROUPS somewhere in any active plan schema. This ensures that a session which is not part of any of the consumer groups included in the currently active plan is allocated resources (as specified by the OTHER_GROUPS directive).

You will receive an error message if any of the preceding rules are violated. You can then make changes to fix any problems and call the validate procedure again. It is possible to create "orphan" consumer groups that have no plan directives referring to them. This allows the creation of consumer groups that will not currently be used, but may be part of some plan to be implemented in the future.

Submitting Changes

After you have validated your changes, call the submit procedure to make your changes active.

The submit procedure also performs validation, so you do not necessarily need to make separate calls to the validate procedure. However, if you are making major changes to plan schemas, debugging problems is often easier if you incrementally validate your changes. No changes are submitted (made active) until validation is successful on all of the changes in the pending area.

The SUBMIT _PENDING _AREA procedure clears (deactivates) the pending area after successfully validating and committing the changes.

Clearing the Pending Area

There is also a procedure for clearing the pending area at any time. This statement causes all of your changes to be cleared from the pending area:

You must call the CREATE _PENDING _AREA procedure before you can again attempt to make changes.

Creating Resource Plans

When you create a resource plan, you can specify the parameters shown in the following table. The first parameter is required; the remainder are optional .

Oracle Database provides one resource plan, SYSTEM _PLAN, that contains a simple structure that may be adequate for some environments. You create a plan using the CREATE _PLAN procedure. The following creates a plan called great _bread. You choose to use the default resource allocation methods.EXEC DBMS _RESOURCE _MANAGER.CREATE _PLAN(PLAN => 'great_bread', -COMMENT => 'great plan');

Updating a Plan

Use the UPDATE _PLAN procedure to update plan information. If you do not specify the arguments for the UPDATE _PLAN procedure, they remain unchanged in the data dictionary. The following statement updates the COMMENT parameter.

EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'great_bread', - NEW_COMMENT => 'great plan for great bread'); Deleting a Plan

The DELETE _PLAN procedure deletes the specified plan as well as all the plan directives associated with it. The following statement deletes the great_bread plan and its directives.

The resource consumer groups themselves are not deleted, but they are no longer associated with the great_bread plan. The DELETE_PLAN_CASCADE procedure deletes the specified plan as well as all its descendants (plan directives, subplans, resource consumer groups). If DELETE_PLAN_CASCADE encounters an error, it will roll back, leaving the plan schema unchanged.

Using the Ratio Policy

The RATIO policy is a single -level CPU allocation method. Instead of percentages, you specify numbers corresponding to the ratio of CPU you want to give to the consumer group. For example, given three consumer groups GOLD_CG, SILVER_ CG, and BRONZE_CG, assume that we specify the following plan directives:

The ratio of CPU allocation would be 10:5:2:1 for the GOLD_CG, SILVER_CG, BRONZE_CG, and OTHER_GROUPS consumer groups, respectively. If sessions exists only in the GOLD_CG and SILVER_CG consumer groups, then the ratio of CPU allocation would be 10:5 between the two groups.

Creating Resource Consumer Groups

When you create a resource consumer group, you can specify the following parameters:

There are two special consumer groups that are always present in the data dictionary, and they cannot be modified or deleted. These are:

• DEFAULT_CONSUMER_GROUP
  

  This is the initial consumer group for all users/sessions that have not been explicitly assigned an initial consumer group. DEFAULT_CONSUMER_GROUP has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this consumer group.

• OTHER_GROUPS
  

  This consumer group cannot be explicitly assigned to a user. OTHER _GROUPS must have a resource directive specified in the schema of any active plan. This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema, including DEFAULT _CONSUMER _GROUP.

  Additionally, two other groups, SYS_GROUP and LOW_GROUP, are provided as part of the Oracle -supplied SYSTEM_PLAN .

Creating a Consumer Group

You create a consumer group using the CREATE _CONSUMER _GROUP procedure. The following creates a consumer group called sales. Remember, the pending area must be active to execute this statement successfully.

EXEC DBMS_RESOURCE_MANAGER. CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'sales', - COMMENT => 'retail and wholesale sales');
Updating a Consumer Group

Use the UPDATE_CONSUMER_GROUP procedure to update consumer group information. If you do not specify the arguments for the UPDATE_CONSUMER_ GROUP procedure, they remain unchanged in the data dictionary.

Deleting a Consumer Group

The DELETE _CONSUMER _GROUP procedure deletes the specified consumer group. Upon deletion of a consumer group, all users having the deleted group as their initial consumer group will have the DEFAULT _CONSUMER _GROUP set as their initial consumer group. All currently running sessions belonging to a deleted consumer group will be switched to DEFAULT_CONSUMER_GROUP.

Specifying Resource Plan Directives

Resource plan directives assign consumer groups to resource plans and provide the parameters for each resource allocation method. When you create a resource plan directive, you can specify the following parameters

Creating a Resource Plan Directive

You use the CREATE _PLAN _DIRECTIVE to create a resource plan directive. The following statement creates a resource plan directive for plan great _bread.

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', - GROUP_OR_SUBPLAN => 'sales', COMMENT => 'sales group', - CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 => 4); To complete the plan, execute the following statements: BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', GROUP_OR_SUBPLAN => 'market', COMMENT => 'marketing group', CPU_P1 => 20); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', GROUP_OR_SUBPLAN => 'develop', COMMENT => 'development group', CPU_P1 => 20); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'this one is required', CPU_P1 => 0, CPU_P2 => 100); END; In this plan, consumer group sales has a maximum degree of parallelism of 4 for any operation, while none of the other consumer groups are limited in their degree of parallelism. Also, whenever there are leftover level 1 CPU resources, they are allocated (100%) to OTHER_GROUPS

Updating Resource Plan Directives

Use the UPDATE_PLAN_DIRECTIVE procedure to update plan directives. This example changes CPU allocation for resource consumer group develop.

If you do not specify the arguments for the UPDATE _PLAN _DIRECTIVE procedure,

they remain unchanged in the data dictionary.

Deleting Resource Plan Directives

To delete a resource plan directive, use the DELETE _PLAN _DIRECTIVE procedure

How Resource Plan Directives Interact

If there are multiple resource plan directives that refer to the same consumer group, then the following rules apply for specific cases:

1. The parallel degree limit for the consumer group will be theminimumof all the incoming values.
2. The active session pool for the consumer group will be thesumof all the incoming values and the queue timeout will be theminimumof all incoming timeout values.
3. If there is more than one switch group and more than one switch time, the Database Resource Manager will choose themost restrictiveof all incoming values. Specifically:
   • SWITCH_TIME =min(all incoming over_switch_time values)
   • SWITCH_ESTIMATE = TRUE overrides SWITCH_ESTIMATE = FALSE
4. If a session is switched to another consumer group because it exceeds its switch time, that session will execute even if the active session pool for the new consumer group is full.
5. The maximum estimated execution time will be the most restrictive of all incoming values. Specifically:
   MAX_EST_EXEC_TIME = min (all incoming MAX_EST_EXEC_TIME values