25
The Database Resource Manager

Oracle8i provides database resource management capability through its Database Resource Manager. This chapter introduces you to its use.

The following topics are included:

• What is the Database Resource Manager?

• Administering the Database Resource Manager

• Creating and Managing Resource Plans

• Managing Resource Consumer Groups

• Enabling the Database Resource Manager

• Putting it All Together: Examples

• Database Resource Manager Views

What is the Database Resource Manager?

Typically, when database resource allocation decisions are left to the operating system, you may encounter the following problems:

• Excessive overhead

  Excessive overhead results from operating system context switching between Oracle servers when the number of servers is high.

• Inefficient scheduling

  The operating system de-schedules Oracle servers while they hold latches, which is inefficient.

• Poor allocation of resources

  The operating system fails to allocate CPU resources appropriately among tasks of varying importance.

• Inability to manage database-specific resources, such as parallel slaves and active sessions

Oracle's Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated.

Specifically, using the Database Resource Manager, you can:

• Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users.

• Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP applications than to batch jobs.

• Limit the degree of parallelism that a set of users can use.

• Configure an instance to use a particular method of allocating resources. A DBA can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.

The elements of Oracle's database resource management, which you define through the Database Resource Manager packages, are described below.

Element	Description
Resource consumer group	User sessions grouped together based on resource processing requirements.
Resource plan	Contains directives that specify which resources are allocated to resource consumer groups.
Resource allocation method	The method/policy used by Database Resource Manager when allocating for a particular resource; used by resource consumer groups and resource plans.
Resource plan directive	Used by administrators to associate resource consumer groups with particular plans and allocate resources among resource consumer groups.

You will learn how to create and use these elements in the remaining sections of this chapter:

• Administering the Database Resource Manager

• Creating and Managing Resource Plans

• Managing Resource Consumer Groups

• Enabling the Database Resource Manager

• Putting it All Together: Examples

• Database Resource Manager Views

  See Also: For detailed conceptual information about the Database Resource Manager, see Oracle8i Concepts. Before attempting to use the Database Resource Manager it is recommended you read the related material contained in that book.

Administering the Database Resource Manager

You must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the Database Resource Manager. Typically, database administrators have this privilege with the ADMIN option as part of the DBA (or equivalent) role.

Being an administrator for the Database Resource Manager allows you to execute all of the procedures in the DBMS_RESOURCE_MANAGER package. These are listed in table Table 25-1, and their use is explained is succeeding sections of this chapter.

Table 25-1 DBMS_RESOURCE_MANAGER Procedures 

Procedure	Description
CREATE_PLAN	Names a resource plan and specifies its allocation methods.
UPDATE_PLAN	Updates a resource plan's comment.
DELETE_PLAN	Deletes a resource plan and its directives.
DELETE_PLAN_CASCADE	Deletes a resource plan and all of its descendents.
CREATE_CONSUMER_GROUP	Names a resource consumer group and specifies its allocation method.
UPDATE_CONSUMER_GROUP	Updates a consumer group's comment.
DELETE_CONSUMER_GROUP	Deletes a consumer group.
CREATE_PLAN_DIRECTIVE	Specifies the resource plan directives that allocate resources to resource consumer groups within a plan or among subplans in a multilevel plan schema.
UPDATE_PLAN_DIRECTIVE	Updates plan directives.
DELETE_PLAN_DIRECTIVE	Deletes plan directives.
CREATE_PENDING_AREA	Creates a pending area (scratch area) within which changes can be made to a plan schema.
VALIDATE_PENDING_AREA	Validates the pending changes to a plan schema.
CLEAR_PENDING_AREA	Clears all pending changes from the pending area.
SUBMIT_PENDING_AREA	Submits all changes to a plan schema.
SET_INITIAL_CONSUMER_GROUP	Sets the initial consumer group for a user.
SWITCH_CONSUMER_GROUP_FOR_SESS	Switches the consumer group of a specific session.
SWITCH_CONSUMER_GROUP_FOR_USER	Switches the consumer group of all sessions belonging to a specific user.

The use of these procedures will be explained later in this chapter.

You may, as an administrator with the ADMIN option, choose to grant the administrative privilege to other users or roles. This is possible using the DBMS_RESOURCE_MANAGER_PRIVS package. This package contains the procedures listed in table Table 25-2.

Table 25-2 DBMS_RESOURCE_MANAGER_PRIVS Procedures 

