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:
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:
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:
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:
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
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
After clearing and re-initializing the pending area (see
previous section), I will utilize the following
Finally, I will submit the changes to the DRM via the
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
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
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
Listing 2.5 shows how to switch consumer groups for just one session, or for all sessions for a specified user.
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