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:
SQL> COL name FORMAT A12 HEADING 'Resource|Consumer|Group'
SQL> COL active_sessions FORMAT 9999 HEADING 'Act|Sess'
SQL> COL execution_waiters FORMAT 9999 HEADING 'Exec|Wtrs'
SQL> COL requests FORMAT 9999 HEADING 'Reqs'
SQL> COL cpu_wait_time FORMAT 9999999 HEADING 'CPU|Wait|Time'
SQL> COL cpu_waits FORMAT 9999999 HEADING 'CPU|Waits'
SQL> COL consumed_cpu_time FORMAT 9999999 HEADING 'CPU|Time|Used'
SQL> COL yields FORMAT 9999 HEADING 'Ylds'
SQL> COL queue_length FORMAT 99999 HEADING 'Queue|Len'
SQL> COL current_undo_consumption FORMAT 99999 HEADING 'Curr|UNDO|Used'
SQL>
SQL> SELECT
2 name
3 ,active_sessions
4 ,execution_waiters
5 ,requests
6 ,cpu_wait_time
7 ,cpu_waits
8 ,consumed_cpu_time
9 ,yields
10 ,queue_length
11 ,current_undo_consumption
12 FROM v$rsrc_consumer_group
13 ;
Resource CPU CPU Curr
Consumer Act Exec Wait CPU Time Queue UNDO
Group Sess Wtrs Reqs Time Waits Used Ylds Len Used
------------ ----- ----- ----- -------- -------- -------- ----- ------ ------
DSS 1 1 1 32656 167 17115 167 0 0
ADHOC 0 0 0 0 0 0 0 0 0
OTHER_GROUPS 1 0 5 0 0 17 0 0 0
OLTP 1 0 1 9207 377 37957 377 0 0
And here are the results when I switched my current
resource management plan to OFF-PEAK and reran the same tests for both users:
Resource CPU CPU Curr
Consumer Act Exec Wait CPU Time Queue UNDO
Group Sess Wtrs Reqs Time Waits Used Ylds Len Used
------------ ----- ----- ----- -------- -------- -------- ----- ------ ------
OTHER_GROUPS 1 0 5 0 1 12 0 0 0
DSS 1 0 1 7066 454 44917 454 0 0
OLTP 1 1 1 36774 116 12072 116 0 0
ADHOC 0 0 0 0 0 0 0 0 0
Listing
3.5 shows sample queries of dynamic views to measure how well a
resource plan is being utilized.
DRM Maintenance Procedures
Finally, DBMS_RESOURCE_MANAGER provides some basic
maintenance procedures that I have not discussed in detail:
|
Table 2.
DBMS_RESOURCE_MANAGER Maintenance Procedures
|
|
UPDATE_PLAN
|
Updates comments for an
existing Resource Plan
|
|
UPDATE_CONSUMER_GROUP
|
Updates comments for an
existing Resource Consumer Group
|
|
DELETE_CONSUMER_GROUP
|
Deletes an existing
Resource Consumer Group
|
And here are the
DBMS_RESOURCE_MANAGER_PRIVS maintenance procedures I have not discussed in detail:
|
Table 3.
DBMS_RESOURCE_MANAGER_PRIVS Maintenance Procedures
|
|
GRANT_SYSTEM_PRIVILEGE
|
Grants the
ADMINISTER_RESOURCE_MANAGER system privilege to the specified user; the
grantee is then allowed to use DBMS_RESOURCE_MANAGER to perform all DRM
maintenance activities
|
|
REVOKE_SYSTEM_PRIVILEGE
|
Revokes the
ADMINISTER_RESOURCE_MANAGER system privilege from the specified user
|
|
REVOKE_SWITCH_CONSUMER_GROUP
|
Revokes the ability of a
user, role, or PUBLIC to switch from one resource consumer group to another
|
Listing 3.6
and Listing 3.7 respectively
show examples for each of these procedures.
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
» 
Comment and Contribute