Procedure	Description
GRANT_SYSTEM_PRIVILEGE	Grants ADMINISTER_RESOURCE_MANAGER system privilege to a user or role.
REVOKE_SYSTEM_PRIVILEGE	Revokes ADMINISTER_RESOURCE_MANAGER system privilege to a user or role.
GRANT_SWITCH_CONSUMER_GROUP	Grants permission to a user, role, or PUBLIC to switch to a specified resource consumer group.
REVOKE_SWITCH_CONSUMER_GROUP	Revokes permission for a user, role, or PUBLIC to switch to a specified resource consumer group.

The following example, grants the administrative privilege to user SCOTT, but does not grant SCOTT the ADMIN option. Therefore, SCOTT will be able to execute all of the procedures in the DBMS_RESOURCE_MANAGER package, but SCOTT cannot use the GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privilege to others.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE -
    (GRANTEE_NAME => 'scott', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', -
     ADMIN_OPTION => FALSE);

You can revoke this privilege using the REVOKE_SYSTEM_PRVILEGE procedure.

Note: The ADMINISTER_RESOURCE_MANAGER system privilege can only be granted or revoked by using the DBMS_RESOURCE_MANAGER_PRIVS package. It cannot be granted through the SQL GRANT or REVOKE statements.

The other procedures in the DBMS_RESOURCE_MANAGER_PRIVS package are discussed in "Granting the Switch Privilege".

See Also: Refer to the Oracle8i Supplied PL/SQL Packages Reference. for detailed information on the Database Resource Manager packages: DBMS_RESOURCE_MANAGER DBMS_RESOURCE_MANAGER_PRIVS

Creating and Managing Resource Plans

Resource plans specify the resource consumer groups belonging to the plan and contain directives for how resources are to be allocated among these groups. You use the DBMS_RESOURCE_MANAGER package to create and maintain the elements of the Database Resource Manager: resource consumer groups, resource plan directives, and resource plans. Plan information is stored in tables in the data dictionary. Several views are available for viewing plan data.

You also use this package to assign an initial consumer group to a user, and to switch the consumer group for a particular session or user. These are discussed in "Managing Resource Consumer Groups".

The following are examples of very simple resource plans. A more complex plan is presented later in this chapter, after it has been explained how to build and maintain the elements.

The first example, shown in Figure 25-1, is of a single-level plan, where the plan allocates resources among resource consumer groups. The Great Bread Company has a plan called GREAT_BREAD that allocates CPU resources among three resource consumer groups. Specifically, SALES is allotted 60% of the CPU time, MARKET[ing] is allotted 20%, and DEVELOP[ment] receives the remaining 20%.

Figure 25-1 A Simple Plan

Text description of the illustration dbrm2.gif.

But a plan can not only contain resource consumer groups, it can also contain other plans, called subplans. Maybe the Great Bread Company chooses to divide their CPU resource as shown in Figure 25-2.

Figure 25-2 A Simple Plan With Subplans

Text description of the illustration dbrm3.gif.

In this case, the GREAT_BREAD plan still allocates CPU resources to the consumer group MARKET, but now it allocates CPU resources to subplans SALES_TEAM and MARKET_TEAM, who in turn allocate resources to consumer groups. Figure 25-2 illustrates a plan schema, which contains a top plan (GREAT_BREAD) and all of its descendents.

It is possible for a subplan or consumer group to have more than one parent (owning plan), but there cannot be any loops in a plan schema. An example of a subplan having more that one parent would be if the Great Bread Company had a night plan and a day plan. Both the night plan and the day plan contain the SALES subplan as a member, but perhaps with a different CPU resource allocation in each instance.

Note: As explained later, the above plans should also contain a plan directive for OTHER_GROUPS. To present a simplified view, however, this plan directive is not shown.

Using the Pending 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:

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

In effect, what is really happening here is that 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. These views are listed in Database Resource Manager Views.

Validating Changes

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

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

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 plan and/or 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 for the EMPHASIS resource allocation method.

5. A plan that is currently being used as a top plan by an active instance cannot be deleted.

6. The plan directive parameter PARALLEL_DEGREE_LIMIT_P1 can appear only in plan directives that refer to resource consumer groups (not other resource plans).

7. There can be no more than 32 resource consumer groups in any active plan schema. Also, at most, a plan can have 32 children. All leaves of a top plan must be resource consumer groups; at the lowest level in a plan schema the plan directives must refer to consumer groups.

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 not covered by the currently active plan is allocated resources as specified by the OTHER_GROUPS directive.

You will receive an error message if any of the above rules are not adhered to. You can then make changes to fix the problem(s) and again call the validate procedure.

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, you call the submit procedure to make your changes active.

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

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.

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

