9
Database Resource Management

This chapter describes how you can use the Database Resource Manager to allocate resources to different groups of users. This chapter includes the following topics:

• Introduction to the Database Resource Manager

• Resource Consumer Groups and Resource Plans

• Resource Allocation Methods

• Resource Plan Directives

• Examples

• Using the Database Resource Manager

  Note: The features of the Database Resource Manager described in this chapter are available only if you have purchased the Oracle8i Enterprise Edition. See Getting to Know Oracle8i for information about Oracle8i Enterprise Edition.

Introduction to the Database Resource Manager

The Database Resource Manager allows the database administrator to have more control over resource management than would normally be possible through operating system resource management alone. Improved resource management enables better application performance and availability. By using the Database Resource Manager, the database administrator can:

• Guarantee groups of users a minimum amount of processing resources, regardless of the load or number of users in other groups on the system.

• Distribute available processing resources by allocating percentages of CPU time to different users and applications. For example, in a data warehouse, a higher priority 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 plan for allocating resources. A database administrator can dynamically change the plan, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.

To use the Database Resource Manager, a database administrator defines the following items:

resource consumer groups	Groups of user sessions that have similar processing and resource use requirements.
resource plans	Means of allocating resources among the consumer groups.
resource allocation methods	Policies for allocating a particular resource. Resource allocation methods are used by both plans and consumer groups.
resource plan directives	Means of: assigning consumer groups or subplans to resource plans allocating resources among consumer groups in the plan by specifying parameters for each resource allocation method.

These items are described in detail in the following sections.

Resource Consumer Groups and Resource Plans

Resource consumer groups and resource plans provide methods of specifying how to allocate processing resources among different users. Currently, CPU is the only resource that is controlled at the level of resource consumer groups. Resource plans currently support control of two resources: CPU and degree of parallelism.

This section describes resource consumer groups and plans.

What Are Resource Consumer Groups?

To control resource consumption, you can assign user sessions to resource consumer groups. A resource consumer group defines a set of users who have similar requirements for resource use. A resource consumer group also specifies a resource allocation method for allocating CPU among sessions. Currently, the only resource allocation method for allocating CPU among sessions in a resource consumer group is the round-robin method.

Users can have the privilege of switching to different consumer groups. You can use PL/SQL procedures to switch resource consumer groups for a particular session, assuming you have the appropriate privileges.

There are two special consumer groups that cannot be modified or deleted:

• OTHER_GROUPS applies to all sessions that belong to a consumer group that is not part of the active plan schema. This group name always exists in the data dictionary. OTHER_GROUPS must have a resource plan directive specified in the schema of any active plan.

• DEFAULT_CONSUMER_GROUP applies to all sessions that have not been explicitly assigned to an initial consumer group.This group name always exists in the data dictionary.

There are two Oracle-provided consumer groups which you can modify, use as is, not use, or delete as appropriate for your environment:

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

• LOW_GROUP is provided for use in the SYSTEM_PLAN (see Table 9-2, "SYSTEM_PLAN Default Resource Plan"). Switch privilege is granted to PUBLIC for this group.

What Are Resource Plans?

Resource allocations are specified in a resource plan. Resource plans contain resource plan directives, which specify the resources that are to be allocated to each resource consumer group.

Conceptually, a session belongs to a resource consumer group, and this resource consumer group is used by a resource plan to determine allocation of processing resources.

You can use resource plans to:

• Group resource consumer groups and/or other resource plans together

• Allocate resources among those resource consumer groups or plans

• Specify a resource allocation. (Currently, the only resource allocation method for allocating CPU among resource consumer groups is the emphasis method. The only resource allocation method for limiting the degree of parallelism is the absolute method. Both of these methods are explained below.)

You can have multiple resource plans defined in the database, each allocating resources to resource consumer groups in different ways, making resource assignment flexible. However, only one plan can be active at a time. For example, you might define a daytime plan, a nighttime plan, and a weekend plan. Different instances of an Oracle Parallel Server can use different resource plans.

You can specify resource plans in a hierarchical fashion using subplans. Activating a plan also activates all of its subplans.

You can dynamically switch the top-level active plan while an instance is running. This enables you to define resource plans for different situations, and to change the plan depending upon the situation.

You can view resource plans and their associated attributes in the data dictionary view DBA_RSRC_PLANS. Each entry contains the following information:

• Plan name

• Number of plan directives

• Its CPU resource allocation method

• Its parallel degree limit method

• Comment

• Status (pending or active)

• Statement about whether the plan is mandatory

Following are sample resource plan entries:

PLAN   NUM_PLAN_D  CPU_METHOD  PARALLEL_DEGREE_LIMIT_MTH       COMMENTS                    STATUS  MANDATORY 
------ ----------  ----------- ------------------------------  --------------------------  ------- --------- 
MAILDB   3         EMPHASIS    PARALLEL_DEGREE_LIMIT_ABSOLUTE  Plan/method for mail users  ACTIVE  0 
APPDB    3         EMPHASIS    PARALLEL_DEGREE_LIMIT_ABSOLUTE  Plan/method for apps users  ACTIVE  0 

Changes to resource plans take immediate effect across all instances.

See Also: Oracle8i Reference for details about data dictionary views associated with resource plans and resource consumer groups

Resource Allocation Methods

Resource allocation methods determine what method or policy the Database Resource Manager uses when allocating a particular resource to a resource consumer group or resource plan.

Oracle provides the following methods shown in Table 9-1 for allocating resources to a resource consumer group or resource plan:

Table 9-1 Methods for Allocating Resources

Method	Resource	Resource Recipient
Round-robin method	CPU allocation to sessions	Resource consumer groups
Emphasis method	CPU allocation to consumer groups	Resource plans
Absolute method	Parallel degree limit	Resource plans

CPU Allocation for Resource Plans: Emphasis Method

The emphasis CPU allocation method determines how much emphasis is given to sessions in different consumer groups in a resource plan. CPU usage is assigned using levels from 1 to 8, with level 1 having the highest priority. Percentages specify how to allocate CPU to each consumer group at each level.

The following rules apply for the emphasis resource allocation method:

• Sessions in resource consumer groups with non-zero percentages at higher-priority levels always get the first opportunity to run.

• CPU resources are distributed at a given level based on the specified percentages. The percentage of CPU specified for a resource consumer group is a maximum for how much that consumer group can use at a given level. If any CPU resources are left after all resource consumer groups at a given level have been given an opportunity to run, the remaining CPU resources fall through to the next level. If a consumer group does not consume its allotted resources, then the resources are passed to the next level, not given to the other consumer groups at the same level.

• The sum of percentages at any given level must be less than or equal to 100.

• Any unused CPU time gets recycled. In other words, if no consumer groups are immediately interested in a specific period of CPU time (due to percentages), the consumer groups get another opportunity to use the CPU time, starting at level one.

• Any levels that have no plan directives explicitly specified have a default of 0% for all subplans/consumer groups.

An example of the emphasis method is explained later in this chapter.

The emphasis resource allocation method offers the following advantages:

• Setting percentages enables you to bring CPUs online and offline and to add and remove servers without having to change CPU percentages.

• The amount of CPU resources specified is not proportional to the number of servers, so there is a fine level of control even with a small number of servers.

• Setting percentages avoids the starvation problem associated with priorities. Users do not run at priorities; instead, they run based on the percentages specified for their resource consumer group. In addition, percentages can be used to simulate a priority scheme.

Parallel Degree Limit for Resource Plans: Absolute Method

The parallel degree limit allows the administrator to specify a limit on the degree of parallelism of any operation. This parameter is only allowed in directives that refer to resource consumer groups. Currently, the only resource allocation method for a limit on the degree of parallelism is the absolute method. Absolute refers to the fact that a number (rather than a percentage or fraction, for example) specifies how many processes may be assigned to an operation.

If there are multiple plan directives referring to the same subplan/consumer group, the parallel degree limit for that subplan/consumer group will be the minimum of all the incoming values.

See Also: Chapter 23, "Parallel Execution of SQL Statements" for an explanation of parallel execution

Resource Plan Directives

Resource plan directives are a means of:

• Assigning consumer groups or subplans to resource plans

• Allocating resources among consumer groups in the plan by specifying parameters for each resource allocation method

There is one resource plan directive for each entry in the plan.

Examples

This section includes examples of using resource consumer groups, resource plans, resource allocation methods, and resource plan directives.

Using Resource Consumer Groups and Resource Plans

The first step in using the Database Resource Manager is to identify resource requirements using resource consumer groups and resource plans.

Oracle provides a resource plan, SYSTEM_PLAN, which is defined in Table 9-2:

Table 9-2 SYSTEM_PLAN Default Resource Plan

Entry	Level 1	Level 2	Level 3
SYS_GROUP	100%	0%	0%
OTHER_GROUPS	0%	100%	0%
LOW_GROUP	0%	0%	100%

Users SYS and SYSTEM are in the consumer group SYS_GROUP. You can change this. SYSTEM_PLAN gives priority to system sessions. It also specifies a low priority group, LOW_GROUP, which has lower priority than SYS_GROUP and OTHER_GROUPS. It is up to you to decide which user sessions will be part of LOW_GROUP. You can use this simple Oracle-provided plan if it is appropriate for your environment.

Table 9-3 and Table 9-4 show sample resource plans for BUGDB and MAILDB:

Table 9-3 BUGDB Sample Resource Plan

Entry	Level 1	Level 2	Level 3
Online resource consumer group	80%	0%	0%
Batch resource consumer group	20%	0%	0%
Bug_Maintenance resource consumer group	0%	100%	0%
OTHER_GROUPS	0%	0%	100%

Table 9-4 MAILDB Sample Resource Plan  

