11
Using the Database Resource Manager

This chapter describes how to use the Database Resource Manager and includes the following topics:

Introduction

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

Excessive overhead

Excessive overhead results from OS context switching between Oracle servers when the number of servers is high.
Inefficient scheduling

The OS de-schedules Oracle servers while they hold latches, which is inefficient.
Poor resource partitioning

The OS fails to partition 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 allocates resources based on a resource plan that is specified by database administrators. Database Resource Manager ultimately offers you more control over resource management decisions and addresses the problems caused by inefficient OS scheduling.

Administrators use the basic elements of Database Resource Manager described in Table 11-1.

Table 11-1 Database Resource Manager Elements

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 partition resources among resource consumer groups

See Also: For detailed conceptual information about the Database Resource Manager, see Oracle8i Concepts.

Using Database Resource Manager Packages

To create resource plans and resource consumer groups, use the following packages:

DBMS_RESOURCE_MANAGER
DBMS_RESOURCE_MANAGER_PRIVS

Using the DBMS_RESOURCE_MANAGER Package

Use the DBMS_RESOURCE_MANAGER package to maintain resource plans, resource consumer groups, and plan directives. You can also use this package to group together changes to the plan schema.

You must have the SYSTEM privilege to administer the Database Resource Manager. Typically, administrators have this SYSTEM privilege with the ADMIN option. Following are procedures that grant and revoke this SYSTEM privilege.

grant_system_privilege(grantee_name in varchar2,admin_option in boolean)

revoke_system_privilege (revokee_name in varchar2)

Administering Resource Plans

Note:
You must create a pending area before creating any Resource Manager objects. For more details see "Creating and Administering the Pending Area" .

You can use the following procedures to create, update, or delete resource plans:

create_plan(plan in varchar2, comment in varchar2,
   cpu_mth in varchar2 DEFAULT 'EMPHASIS',
   max_active_sess_target_mth in varchar2 DEFAULT
   'MAX_ACTIVE_SESS_ABSOLUTE',
   parallel_degree_limit_mth in varchar2 DEFAULT
   'PARALLEL_DEGREE_LIMIT_ABSOLUTE')
update_plan(plan in varchar2, new_comment in varchar2)
   DEFAULT NULL, new_cpu_mth in varchar2
   DEFAULT NULL, new_max_active_sess_target_mth in
   varchar2 DEFAULT NULL,
   new_parallel_degree_limit_mth in varchar2
   DEFAULT NULL)
delete_plan(plan in varchar2)
delete_plan_cascade(plan in varchar2)

The delete_plan procedure deletes the specified plan as well as all the plan directives it refers to. 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.

If you do not specify the arguments to update_plan procedure, they remain unchanged in the data dictionary.

If you wish to use a default resource allocation method, you need not specify it when creating or updating a plan. The method defaults are:

cpu_method ='EMPHASIS'
parallel_degree_limit_mth ='PARALLEL_DEGREE_LIMIT_ABSOLUTE'

Administering Resource Consumer Groups

You can use the following procedures to create, update, or delete resource consumer groups:

create_consumer_group(consumer_group in varchar2,
   comment in varchar2, cpu_mth in varchar2
   DEFAULT 'ROUND-ROBIN')
update_consumer_group(consumer_group in varchar2,
   new_comment in varchar2 DEFAULT NULL, 
   new_cpu_mth in varchar2 DEFAULT NULL)
delete_consumer_group(consumer_group in varchar2)

You need not specify the cpu_mth parameter if you wish to use the default CPU method, which is ROUND-ROBIN.

If you do not specify the arguments for the update_consumer_group procedure, they remain unchanged in the data dictionary.

Administering Resource Plan Directives

You can use the following procedures to create, update, or delete resource plan directives:

create_plan_directive(plan in varchar2, group_or_subplan 
   in varchar2, comment in varrchar2, cpu_p1 in number 
DEFAULT NULL, cpu_p2 in number
DEFAULT NULL, cpu_p3 in number
DEFAULT NULL, cpu_p4 in number
DEFAULT NULL, cpu_p5 in number
DEFAULT NULL, cpu_p6 in number
DEFAULT NULL, cpu_p7 in number
DEFAULT NULL, cpu_p8 in number
DEFAULT NULL, max_active_sess_target_p1 in number
DEFAULT NULL, parallel_degree_limit_p1 in number DEFAULT NULL)
update_plan_directive(plan in varchar2, group_or_subplan 
   in varchar2, new_comment in varchar2
DEFAULT NULL, new_cpu_p1 in number
DEFAULT NULL, new_cpu_p2 in number 
DEFAULT NULL, new_cpu_p3 in number DEFAULT NULL, new_cpu_p4 in number 
DEFAULT NULL, new_cpu_p5 in number DEFAULT NULL, new_cpu_p6 in number
DEFAULT NULL, new_cpu_p7 in number DEFAULT NULL, new_cpu_p8 in number
DEFAULT NULL, max_active_sess_target_p1 in number
DEFAULT NULL, new_parallel_degree_limit_p1 in number
DEFAULT NULL)
delete_plan_directive(plan in varchar2, group_or_subplan
  in varchar2)

All parameters default to NULL.

If you do not specify the arguments for the update_plan_directive procedure, they remain unchanged in the data dictionary.

Creating and Administering the Pending Area

All changes to the plan schema can be done within a pending area, which is a "scratch" area for plan schema changes. You must create this pending area, make changes as necessary and submit the changes (validation is optional).

You can use the following procedures to create, validate, and submit pending changes for the Database Resource Manager:

dbms_resource_manager.create_pending_area

dbms_resource_manager.validate_pending_area

dbms_resource_manager.clear_pending_area