Note: A call to SUBMIT_PENDING_AREA may fail even if VALIDATE_PENDING_AREA succeeds. This can happen if, for example, a plan being deleted is loaded by an instance after a call to VALIDATE_PENDING_AREA, but before a call to SUBMIT_PENDING_AREA.

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 aborted.

EXEC DBMS_RESOURCE_MANAGER.CLEAR_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 following parameters:

Parameter	Description
PLAN	Name of the plan.
COMMENT	Any comment.
CPU_MTH	CPU resource allocation method. EMPHASIS is the default and the only method as of this Oracle release.
MAX_ACTIVE_SESS_TARGET_MTH	Reserved for a future release.
PARALLEL_DEGREE_LIMIT_MTH	The resource allocation method for specifying a limit on the degree of parallelism of any operation. The default is PARALLEL_DEGREE_LIMIT_ABSOLUTE.

Oracle provides one resource plan, SYSTEM_PLAN, that contains a simple structure that may be adequate for some environments. It is illustrated later in "An Oracle Supplied Plan".

Creating a Plan

You create a plan using the CREATE_PLAN procedure. The following creates a plan called GREAT_BREAD.

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.

EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'great_bread');

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.

Creating Resource Consumer Groups

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

Parameter	Description
CONSUMER_GROUP	Name of the consumer group.
COMMENT	Any comment.
CPU_MTH	The CPU resource allocation method for consumer groups. The default is ROUND-ROBIN. This is the only method currently available for resource consumer groups.

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

• OTHER_GROUPS

  This group applies to all sessions that belong to a consumer group that is not part of the currently active plan schema. OTHER_GROUPS must have a resource directive specified in the schema of any active plan.

• 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 (see "Granting the Switch Privilege").

Additionally, two other groups, SYS_GROUP and LOW_GROUP, are provided as part of the Oracle supplied SYSTEM_PLAN that is described in "An Oracle Supplied 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 specify the following parameters:

Parameter	Description
PLAN	Name of the resource plan.
GROUP_OR_SUBPLAN	Name of the consumer group or subplan.
COMMENT	Any comment.
CPU_P1	Specifies CPU percentage at the first level.
CPU_P2	Specifies CPU percentage at the second level.
CPU_P3	Specifies CPU percentage at the third level.
CPU_P4	Specifies CPU percentage at the fourth level.
CPU_P5	Specifies CPU percentage at the fifth level.
CPU_P6	Specifies CPU percentage at the sixth level.
CPU_P7	Specifies CPU percentage at the seventh level.
CPU_P8	Specifies CPU percentage at the eighth level.
MAX_ACTIVE_SESS_TARGET_P1	Reserved for future use.
PARALLEL_DEGREE_LIMIT_P1	Sets a limit on the degree of parallelism for any operation.

The multiple levels of CPU resource allocation provide a means of prioritizing CPU usage within a plan schema. Level 2 gets resources only after level 1 is unable to use all of its resources. Note that no consumer group is allowed to use more than the specified percentage of available CPU. Multiple levels not only provide a way of prioritizing, but they provide a way of explicitly specifying how all primary and leftover resources are to be used.

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, similar to that shown in Figure 25-1, you would also 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 for any operation of 4, 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.

EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'great_bread', -
     GROUP_OR_SUBPLAN => 'develop', NEW_CPU_P1 => 15);

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.

Managing Resource Consumer Groups

Before you enable the Database Resource Manager, you must assign resource consumer groups to users. In addition to providing procedures to create, update, or delete the elements used by the Database Resource Manager, the DBMS_RESOURCE_MANAGER package contains the procedure to assign resource consumer groups to users. It also provides procedures that allow you to temporarily switch a user session to another consumer group.

The DBMS_RESOURCE_MANAGER_PRIVS package, described earlier for granting the Database Resource Manager system privilege, can also be used to grant the switch privilege to another user, who can then alter their own consumer group.

You do not use a pending area for any of the procedures discussed below.

Assigning an Initial Resource Consumer Group

The initial consumer group of a user is the consumer group to which any session created by that user initially belongs. If you have not set the initial consumer group for a user, the user's initial consumer group will automatically be the consumer group DEFAULT_CONSUMER_GROUP.

You must grant switch privilege to a consumer group directly to the user or PUBLIC before that consumer group can be the user's initial consumer group (see "Granting the Switch Privilege"). The switch privilege for the initial consumer group cannot come from a role granted to that user.

The following statements illustrate setting a user's initial consumer group.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('scott', 'sales',-
    TRUE);
EXEC DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('scott', 'sales');

Changing Resource Consumer Groups