Entry	Level 1	Level 2	Level 3
Mailusers resource consumer group	0%	80%	0%
Postman resource consumer group	40%	0%	0%
Mail_Maintenance resource consumer group	0%	20%	0%
OTHER_GROUPS	0%	0%	100%

The data in the BUGDB and MAILDB sample resource plans adheres to the emphasis CPU resource allocation method, which enables you to determine the degree of emphasis for sessions in different resource consumer groups by assigning an emphasis percentage for each resource consumer group.

In the MAILDB plan, the Postman resource consumer group is guaranteed 40% of the CPU. The CPU that is left over (60% or more if Postman does not need all of its allotted 40%) is split between the Mailusers resource consumer group and the Mail_Maintenance resource consumer group in a ration of 80:20. If Mailusers and Mail_Maintenance do not use up the remaining 60% of the CPU time, the remaining CPU time goes to OTHER_GROUPS. If there is still CPU time left after OTHER_GROUPS has an opportunity to use it, all of the consumer groups have an opportunity to use the leftover CPU time according to the rules listed .

Using Subplans

A resource plan that is referred to by another plan is called a subplan. For example, Table 9-5 is a plan that contains directives for two subplans:

Table 9-5 MYDB Resource Plan, CPU Plan Directives

Subplan/Group	CPU_Level 1
MAILDB Plan	30%
BUGDB Plan	70%

If the MYDB resource plan were in effect and there were an infinite number of runnable users in all resource consumer groups, the MAILDB plan would be in effect 30% of the time, while the BUGDB plan would be in effect 70% of the time.

Breaking this down further, if the MAILDB plan allocates 40% of resources to the Postman resource consumer group and the BUGDB plan allocates 80% of resources to the Online resource consumer group, then users in the Postman group would be run 12% (40% of 30%) of the time, while users in the Online group would be run 56% (80% of 70%) of the time. Figure 9-1 depicts this scenario.

Figure 9-1 Subplans: Resource Plans That Refer to Each Other

Using Multi-Level Resource Plans

A multi-level resource plan is more powerful than a single-level plan. When a resource consumer group does not use its allotment in a level, the remainder falls through to the next level, and you can specify explicitly what to do with it. In the single-level scheme the only choice is to spread the unused time among all the remaining resource consumer groups in the given ratios. Because of this difference, a multi-level scheme cannot be collapsed into a single-level scheme if the percentages for any given level less than the highest level add up to 100.

Using the Parallel Degree Limit Resource Plan Directive

In Table 9-6, the maximum degree of parallelism for any operation issued from the Online Group is 0, 4 for the Batch Group, 4 for the Bug_Maintenance group, and 4 for OTHER_GROUPS. This specification is an example of how the parallel degree limit plan directive can be used to restrict a group of sessions from running parallel operations. Online Group's parallel degree limit is 0. Therefore, all of its operations must run serially.

Table 9-6 Maximum Degree of Parallelism Plan Directives

Subplan/Group	parallel_degree_limit
Online Group	0
Batch Group	4
Bug_Maintenance Group	4
OTHER_GROUPS	4

Summary

Table 9-7 uses the BUGDB plan information from Table 9-3 and combines all the plan directives for the default resource allocation methods:

Table 9-7 Resource Plan Directives

	CPU Resource Plan Directives for Levels 1-8 (Note that only levels that have some non-zero directives need to be specified explicitly)				Parallel Degree Limit Resource Plan Directive
Subplan/Group	CPU_level 1	CPU_level 2	CPU_level 3	...	parallel_degree _limit_1
ONLINE Group	80%	0%	0%	0%	0
BATCH Group	20%	0%	0%	0%	4
BUG_MT Group	0%	100%	0%	0%	4
OTHER_GROUPS	0%	0%	100%	0%	4

Using the Database Resource Manager

To use the Database Resource Manager, the database administrator:

1. Creates resource plans using the PL/SQL package DBMS_RESOURCE_MANAGER.

2. Creates resource consumer groups using the PL/SQL package DBMS_RESOURCE_MANAGER.

3. Creates resource plan directives using the PL/SQL package DBMS_RESOURCE_MANAGER.

4. Assigns users to consumer groups using the PL/SQL package DBMS_RESOURCE_MANAGER_PRIVS.

5. Specifies the plan to be used by an instance. The initialization parameter RESOURCE_MANAGER_PLAN specifies which top plan to use for a given instance. The Database Resource Manager loads this top plan as well as all its descendants (subplans, directives, and consumer groups).

If the RESOURCE_MANAGER_PLAN parameter is not specified, the Database Resource Manager is disabled. The database administrator can set the parameter dynamically using the ALTER SYSTEM statement to enable the Database Resource Manager (if it was previously disabled), disable the Database Resource Manager, or change the current plan.

See Also: Oracle8i Supplied PL/SQL Packages Reference Oracle8i Administrator's Guide for information about using these PL/SQL packages