Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 18, 2004

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

By Jim Czuprynski



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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date