There are two procedures, as part of the DBMS_RESOURCE_MANAGER package, that allow administrators to change the resource consumer group of running sessions. Both of these procedures will also change the consumer group of any parallel query slave sessions associated with the coordinator's session. They do not change the initial consumer group.

Switching a Session

The SWITCH_CONSUMMER_GROUP_FOR_SESS causes the specified session to immediately be moved into the specified resource consumer group. In effect, this statement can raise or lower priority. The following statement changes the resource consumer group of a specific session to a new consumer group. The session identifier (SID) is 17, the session serial number (SERIAL#) is 12345, and the session is to be changed to the HIGH_PRIORITY consumer group.

EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345', -
   'high_priorty');

Switching Sessions for a User

The SWITCH_CONSUMER_GROUP_FOR_USER procedure changes the resource consumer group for all sessions with a given user id.

EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('scott', -
    'low_group'); 

Granting the Switch Privilege

Using the DBMS_RESOURCE_MANAGER_PRIVS package, you can grant or revoke the switch privilege to a user, role, or PUBLIC. The switch privilege gives users the privilege to switch their current resource consumer group to a specified resource consumer group. The package also allows you to revoke the switch privilege.

The actual switching is done by executing a procedure in the DBMS_SESSION package. A user who has been granted the switch privilege (or a procedure owned by that user) can use the SWITCH_CURRENT_CONSUMER_GROUP procedure to switch to another resource consumer group. The new group must be one to which the user has been specifically authorized to switch.

Granting the Switch Privilege

The following example grants the privilege to switch to a consumer group. User SCOTT is granted the privilege to switch to consumer group BUG_BATCH_GROUP.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('scott', -         
     'bug_batch_group', TRUE);

SCOTT is also granted permission to grant switch privileges for BUG_BATCH_GROUP to others.

If you grant a user permission to switch to a particular consumer group, then that user can switch their current consumer group to the new consumer group.

If you grant a role permission to switch to a particular resource consumer group, then any users who have been granted that role and have enabled that role can immediately switch their current consumer group to the new consumer group.

If you grant PUBLIC the permission to switch to a particular consumer group, then any user can switch to that group.

If the grant_option argument is TRUE, then users granted switch privilege for the consumer group can also grant switch privileges for that consumer group to others.

Revoking Switch Privileges

The following example revokes user SCOTT's privilege to switch to consumer group BUG_BATCH_GROUP.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP ('scott', -         
     'bug_batch_group');

If you revoke a user's switch privileges to a particular consumer group, then any subsequent attempts by that user to switch to that consumer group will fail. If you revoke the initial consumer group from a user, then that user will automatically be part of the DEFAULT_CONSUMER_GROUP when logging in.

If you revoke a role's switch privileges to a consumer group, then any users who only had switch privilege for the consumer group via that role will not be able to subsequently switch to that consumer group.

If you revoke from PUBLIC switch privileges to a consumer group, then any users who could previously only use the consumer group via PUBLIC will not be able to subsequently switch to that consumer group.

Using the DBMS_SESSION Package to Switch Consumer Group

If granted the switch privilege, users can switch their current consumer group using the SWITCH_CURRENT_CONSUMER_GROUP procedure in the DBMS_SESSION package.

This procedure enables users to switch to a consumer group for which they have the switch privilege. If the caller is another procedure, then this procedure enables users to switch to a consumer group for which the owner of that procedure has switch privileges.

The parameters for this procedure are:

Parameter	Description
NEW_CONSUMER_GROUP	The consumer group being switched to.
OLD_CONSUMER_GROUP	An output parameter. Stores the name of the consumer group being switched from. Can be used to switch back later.
INITIAL_GROUP_ON_ERROR	Controls behavior if a switching error occurs. If TRUE, in the event of an error, the user is switched to the initial consumer group. If FALSE, raise an error.

Enabling the Database Resource Manager

You enable the Database Resource Manager by setting the RESOURCE_MANAGER_PLAN initialization parameter. This parameter specifies the top plan, identifying the plan schema to be used for this instance. If no plan is specified with this parameter, the Database Resource Manager is not activated.

You can also activate or deactivate the Database Resource Manager, or change the current top plan, using the ALTER SYSTEM statement. In this example, the top plan is specified as MYDB_PLAN.

ALTER SYSTEM SET RESOURCE _MANAGER_PLAN = mydb_plan;

An error message is returned if the specified plan does not exist in the data dictionary.

Putting it All Together: Examples

This section provides some examples of resource plan schemas.

A Multilevel Schema

