Implementing the Database Resource Manager
By Roby Sherman


In the past, the task of resource management was often left to the sole discretion of the operating system running underneath the RDBMS engine. This type of management, although sometimes useful from a operating system perspective, wasn't granular enough to effectively manage database activities and introduced other management issues, such as :

 

Database Resource Manager Benefits

The database resource manager introduced in Oracle 8i addresses these issues by giving the database more control over the management of its own resources. This enables a DBA to:

 

New Application Possibilities

The database resource manager allows us to explore new application possibilities that were previously believed to be either too impractical or impossible. Let us examine some situations where using database resource manager may be useful:

As you can see, there are a number of situations where this new feature may be potentially useful. It is also important to point out that the database resource manager may not be appropriate for all situations. We will discuss this further in the next section.

Database Resource Manager Considerations

Before you run out and start deploying the database resource manager as the new silver-bullet in your Oracle enterprise, remember that as with many enabling technologies, there are some environments where the database resource manager may not be an appropriate fit. Some initial thoughts to ponder are:

As with any architecture change, thorough research and evaluation are the keys to understanding whether or not implementing the database resource manager in your environment is appropriate for you.

 

Getting Started with the Database Resource Manager

So you've decided to take the database resource manager for a spin. Let's first walk through some quick fundamentals before we jump into the database setup portion of this article.

In order to administer the database resource manager environment, a special system privilege called ADMINISTER RESOURCE MANAGER must be granted to your Oracle user account. This privilege cannot be granted or revoked from a typical GRANT or REVOKE SQL statement, but rather through the exclusive use of the DBMS_RESOURCE_MANAGER_PRIVS package. Make sure that the Oracle user that will be administering your database resource management environment has been granted this necessary system privilege prior to setting up your environment.

The two calls that are used to control the grant/revoke of these privileges are:

DBMS_RESOURCE_MANAGER_PRIVS.Grant_System_Privilege

Argument Name

Type

Default Value

Description

GRANTEE_NAME

Varchar2

-

The database user or role to get the privilege.

PRIVILEGE_NAME

Varchar2

'ADMINISTER_RESOURCE_MANAGER'

The name of the privilege to grant.

ADMIN_OPTION

BOOLEAN

FALSE

'true' if the user/role should have the ability to grant the privilege to other users/roles.

 and

 DBMS_RESOURCE_MANAGER_PRIVS.Revoke_System_Privilege

Argument Name

Type

Default Value

Description

REVOKEE_NAME

Varchar2

-

The database user or role to loose the privilege.

PRIVILEGE_NAME

Varchar2

'ADMINISTER_RESOURCE_MANAGER'

The name of the privilege to revoke.

 

Database Resource Manager Elements

In order to setup a functioning database resource management environment, a series of related elements must be created and/or utilized: 

 

Element

Description 

Pending Area

A session memory "scratch-pad" used to store / validate changes to the resource manager prior to committing them into the database.

Resource Plan 

Specifies how database resources will be allocated (e.g. round-robin, etc) to the resource consumer groups (or possibly sub-plans). Each plan/sub-plan receives a portion of resources and then distributes these resources within its own level 1, 2, n consumers. The distribution of these resources is controlled by Resource Plan Directives that are assigned to each Resource Plan.

Resource Consumer Groups

A logical grouping of user sessions based on their requirements for processing resources.

Resource Plan Directives 

Each directive is associated with a particular resource plan. They define when and how resources are given to each consumer group. 

Resource Allocation Method

Determines the pre-defined method (and policies) to use when allocating for any particular resource.

 

An Implementation Example

Probably the best way to illustrate these elements how they are used within the database resource manager is to walk through an example database implementation. Here's our scenario that will be using:

Let's say that you've been working with the Sales and Support organizations to jointly migrate their applications to a single Oracle database instance. All of the other technical issues (number of connections, size of data, Etc.) have been researched and resolved. The only remaining concern is to establish an environment where the applications can have guaranteed minimum amounts of database resources. Through various application meetings and agreements, you have captured the following application requirements:

If you were to translate these requirements into a visual representation, you might create a resource hierarchal diagram similar to this:

 

Let's verify our resource hierarchy diagram against our configuration notes. Everything seems to be in order. We are now ready to identify and categorize the tasks necessary to implement this environment:


Required Tasks:

 

          Set Up The Pending Area –

  • We need to create a pending area to hold our changes until we are ready to submit them into the dictionary.

Create The Resource Plans –

  • We will create a resource plan called DAY_PLAN

  • We will create another resource plan called APP_PLAN that will be used by the sales and service application users. It will be treated as a sub-plan for DAY_PLAN.

  • Another resource plan called SALES_PLAN will be created. It will be treated as a sub-plan for APP_PLAN.

  •  Finally, we will create a resource plan called SERVICE_PLAN. It will be treated as a sub-plan for APP_PLAN.

Create The Resource Consumers –

  • We will create the consumer groups BATCH_GROUP, DATA_ADMIN, SALES_BUSY_GROUP, SALES_NON_BUSY_GROUP,SERVICE_CRITICAL_GROUP, SERVICE_NON_CRITICAL_GROUP

     

    Create The Resource Plan Directives –

  • We will specify how the database resources are to be distributed to the various consumer groups / sub-plans by creating the following plan directives:

  • DAY_PLAN will give 45% of the database CPU resources to SYS_GROUP and DATA_ADMIN at level 1. This means that APP_PLAN on level 2 will receive anywhere from 10% (in the most extreme cases) to 100% of the database CPU resources depending on level 1's overall load.

  • APP_PLAN will give SALES_PLAN 40% of the available database CPU resources at level 1 (of the APP_PLAN)

  • APP_PLAN will give SERVICE_PLAN 50% of the available database CPU resources at level 1 (of the APP_PLAN)

  • APP_PLAN will pass down any unused level 1 resources to OTHER_GROUPS and BATCH_GROUP at level 2. OTHER_GROUPS will be allowed to consume up to 50% of these resources and BATCH_GROUP will be given the other 50%.

  • SALES_PLAN will give SALES_BUSY_GROUP 80% of the database CPU resources allocated to the SALES_PLAN.

  • SALES_PLAN will give SALES_NON_BUSY_GROUP 20% of the database CPU resources allocated to the SALES_PLAN.

  • SERVICE_PLAN will give SERVICE_CRITICAL_GROUP 90% of the database CPU resources allocated to the SERVICE _PLAN.

  • SERVICE_PLAN will give SERVICE_NON_CRITICAL_GROUP 10% of the database CPU resources allocated to the SERVICE _PLAN.

Validate and Submit The Pending Area –

  • The pending area will be validated for errors.

  • The pending area will be submitted into the data dictionary.

Assign Users -

  • Users will be assigned to their respective consumer groups.

Final Checks – 

  • Check the dictionary to verify our configuration

Specify the Active Database Resource Plan -

  • The database will be switched to the DAY_PLAN.

 

Now that the major tasks have been identified and grouped together, we're ready to get started with our sample implementation.

 

Set Up The Pending Area

Before you can create or edit a database resource management plan, you must first create a Pending area that will house your changes until they are submitted to the database for commit. This is accomplished by issuing the PL/SQL call: 

Begin
   
DBMS_RESOURCE_MANAGER.Create_Pending_Area;
End;

Any changes you make to the database resource manager plans will now be recorded directly into this pending area for later validation and submission. The changes will not affect the active database environment until after they have been submitted successfully. This step will be illustrated later.

 

Create The Resource Plan

It's time to create all of the resource plans that will be needed for this implementation. These plans will be used to specify not only the methods for allocating resources but will serve as the parent of the many plan directives that will actually regulate how much of each resource is distributed to the various consumer groups and sub-plans.

