Oracle Database Resource Manager, Part 3: Conclusion - Page 2

March 18, 2004

Monitoring Resource Plan Utilization and Effectiveness

Now that I have my DRM resource plans in place, how do I
go about testing them? I could simply wait until my database server's CPU and
other system resources are utilized beyond the thresholds I have set up in my
plans. However, it is definitely a good idea to test if DRM is at least
detecting and acting upon the CPU utilization limits that the plans have set.

Listing
3.4 shows a simple PL/SQL anonymous block that invokes a numeric
function repeatedly to force high CPU utilization at a rapid rate to simulate
user activity so that DRM can detect this and perform its management of those
resources. All I need to do now is run this code block on one or more client
desktops - preferably logged in as users' sessions for which DRM plans have
been implemented - and monitor the results.

Oracle provides the V$RSRC_CONSUMER_GROUP dynamic view
that shows how effectively user sessions are utilizing the resource consumption
directives within the defined resource consumer groups. Here is a list of
statistics provided by this view:

Table 1.
V$RSRC_CONSUMER_GROUP Statistics

NAME

Resource Consumer Group

ACTIVE_SESSIONS

The number of currently active
sessions for this consumer group

EXECUTION_WAITERS

The total number of
currently active sessions that are waiting for an execution time slice
in which they'll be able to use CPU

REQUESTS

The total number of requests
executed by this consumer group

CPU_WAIT_TIME

The total amount of time
that sessions in this consumer group had to wait for a CPU execution time
slice

CPU_WAITS

The total number
of times that all sessions in this consumer group had to wait for a
CPU execution time slice

CONSUMED_CPU_TIME

The total amount of CPU
consumed by all sessions in this consumer group

YIELDS

The total number of times
that any session in this consumer group had to yield the CPU
for another session

QUEUE_LENGTH

How many sessions are waiting
in the queue

CURRENT_UNDO_CONSUMPTION

How much UNDO space (in
KB) is being consumed by the consumer group

To illustrate how this view can be used to measure the
performance of my current DRM configuration, I set my current resource manager
plan to PEAKTIME, logged on as two users in different resource consumer groups
(HR and SLSMGR), and then executed the PL/SQL anonymous block in Listing 3.4.

Here are the results as both sessions struggled with each
other to obtain CPU execution time slices under the PEAKTIME plan:

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: