Synopsis.
Oracle
Database Resource Management (DRM) provides tools that allow any Oracle DBA to
manage a database server’s CPU resources effectively for application user
groups and during different resource demand periods. This article provides a
brief overview of how DRM works and then delves into the preparations necessary
to implement a simple DRM configuration.
I have spent the last two weeks of 2003 reviewing the
performance of our Oracle production database during the months of October and
November, our peak processing season of the year. Perusing STATSPACK reports
has shown me a few minor areas for improvement – an unexpected table scan here,
a SQL query that needed a better hint, and a few indexes that needed rebuilding
– yet I realized the database is pretty well tuned based on all the traditional
metrics.
My next area of attack, then, is to improve application
performance by allocating server resources more effectively. Our production
cycle is not uncommon for customer service organizations: During peak demand
periods for order taking and order-fulfillment services, on-line transaction
processing (OLTP) user sessions must be given priority over sessions for all
other online applications. While users running reports and ad hoc
queries must be granted sufficient resources to perform their duties, I cannot
allow read-only queries to grab server resources at the expense of my OLTP
application users.
In addition, I need to balance the resources needed during
peak versus off-peak periods. Like many customer service organizations, our
peak periods spike in the late morning, drop off during lunchtime and then
spike again in the mid-afternoon. However, late in the evenings, other batch
processing tasks like bulk customer billing, data extraction, translation and
loading (ETL) for our (eventual) data warehouse, and database backups and
exports must take precedence.
Finally, it is really important that changes to database
resource utilization happen either automatically or with only minimal DBA
intervention. After all, our DBA team has better things to do than constantly
monitor a few user sessions that are consuming resources beyond their
measure! Fortunately, Oracle provides a solution for managing server and
database resources to fulfill these requirements: Oracle Database Resource
Management (“acronyzed” to DRM for the remainder of these articles).
DRM consists of four basic components:
Resource Consumer Groups. A resource
consumer group is a collection of users with similar requirements for resource
consumption. Users can be assigned to more than one resource consumer group,
but each user’s active session can only be assigned to one resource
consumer group at a time. For example, I can construct separate consumer groups
for OLTP users, one for decision support users and one for users who typically
create ad hoc queries.
Resource Plans. In its simplest form, a
resource plan describes the resources allocated to one or more resource
consumer group(s). For example, I can specify the amount of CPU utilization
permitted for a group of users.
A resource plan can also have multiple subplans
that specify a finer grain of resource allocation detail. For example, I can
create two subplans for my OLTP users, one for high-volume users and one for
lower-volume users. Note that a subplan can have more than one parent plan, but
Oracle will not permit a subplan to “loop back” upon itself.
Resource Plan Directives. Resource
plan directives allocate resources among the resource consumer groups in the
resource plan. Essentially, directives connect resource consumer groups or subplans
to their resource plans. There is at least one resource plan directive for each
entry in the resource plan.
Resource plan directives use resource allocation
methods to determine how the DRM will allocate resources to a resource
consumer group or resource plan. Several allocation methods are available:
CPU. As its name implies, this method controls how
much CPU utilization will be permitted to user sessions for different resource
consumer groups within a specific resource plan. CPU utilization is parceled
out in declining levels from 1 to 8, and percentages are used to assign how
much CPU should be granted to each consumer group at each level. User sessions
for a consumer group with the highest level will receive resources before
sessions assigned to a lower level are allowed to receive resources, so this
method provides a mechanism to insure the highest-priority sessions receive at
least some CPU resources.
Automatic Consumer Group Switching. In some
circumstances, I would like to have a user session that has begun to consume
too many resources get switched automatically to a lower priority (for example,
an ad hoc query user starts a “not in your lifetime” query, or a user runs a
large report during peak OLTP processing hours). Using this method, the DRM can
be directed to automatically switch such a user session to a different resource
consumer group if the session is actively consuming resources beyond a certain
amount of time. Even more impressive, this method can estimate an approximate
time for the completion of the query and switch the session to a different
group before the query is executed.
Active Session Pool Queueing. In some cases, I
would like to be able to limit the activity for some groups of users based upon
their maximum number of active sessions. This method detects when a specified
threshold of user sessions has been reached for a consumer group and queues
sessions until a “slot” is available. This method can also detect if a queued
task has exceeded a specified time threshold, automatically terminate the
queued task and return an error.
Maximum Estimated Execution Time. The DRM can
also be directed to terminate an operation that has exceeded a specified
maximum execution time. DRM will terminate the session and return a trappable
error.
Maximum Degree of Parallelism. The DRM can limit
the maximum degree of parallel processing for any operation within the plan.
Undo Pool Quota. Finally, the DRM can be directed
to monitor the amount of undo (i.e. rollback) space being used by a resource
consumer group. When undo utilization reaches the specified threshold, the DRM
will terminate the current DML generating the redo log entries; no other
members of that consumer group will be allowed to continue DML operations until
sufficient redo space is available.
SYSTEM_PLAN. Oracle supplies an initial, default
resource plan named SYSTEM_PLAN. This plan implements a CPU utilization resource
allocation method to divide and prioritize CPU resources to three resource
consumer groups:
-
SYSTEM_GROUP. This is the initial consumer group for the SYS and
SYSTEM users, and it assigns 100% of Level 1 CPU utilization to these users. -
OTHER_GROUPS. This is the initial consumer group for all sessions
not part of the active resource plan and it assigns 100% of Level 2 CPU
utilization. There must always be a plan directive for this group in any plan. -
LOW_GROUP. This consumer group is initially unassigned to any
user; it assigns 100% of Level 3 CPU utilization.
Creating a Simple DRM Plan. To illustrate how easy
it is to create a simple resource plan, I will utilize one of the
CREATE_SIMPLE_PLAN procedures of the DBMS_RESOURCE_MANAGER Oracle-supplied
package for maintaining DRM plans.
I will create a new plan named PEAKTIME that allocates
resources using the CPU allocation method: 70% to my prime user group, OLTP,
20% to DSS, my decision support resource group, and 10% to my ad hoc query user
group, ADHOC:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
simple_plan => ‘PEAKTIME’
,consumer_group1 => ‘OLTP’
,group1_cpu => 70
,consumer_group2 => ‘DSS’
,group2_cpu => 20
,consumer_group3 => ‘ADHOC’
,group3_cpu => 10
);
END;
/
Querying DRM Settings. I have prepared some
queries to prove the success of my initial DRM plan creation and for future DRM
management efforts. Listing 1.2
displays all resource consumer groups and their attributes, and Listing 1.3 shows all resource
plans and their attributes.
Finally, Listing
1.4 shows the results of setting up the PEAKTIME plan (shown in Listing 1.1 for sake of
completeness). Note that when a plan is created using the supplied package
procedure DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN, the two mandatory
additional subplans – SYS_GROUP and OTHER _GROUPS – are created automatically,
with their appropriate CPU Level 1 and Level 2 100% allocations.
Next Steps. So far, so good! The next article will
delve into creating more complex DRM plans and explore methods for maintaining
existing DRM plans via the Oracle-supplied packages DBMS_RESOURCE_MANAGER and
DBMS_RESOURCE_MANAGER_PRIVS.
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