Oracle 8i ships with a single pre-defined resource plan called SYSTEM_PLAN.

In order to create these plans, we will be using the DBMS_RESOURCE_MANAGER.CREATE PLAN procedure which accepts the following arguments:

Argument Name

Type

Default Value

Description

PLAN

Varchar2

-

The name of the resource plan

COMMENT

Varchar2

-

A meaningful description of what the plan will do

CPU_MTH

Varchar2

'EMPHASIS'

The name of the pre-defined allocation method to be used for CPU resources.

MAX_ACTIVE_SESS_TARGET_MTH

Varchar2

'MAX_ACTIVE_SESS_ABSOLUTE'

The name of the pre-defined allocation method for regulating the maximum number of sessions.
(This feature is presently unsupported in 8.1.6)

PARALLEL_DEGREE_LIMIT_MTH

Varchar2

'PARALLEL_DEGREE_LIMIT_ABSOLUTE'

The name of the pre-defined allocation method for regulating parallel degree limitations.

This example code uses the default resource methods to produce the necessary resource plans  / sub-plans for our sample implementation:

Begin

   DBMS_RESOURCE_MANAGER.create_plan(plan=>'DAY_PLAN', comment=>'The top-level daytime operating plan');

   DBMS_RESOURCE_MANAGER.create_plan(plan=>'APP_PLAN', comment=>'The Sales and Service common plan');        

   DBMS_RESOURCE_MANAGER.create_plan(plan=>'SALES_PLAN', comment=>'The plan specific to the sales application');

   DBMS_RESOURCE_MANAGER.create_plan(plan=>'SERVICE_PLAN', comment=>'The plan specific to the service application');   

End;

 

Create The Consumer Groups

The next task is to create the groups that will be later assigned as resource consumers to the plans / sub-plans created in the previous step.

Oracle provides 4 generic consumer groups with 8i that can be used in your database resource manager planning:

WARNING>> If you do not include SYS_GROUP in your active plan hierarchy, you will essentially be allocating 0% of available resources for SYS and SYSTEM (unless you specifically assign these database user IDs to an active plan/sub-plan.)

In our example, we will be using the predefined SYS_GROUP and OTHER_GROUPS in our overall DAY_PLAN. We can therefore focus on creating the remaining consumer groups by using the DBMS_RESOURCE_MANAGER.Create_Consumer_Group procedure:

Argument Name

Type

Default Value

Description

CONSUMER_GROUP

Varchar2

-

The name of the consumer group.

COMMENT

Varchar2

-

A meaningful description of who the consumer group represents.

CPU_MTH

Varchar2

'ROUND-ROBIN'

The name of the pre-defined allocation method to be used for CPU resources.

This example code uses the default CPU resource method to produce the necessary consumer groups for our sample implementation:

 

Begin

            DBMS_RESOURCE_MANAGER.create_consumer_group(consumer_group=>'DATA_ADMIN',
           
comment=>'Data Administrator'); 

DBMS_RESOURCE_MANAGER.create_consumer_group(consumer_group=>'BATCH_GROUP',
comment=>'Daytime Batch Processing (Non-Intrusive)'); 

            DBMS_RESOURCE_MANAGER. create_consumer_group (consumer_group=>'SALES_NON_BUSY_GROUP',
            comment=>'Sales Users Assigned to Non-Busy Regions');

DBMS_RESOURCE_MANAGER. create_consumer_group (consumer_group=>'SALES_BUSY_GROUP',
   
         comment=>'Sales Users Assigned to Busy Regions');

DBMS_RESOURCE_MANAGER. create_consumer_group (consumer_group=>'SERVICE_CRITICAL_GROUP',
           
comment=>'Service Reps Assigned to Business-Critical Regions');

