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:

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:

And here are the DBMS_RESOURCE_MANAGER_PRIVS maintenance procedures I have not discussed in detail:

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