Oracle8i Administrator's Guide
Release 2 (8.1.6)

Part Number A76956-01

Library

Product

Contents

Index

Go to previous page Go to next page

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?

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

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:

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

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 dbrm2.gif follows.

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 dbrm3.gif follows.

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:

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. 

See Also:

For more information about the DBMS_SESSION package, see the Oracle8i Supplied PL/SQL Packages Reference. 

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 dbrma.gif follows.

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:

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. 


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index