DBMS_RESOURCE_MANAGER. create_consumer_group(consumer_group=>
            'SERVICE_NON_CRITICAL_GROUP',
   
         comment=>'Service Reps Assigned to Non Business-Critical Regions');

End;

  

Create The Resource Plan Directives

It's now time to assign to the consumer groups (including the sub-plans) to their respective parent resource plans. By creating resource plan directives we form this relationship and also specify how much resource should be allocated to the respective consumer group / sub-plan.

By default, a small set of resource plan directives ship with the Oracle 8i database. These directives are bound to the SYSTEM_PLAN can be utilized, modified, or simply ignored by the DBA. The SYSTEM_PLAN directives state:

Although our sample implementation does make use of some of 8i's default consumer groups, we will not be using any of these resource plan directives in our example.

In order to create our own resource plan directives, we will be using the DBMS_RESOURCE_MANAGER.Create_Plan_Directive procedure:

Argument Name

Type

Default Value

Description

PLAN

Varchar2

-

The name of the resource plan that this directive will bind to.

GROUP_OR_SUBPLAN

Varchar2

-

The name of the consumer group or the sub-plan that this directive will affect.

COMMENT

Varchar2

-

Some meaningful verbiage which describes the purpose of this directive.

CPU_P1, CPU_P2, … , CPU_P8

Number

0

Parameters for the CPU resource allocation method. (Generally specifies the amount of CPU resources allocated to the group or sub-plan at a particular level of the resource allocation hierarchy per the Emphasis CPU Method)

MAX_ACTIVE_SESS_TARGET_P1

Number

0

Parameter for the Active Sessions allocation method. Generally Specifies the maximum number of active sessions permitted. (Not currently supported. Reserved for future use)

PARALLEL_DEGREE_LIMIT_P1

Number

0

Parameter for the Degree of Parallelism allocation method (Generally specifies the maximum  amount of degree permitted)

Starting at the Top-plan and working our way down, our example code used to produce the resource plan directives for the sample implementation is:

Begin

DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'DAY_PLAN', 
            group_or_subplan=>'SYS_GROUP',
   
         comment=>'Directs 45% of the CPU resources at level 1 for critical DBA work',
            cpu_p1=>45);

DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'DAY_PLAN', 
            group_or_subplan=>'DATA_ADMIN',
   
         comment=>'Directs 45% of the CPU resources at level 1 for critical DA work',
            cpu_p1=>45);

DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'DAY_PLAN', 
            group_or_subplan=>'APP_PLAN',
   
         comment=>'APP_PLAN will use 100% of the left overs from level 1 (10-100%)', 
            cpu_p2=>100);

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'APP_PLAN', 
            group_or_subplan=>'BATCH_GROUP',
   
         comment=>'Any remaining resources from APP_PLAN lvl 1 may be used here (up to 50%)',
   
         cpu_p2=>50);

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'APP_PLAN',
            group_or_subplan=>'SALES_PLAN',
            comment=>'Directs 40% CPU resources from App_Plan to Sales Applications', 
            cpu_p1=>40);

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'APP_PLAN',
            group_or_subplan=>'SERVICE_PLAN',
            comment=>'Directs 50% CPU resources from App_Plan to Service Applications', 
            cpu_p1=>50);

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'APP_PLAN',
           
group_or_subplan=>'OTHER_GROUPS',
            comment=>'Any remaining resources from APP_PLAN lvl 1 may be used here (up to 50%)', 
            cpu_p2=>50,parallel_degree_limit_p1=>1);

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'SALES_PLAN',
           
group_or_subplan=>'SALES_NON_BUSY_GROUP',
   
         comment=>'Directs 20% CPU (max deg 2) resources from Sales_Plan to reps in non-busy states',
   
         cpu_p1=>20, parallel_degree_limit_p1=>2);

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'SALES_PLAN',
           
