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:
2.1 shows examples of the commands necessary to maintain the pending
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:
to create the new resource plans
CREATE_RESOURCE_GROUP procedure to create the new resource consumer groups
CREATE_PLAN_DIRECTIVE procedure to create some resource plan directives
Finally, I will submit the changes to the DRM via the
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.)
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
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
SQL> SELECT VALUE FROM V$PARAMETER
SQL> WHERE name = 'resource_manager_plan';
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
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.
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
See All Articles by Columnist Jim Czuprynski