The following statements create a multilevel schema as illustrated in Figure 25-3. They use the default plan and resource consumer group methods.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan', 
   COMMENT => 'Resource plan/method for bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan', 
   COMMENT => 'Resource plan/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan', 
   COMMENT => 'Resource plan/method for bug and mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Online_group', 
   COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Batch_group', 
COMMENT => 'Resource consumer group/method for bug users sessions who run batch jobs');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maintenance_group',
   COMMENT => 'Resource consumer group/method for users sessions who maintain 
   the bug  db');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_users_group', 
   COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Postman_group',
   COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maintenance_group', 
   COMMENT => 'Resource consumer group/method for users sessions who maintain the mail
   db');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 
'Bug_Online_group',
   COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
   PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 
'Bug_Batch_group', 
   COMMENT => 'batch bug users sessions at level 1', CPU_P1 => 20, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 
'Bug_Maintenance_group',
   COMMENT => 'bug maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 100, 
PARALLEL_DEGREE_LIMIT_P1 => 3);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 
'OTHER_GROUPS', 
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0, CPU_P3 => 
100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 
'Mail_Postman_group',
   COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 
'Mail_users_group',
   COMMENT => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 
'Mail_Maintenance_group',
   COMMENT => 'mail maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 20,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 
'OTHER_GROUPS', 
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0, CPU_P3 => 
100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', GROUP_OR_SUBPLAN => 
'maildb_plan', 
   COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', GROUP_OR_SUBPLAN => 
'bugdb_plan', 
   COMMENT => 'all bug users sessions at level 1', CPU_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;

The preceding call to VALIDATE_PENDING_AREA is optional because the validation is implicitly performed in SUBMIT_PENDING_AREA.

Figure 25-3 Multilevel Schema

Text description of the illustration dbrma.gif.

An Oracle Supplied Plan

Oracle provides one default resource manager plan, SYSTEM_PLAN, which gives priority to system sessions. SYSTEM_PLAN is defined as follows:

	CPU Resource Allocation
Resource Consumer Group	Level 1	Level 2	Level 3
SYS_GROUP	100%	0%	0%
OTHER_GROUPS	0%	100%	0%
LOW_GROUP	0%	0%	100%

Two new Oracle provided consumer groups are introduced here. They are defined as:

• SYS_GROUP is the initial consumer group for the users SYS and SYSTEM.

• LOW_GROUP provides a group having lower priority than SYS_GROUP and OTHER_GROUPS in this plan. It is up to you to decide which user sessions will be part of LOW_GROUP. Switch privilege is granted to PUBLIC for this group.

These groups can be used, or not used, and can be modified or deleted.

You can use this simple Oracle provided plan if it is appropriate for your environment.

Database Resource Manager Views

Table 25-3 lists views that are associated with Database Resource Manager:

Table 25-3 Database Resource Manager Views

View	Description
DBA_RSRC_CONSUMER_GROUP_PRIVS	Lists all resource consumer groups and the users and roles to which they have been granted.
DBA_RSRC_CONSUMER_GROUPS	Lists all resource consumer groups that exist in the database.
DBA_RSRC_MANAGER_SYSTEM_PRIVS	Lists all users and roles that have been granted Database Resource Manager system privileges.
DBA_RSRC_PLAN_DIRECTIVES	Lists all resource plan directives that exist in the database.
DBA_RSRC_PLANS	List all resource plans that exist in the database.
DBA_USERS	Contains information about all users of the database. Specifically, for the Database Resource Manager, it contains the initial resource consumer group for the user.
USER_RSRC_CONSUMER_GROUP_PRIVS	Lists all resource consumer groups granted to the user.
USER_RSRC_MANAGER_SYSTEM_PRIVS	Shows all the users that are granted system privileges for the DBMS_RESOURCE_MANAGER package.
USERS_USERS	Contains information about the current user. Specifically, for the Database Resource Manager, it contains the current user's initial resource consumer group.
V$PARALLEL_DEGREE_LIMIT_MTH	Displays all available parallel degree limit resource allocation methods.
V$RSRC_CONSUMER_GROUP	Displays information about active resource consumer groups that can be used for tuning.
V$RSRC_CONSUMER_GROUP_CPU_MTH	Displays all available CPU resource allocation methods for resource consumer groups.
V$RSRC_PLAN	Displays the names of all currently active resource plans.
V$RSRC_PLAN_CPU_MTH	Displays all available CPU resource allocation methods for resource plans.
V$SESSION	Lists session information for each current session. Specifically, lists the name of each current session's resource consumer group.

You can use these views for viewing plan schemas, or you might want to monitor them to gather information for tuning the Database Resource Manager.

See Also: For detailed information about the contents of each of these views, see the Oracle8i Reference.