group_or_subplan=>'SALES_BUSY_GROUP',
   
         comment=>'Directs at least 80% CPU resources from Sales_Plan to reps in busy states', 
            cpu_p1=>80); 

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'SERVICE_PLAN',
   
         group_or_subplan=>'SERVICE_CRITICAL_GROUP',
   
         comment=>'Directs at least 90% CPU resources from Service_Plan to reps in busy states', 
            cpu_p1=>90);

            DBMS_RESOURCE_MANAGER.Create_Plan_Directive(plan=>'SERVICE_PLAN',
   
         group_or_subplan=>'SERVICE_NON_CRITICAL_GROUP',
   
         comment=>'Directs at 10% CPU (max deg 2) resources from Service_Plan to non-busy states.',
   
         cpu_p1=>10, parallel_degree_limit_p1=>2);

End;

 

Validate and Submit the Pending Area

Everything should have been successful so far. We're now ready to validate that the changes in the pending area. While we could simply try submitting the Pending Area (causing a validation to occur regardless) it's sometimes nice to have a sanity check along the way without attempting to submit a modification in progress. The Validate_Pending_Area procedure will look for any obvious flaws that might exist in our proposed configuration (e.g. The sum of percentages at a particular level is more than 100%, Etc.) and report them with an error message if any are discovered. Please refer to Appendix B for a list of configuration notes that are evaluated during the validation process.

 

To validate the pending area, issue the following statement:

Begin
            DBMS_RESOURCE_MANAGER.Validate_Pending_Area;
End;

If no errors are produced then we'll be ready to submit the pending area. To permanently submit (why not call it COMMIT!?) the pending area into the data dictionary, issue the following statement: 

Begin
            DBMS_RESOURCE_MANAGER.Submit_Pending_Area;
End;

If the call completes successfully, the commit will be complete and the pending area will be cleared.

 

Assign Users 

With the resource plans, directives, and consumer groups in place, we are now ready to set up our database users / roles.

Two stored procedure calls are involved in assigning Oracle users or roles to a Resource Consumer Group:

DBMS_RESOURCE_MANAGER_PRIVS.Grant_Switch_Consumer_Group* – Allows the user / role to switch to a specified consumer group.

Argument Name

Type

Default Value

Description

GRANTEE_NAME

Varchar2

-

The database user / role to receive the privilege.

CONSUMER_GROUP

Varchar2

-

The consumer group to grant to the user / role.

GRANT_OPTION

Boolean

False

'True' if the user / role should be able to grant this consumer group to someone else.

 

DBMS_RESOURCE_MANAGER.Set_Initial_Consumer_Group* – Sets the initial resource consumer group for the specified user / role.

Argument Name

Type

Default Value

Description

USER

Varchar2

-

The database user / role to modify.

CONSUMER_GROUP

Varchar2

-

The initial consumer group the user should by default.

* You must have the ADMINISTER RESOURCE MANAGER or the ALTER USER system privilege in order to call these procedures.

 

For the implementation example, create or use some test database IDs and assign them to resource consumer groups using the procedures above.

 

Final Checks

Our configuration is now committed and complete. If we wanted to validate the configuration as it exists in the Oracle data dictionary, there are a number of views that provide us with useful information. They are:

 

Specify the Active Database Resource Plan

We're now ready to switch the database to our new resource plan. To do this, we will execute:

alter system set resource_manager_plan='DAY_PLAN'; 

This command will cause the database to switch to the specified resource plan and modify the future (and current) resource utilization of any active / inactive consumers in the database.

If we decide later to reset the current database plan back to the default plan, we simply need to issue the command:

alter system set resource_manager_plan='';

To set the default resource manager plan at database startup, use the init.ora parameter:

resource_manager_plan = plan_name

 

Finished!

We have now established an operating resource manager environment within our database environment. While the environment is running, a number of dynamic views can give the DBA / Resource Manager Administrator a pretty good idea of how the database is performing. These views are:

 

Switching Consumer Groups 

