9
Database Resource Management

Seek not, my soul, the life of the immortals; but enjoy to the full the resources that are within thy reach.

Pindar: Pythian Odes

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

  Attention: The Database Resource Manager features 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. Using this facility, 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 shutdown and restart the instance.

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

resource consumer groups	A means of grouping user sessions that have similar processing and resource usage requirements.
resource plans	A means of allocating resources among the consumer groups.
resource allocation methods	A policy to use when allocating for any particular resource. Resource allocation methods are used by both plans and consumer groups.
resource plan directives	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.

These items are described in detail in the following sections.

Resource Consumer Groups and Resource Plans

Resource consumer groups and resource plans provide a method for specifying how to partition 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 limit.

This section describes resource consumer groups and plans, and explains how you can use resource consumer groups and resource plans to control resources.

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 resource usage requirements. A resource consumer group also specifies a resource allocation method for each controlled resource.

You can view resource consumer groups and their associated attributes in the data dictionary view DBA_RSRC_CONSUMER_GROUPS. Each entry contains the following information:

• resource consumer group name

• the CPU resource allocation method for the group

• comment

• status (pending or active)

• statement about whether or not the resource consumer group is mandatory (and cannot be deleted)

Following are two sample entries in the DBA_RSRC_CONSUMER_GROUPS view:

CONSUMER_GROUP CPU_METHOD  COMMENTS                               STATUS  MANDATORY
-------------- ----------- -------------------------------------- ------- ---------
BUGUSERS       ROUND-ROBIN Resource group/method for bug DB users ACTIVE  0 
PQ             ROUND-ROBIN Resource group/method for PQ slaves    PENDING 0 

Each user has a default resource consumer group. By default, all sessions owned by a user belong to that user's default resource consumer group.

Users can have the privilege to switch 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. You also can dynamically change resource allocation methods for a resource consumer group while the database is running.

One consumer group named DEFAULT_CONSUMER_GROUP always exists in the data dictionary. All sessions that do not explicitly belong to any group belong to DEFAULT_CONSUMER_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, below the resource consumer groups are the actual sessions for each user request. In other words, 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

• partition resources among those resource consumer groups or plans

• specify a resource allocation method for resource consumer groups

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 in one instance. 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.

There are two special consumer groups:

• 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 exist somewhere in the plan schema of any active plan.

  Thus, if a session belonged to consumer group 'C' and the instance used plan 'P', servers would try to use the following entries in this order:

  1. 'P', 'C',...

  2. 'P', 'OTHER_GROUPS',...

• DEFAULT_CONSUMER_GROUP applies to all sessions that do not explicitly belong to any group.

You cannot modify or delete these special consumer groups.

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 default group for the users SYS and SYSTEM.

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

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          ROUND-ROBIN MAX_ACTIVE_SESS_ABSOLUTE       Plan/method for apps users ACTIVE  0 
    

        

Changes to resource plans take immediate effect across all instances.

Additional Information: For details about data dictionary views associated with resource plans and resource consumer groups, see the Oracle8i Reference.

Resource Allocation Methods

Resource allocation methods determine what method or policy the Database Resource Manager uses when allocating for a particular resource, and are used by both resource consumer groups and resource plans.

Oracle provides a single resource allocation method for each manageable resource, including CPU and maximum degree of parallelism; these are the defaults. The following sections describe the Oracle-provided default resource allocation methods.

CPU Resource Allocation Method: Emphasis

The emphasis CPU resource allocation method determines how much emphasis is given to sessions in different consumer groups. This is specified by assigning emphasis percentages to each consumer group. CPU usage is assigned using levels, from 1 to 8. Percentages specify how to partition the CPU at each level.

The following rules apply for the emphasis resource allocation method:

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

• CPU resources are distributed at a given level based on the specified percentages. The percentage of resources specified for a resource consumer group is a maximum for how much that consumer group can use. 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 higher 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 quantum (due to percentages), the consumer groups get another opportunity to use the quantum, starting at level one.

• Any levels that have no plan directives explicitly specified are implied to have 0% for all subplans/consumer groups.

The emphasis resource allocation method offers the following advantages:

• The percentage method enables you to bring CPUs online and offline, and to add and remove servers.

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

• The percentage method 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.

Maximum Degree of Parallelism Resource Allocation Method: Absolute

The parallel degree limit resource directive 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. The default resource allocation method for the maximum degree of parallelism is an absolute number.

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.

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 Database Resource Manager is to identify resource requirements using resource consumer groups and resource plans.

Oracle provides one default resource plan, SYSTEM_PLAN, which is defined as follows:

Table 9-1 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%

SYS and SYSTEM have SYS_GROUP as their default consumer 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-2 and Table 9-3 show sample resource plans for BUGDB and MAILDB:

Table 9-2 BUGDB Sample Resource Plan

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

Table 9-3 MAILDB Sample Resource Plan

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

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.

If there were an infinite number of sessions to run in the MAILDB plan, the Postman resource consumer group would run 40% of the time, while the Mailusers resource consumer group and Mail_Maintenance resource consumer group would split the remainder in a ratio of 80:20. Thus, the Mailusers resource consumer group would run 48% (80% of 60%) of the time, and the Mail_Maintenance resource consumer group would run 12% (20% of 60%) of the time. In this example, the entries in Level 2 are guaranteed to get at least 60% of the CPU resource. They may get more, depending on whether the Postman resource consumer group uses up all of its allotted 40%.

Using Subplans

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

Table 9-4 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 Directive

In the following example, the maximum degree of parallelism for any operation issued from the Online Group is 0, 4 for the Batch Group, and 4 for the Bug_Maintenance group. 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-5 Maximum Degree of Parallelism Plan Directives

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

Summary

The following example uses the BUGDB plan example, and combines all the plan directives for the default resource allocation methods specified above:

	CPU Resource Plan Directives (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	...	parallel_degree _limit_1
ONLINE Group	80%	0%	0%	0
BATCH Group	20%	0%	0%	4
BUG_MT Group	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 command to enable the Database Resource Manager (if it was previously disabled), disable the Database Resource Manager, or change the current plan.

   Additional Information: For information about using these PL/SQL packages, see the Oracle8i Administrator's Guide.

---