Oracle8i Concepts
Release 8.1.5






Prev Next

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

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:

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:

Following are two sample entries in the DBA_RSRC_CONSUMER_GROUPS view:

-------------- ----------- -------------------------------------- ------- ---------
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:

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:

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:

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

Following are sample resource plan entries:

------ ---------- ----------- ------------------------------ -------------------------- ------- --------- 
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:

The emphasis resource allocation method offers the following advantages:

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:

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


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



Batch resource consumer group  



Bug_Maintenance resource consumer group  



Table 9-3 MAILDB Sample Resource Plan
Entry  Level 1  Level 2 

Mailusers resource consumer group  



Postman resource consumer group  



Mail_Maintenance resource consumer group  



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 



BUGDB Plan  


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  


Batch Group  


Bug_Maintenance Group  



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

ONLINE Group  





BATCH Group  





BUG_MT Group  





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.  


Copyright © 1999 Oracle Corporation.

All Rights Reserved.