Oracle Database Resource Manager, Part 2: Resource Plan Management

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

Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles