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
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
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:
CREATE_PLANprocedure to create the new resource plans
CREATE_RESOURCE_GROUPprocedure to create the new resource consumer groups
CREATE_PLAN_DIRECTIVEprocedure to create some resource plan directives
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
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
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
procedure. And I can also switch just one user's session to a
different resource consumer group via the
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.
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