dbms_resource_manager.submit_pending_area

Note:
The changes come into effect and become active only if the submit_pending_area procedure completes successfully.

You can also view the current schema containing your changes by selecting from the appropriate user views while the pending area is active. You can clear the pending area to abort the current changes any time as well. Call the validate procedure to check whether your changes are valid.

The changes made within the pending area must adhere to the following rules:

No plan schema may contain any loops.
All plan and/or resource consumer groups referred to by plan directives must exist.
All plans must have plan directives that point to either plans or resource consumer groups.
All percentages in any given level must not add up to greater than 100 for the emphasis resource allocation method.
A plan that is currently being used as a top plan by an active instance cannot be deleted.
The plan directive parameter parallel_degree_limit_p1 can appear only in plan directives that refer to resource consumer groups (not other resource plans).
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 consumer resource groups; at the lowest level in a plan schema the plan directives must refer to consumer groups.
Plans and resource consumer groups may not have the same name.
There must be a plan directive for OTHER_GROUPS somewhere in an 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.

Database Resource Manager allows "orphan" resource consumer groups (resource consumer groups with no plan directives referring to them) because you may wish to create a resource consumer group that is not currently being used, but will be used in the future.

You will receive an error message if any of the above rules are broken when checked by the validate or submit procedures. You may then make changes to fix the problem(s) and reissue the validate or submit procedures. The submit_pending_area clears the pending area after validating and committing the changes (if valid).

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.

The following commands create a multi-level schema, and use the default plan and resource consumer group methods as illustrated in Figure 11-1:

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 0', 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 0', 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 1', cpu_p1 => 0, cpu_p2 => 100, 
parallel_degree_limit_p1 => 3);
dbms_resource_manager.create_plan_directive(plan => 'MAILDB_PLAN', group_or_subplan => 
'Mail_Postman_group',
   comment => 'mail postman at level 0', 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 1', 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 1', cpu_p1 => 0, cpu_p2 => 20,
   parallel_degree_limit_p1 => 2);
dbms_resource_manager.create_plan_directive(plan => 'MYDB_PLAN', group_or_subplan => 
'MAILDB_PLAN', 
   comment=> 'all mail users' sessions at level 0', cpu_p1 => 30);
dbms_resource_manager.create_plan_directive(plan => 'MYDB_PLAN', group_or_subplan => 
'BUGDB_PLAN', 
   comment => 'all bug users' sessions at level 0', 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 11-1 Multi-level Schema

Assigning Resource Consumer Groups to Users

In addition to providing the above procedures to maintain resource plans and resource consumer groups, the DATABASE_RESOURCE_MANAGER package also contains procedures to assign resource consumer groups to users. The following procedure sets the initial consumer group of a user:

set_initial_consumer_group(user in varchar2, consumer_group in varchar2)

The initial consumer group of a user is the consumer group to which any session created by that user initially belongs. You must grant the switch privilege directly to the user or PUBLIC before it can be the user's initial consumer group. The switch privilege for the initial consumer group cannot come from a role granted to that user (these semantics are similar to those for ALTER USER DEFAULT ROLE).

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. DEFAULT_CONSUMER_GROUP has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this 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 as their initial consumer group. All sessions belonging to a deleted consumer group will be switched to DEFAULT_CONSUMER_GROUP.

Changing Resource Consumer Groups

You can use the following procedure to change the resource consumer group of a specific session:

switch_consumer_group_for_sess(session_id in number, session_serial 
   in number, consumer_group in varchar2)

You can use the following procedure to change the resource consumer group for all sessions with a given user id:

switch_consumer_group_for_user(user in varchar2, class in varchar2)

Both procedures also change the resource consumer group of any (PQ) slave sessions that are related to the top user session.

See Also: For information about views associated with Database Resource Manager, see the Oracle8i Reference.

The DBMS_RESOURCE_MANAGER_PRIVS Package

Use the DBMS_RESOURCE_MANAGER_PRIVS package to maintain privileges associated with resource consumer groups. The procedures in this package are executed with the privileges of the caller.

Granting Switch Privileges

To grant the privilege to switch to a consumer group, use the following procedure:

grant_switch_consumer_group(grantee_name in varchar2, consumer_group in varchar2,
   grant_option in boolean)

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 may also grant switch privileges for that consumer group to others.

Revoking Switch Privileges

To revoke the privilege to switch to resource consumer groups, use the following procedure:

revoke_switch_consumer_group(revokee_name in varchar2, consumer_group in varchar2)

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 Change a User's Resource Consumer Groups

You can change your current resource consumer group by calling the following procedure in the DBMS_SESSION package:

switch_current_consumer_group(new_consumer_group in varchar2,
   old_consumer_group out varchar2, initial_group_on_error in boolean)

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. This procedure also returns the old consumer group to users, and can be used to switch back to the old consumer group later.

The parameter initial_group_on_error controls the behavior of the procedure in the event of an error; if the parameter is set to TRUE and an error occurs, the invoker's consumer group is set to his/her initial consumer group.

Database Resource Manager Views

The following dynamic performance table views are associated with Database Resource Manager:

V$SESSION
V$MYSESSION
V$RSRC_CONSUMER_GROUP
V$RSRC_PLAN
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_PLAN_CPU_MTH
V$PARALLEL_DEGREE_LIMIT_MTH

The following static data dictionary views are associated with Database Resource Manager:

DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_RSRC_CONSUMER_GROUPS
DBA_RSRC_PLAN_DIRECTIVES
DBA_RSRC_PLANS
USER_RSRC_CONSUMER_GROUP_PRIVS
USER_RSRC_MANAGER_SYSTEM_PRIVS
DBA_USERS
USERS_USERS

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

11 Using the Database Resource Manager