As your resource manager environment becomes more mature and your application becomes more 8i-saavy, the use of resource consumer group switching will undoubtedly become more frequent. Using this ability, a database session can switch from its initial resource consumer group to another, for example, that is more appropriate for a current application role or specific application task.

The primary way for a database session to switch it's resource consumer group is by using:

DBMS_SESSION.Switch_Current_Consumer_Group* – Allows the current session to switch to a specified consumer group.

Argument Name

Type

Default Value

Description

NEW_CONSUMER_GROUP

Varchar2

 

Name of the consumer group to switch to.

OLD_CONSUMER_GROUP

Varchar2

(Out)

Name of the current consumer group

Returns the name of the consumer group that the session switched out of.

INITIAL_GROUP_ON_ERROR

Boolean

-

If set to TRUE, the session will be switched to its initial consumer group if the SWITCH fails. If set to FALSE, the session will remain its current consumer group.

* The caller of this procedure (whether it is direct user call or another stored procedure) must have been granted switch privileges to the NEW_CONSUMER_GROUP in order for the call to succeed.

 

Resource Manager/Database administrators also have the option of dynamically switching resource consumers from one group to another by using one of the following calls:

DBMS_RESOURCE_MANAGER.Switch_Consumer_Group_For_User* - Switch consumer group for all sessions (including PQ slaves) running with a certain USER ID.

Argument Name

Type

Default Value

Description

USER

Varchar2

-

Name of the user (no roles allowed) to modify

CONSUMER_GROUP**

Varchar2

-

Name of the consumer group to switch the user to.

* The user executing this procedure must have the ALTER USER or ADMINISTER RESOURCE MANAGER system privilege.|
** The user can only be switched to another CONSUMER_GROUP that has been granted to that user.

  

DBMS_RESOURCE_MANAGER.Switch_Consumer_Group_For_Sess* - Switch consumer group for a specific session and possible PQ slaves operating on behalf of the session.

Argument Name

Type

Default Value

Description

SESSION_ID

Number

-

The Session Identifier (SID)

SESSION_SERIAL

Number

-

The Session Serial #.

CONSUMER_GROUP**

Varchar2

-

Name of the consumer group to switch the user to.

* The user executing this procedure must have the ALTER USER or ADMINISTER RESOURCE MANAGER system privilege.
** The session can only be switched to another CONSUMER_GROUP that has been granted to that session.

  

Conclusion

In many environments, the Database Resource Manager can be a useful tool to better manage database resources in terms of how they are consumed by users, groups, database applications, Etc. Used properly, the feature can provide for greater intra-instance stability, allowing for a more diverse range of dissimilar environments to co-inhabit a single database instance.




















 

Appendix A – Resource Allocation Methods

Oracle provides a single resource allocation method (used as the default) for each resource that can be managed. This section will briefly review each resource allocation method, what it is used for, and how it works. 

CPU Resource Allocation Method, EMPHASIS

This method is generally used at the resource plan level and determines how much emphasis is given to different consumer groups (including sub-plans). This is specified by assigning a percentage of the resource to each consumer group at a given level (there are a maximum of 8 levels)

The Emphasis method abides by the following rules:

This method offers the following benefits:

 

CPU Resource Allocation Method, ROUND-ROBIN

This CPU allocation method is generally used at the consumer group level and specifies that the users of the given consumer group will use their allotment of CPU resources in a round-robin type fashion.

 

Parallel Degree Limit Method, PARALLEL_DEGREE_LIMIT_ABSOLUTE

The Absolute method allows the DBA to place a hard limit on the degree of parallelism used by consumer groups.

There are two caveats to be aware of when using this method:

 

Appendix B – Resource Manager Configuration Notes

Database Resource Manager changes must adhere to the following rules in order to pass the validation process that goes against the Pending Area:

While they will not cause errors during the validation process, you should also think about the following points when designing a resource plan hierarchy:

 

Copyright 2000 - Interealm