Oracle Database Resource Manager, Part 2: Resource Plan Management

February 19, 2004

The previous article in this series discussed the basic concepts of Oracle Database Resource Management (DRM) for managing database server resources. This next article moves beyond the conceptual phase and tackles building, maintaining, and monitoring resource consumer groups, resource plans, and the resource directives that bind them together.

In the previous article, I showed how to use the CREATE_SIMPLE_PLAN procedure of DBMS_RESOURCE_MANAGER to create a rudimentary resource plan. I will explore more of the myriad features of this Oracle-supplied DRM package in depth next.

The Pending Area

The previous article mentioned rules about resource plans not looping back upon themselves via their subplans. This is just one example of why creating a more complex plan is not necessarily trivial. Fortunately, Oracle has made the DRM smart enough to detect problems like this before they occur via four procedures that are part of DBMS_RESOURCE_MANAGER.

DRM requires that all changes to resource plan components be posted to a pending area before they are submitted to the Oracle database. This pending area needs to be created before any DRM modifications can be posted:

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

Once modifications are posted to the pending area, it needs to be validated. If the changes are invalid, the procedure will return an error, and the changes must be correctly reposted:

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

Once the changes are validated, they can be submitted to the DRM. This procedure automatically performs one last validation before the submitted changes are accepted:

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

Finally, one of the nice things about the pending area is that I can simply clear out all changes stored within it and start over:

EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

Listing 2.1 shows examples of the commands necessary to maintain the pending area.

Moving Beyond Simple Resource Plans

In the previous article, I showed you how to use the CREATE_SIMPLE_PLAN procedure of DBMS_RESOURCE_MANAGER to create a rudimentary resource plan. I will start with a rudimentary example that recreates the basic plan, PEAKTIME, and adds a new plan, OFF-PEAK.

Before I can create the new plans, however, I will first need to remove the old plan that I created. To do this, I will issue the DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE procedure to remove the original PEAKTIME resource plan, all of its resource plan directives, and any associated resource groups. Listing 2.2 shows how to invoke this procedure within the Pending Area.

After clearing and re-initializing the pending area (see previous section), I will utilize the following DBMS_RESOURCE_MANAGER procedures to create the new plans:

• The CREATE_PLAN procedure to create the new resource plans
• The CREATE_RESOURCE_GROUP procedure to create the new resource consumer groups
• The CREATE_PLAN_DIRECTIVE procedure to create some resource plan directives

Finally, I will submit the changes to the DRM via the SUBMIT_PENDING AREA procedure.

Listing 2.3 shows how these procedures can be used to create the two new resource plans, the three new resource consumer groups and their component resource plan directives.

Assigning Users to Resource Consumer Groups

Now that I have got my two resource plans defined, I will make sure that the users for which I want to manage resource utilization will be permitted to switch to those plans.

I'll do this by first granting the appropriate users the right to switch to different resource consumer groups via the DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP procedure. Then I will use the DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP procedure to set these users' initial resource consumer group via the procedure. (Remember that when a user is created, by default the user is assigned to the DEFAULT_CONSUMER_GROUP resource consumer group, so this is necessary to switch a user to a new resource consumer group.)

Listing 2.4 shows how to use these two procedures to enable the HR and SALESMGR users so they can use the PEAKTIME and OFF-PEAK plans.

Switching Resource Plans

Now that I have all the pieces in place, it is time to activate the new PEAKTIME resource plan for the database via the ALTER SYSTEM command:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PEAKTIME';

To switch the database to the OFF-PEAK resource plan, I would simply issue the ALTER SYSTEM command with that plan name instead:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'OFF-PEAK';

I can verify which resource management plan is in effect by querying V$PARAMETER:

SQL> SELECT VALUE FROM V$PARAMETER 
SQL> WHERE name = 'resource_manager_plan';
VALUE
---------
PEAKTIME
SQL>

Finally, I can insure that the PEAKTIME plan is the default resource plan in effect whenever I restart the database by setting the RESOURCE_MANAGER_PLAN initialization parameter in the database's initialization file.

Switching Users and Sessions to Different Resource Plans

Once the new resource plan has been activated, I can switch all of a user account's sessions to a different resource consumer group via the DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER procedure. And I can also switch just one user's session to a different resource consumer group via the DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS procedure, once I know that user session's session ID and session serial ID by querying the V$SESSION view.

Listing 2.5 shows how to switch consumer groups for just one session, or for all sessions for a specified user.

Final Steps

My final article in this series will discuss the creation of more complex DRM plans via DBMS_RESOURCE_MANAGER, how to monitor DRM resource plan utilization, and some remaining tidbits on maintaining DRM plans, directives, and consumer groups.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 9i Release 2 documentation for the deeper technical details of this article:

A96521-01 Oracle 9i Release 2 Database Administrator's Guide, Chapter 27

» See All Articles by Columnist Jim Czuprynski