Oracle Database Resource Manager, Part 3: Conclusion

Thursday Mar 18th 2004 by Jim Czuprynski
Share:

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 final article builds upon the concepts in the two previous articles, including the construction of more complex resource plans, monitoring DRM utilization, and general DRM maintenance.

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 final article builds upon the concepts in the two previous articles, including the construction of more complex resource plans, monitoring DRM utilization, and general DRM maintenance.

The previous article in this series presented some actual examples of how to use the supplied DRM packages to create new resource plans, resource consumer groups and resource directives that bind them together. The concluding article in this series will discuss the creation of more complex DRM plans via DBMS_RESOURCE_MANAGER, how to monitor DRM resource plan utilization, and some remaining tidbits on maintaining DRM plans, directives, and consumer groups.

Creating Complex Resource Plans

The prior article in this series showed how to use two procedures, CREATE_PLAN and CREATE_PLAN_DIRECTIVE, of the DBMS_RESOURCE_MANAGER package to create new plans and plan directives for specific resource allocations. To modify existing plans and directives, I can invoke the UPDATE_PLAN and UPDATE_PLAN_DIRECTIVE procedures, or I can simply drop an existing plan directive and recreate the directive again.

First, I will modify the existing PEAKTIME plan to handle the needs of my user community as set forth in the requirements I defined in the first article of this series. During peak business hours, online transaction processing (OLTP) user sessions must be given precedence over all other types of user sessions. I will also need to insure that decision support (DSS) users have precedence over ad hoc query generation, but when a DSS user session has been actively executing a query for more than 30 minutes, I will make sure that the DSS user is switched to the ADHOC plan instead. Finally, just to make sure that no ADHOC user session runs an abnormally large query, I will limit the maximum estimated execution time for the query to 45 minutes.

I will accomplish all this by updating the plan directives for the PEAKTIME plan directives to new values in Table 1 below:

Table 1. PEAKTIME Resource Plan Directives

Directive

Resource Plan Settings

OLTP

CPU_P1: 75 (75%)

DSS

CPU_P1: 20 (20%)

SWITCH_TIME: 1800 (1800 seconds = 30 minutes)

SWITCH_GROUP: ADHOC (Switch when SWITCH_TIME is exceeded)

ADHOC

CPU Level 1 allocation: 5%

MAX_EST_EXEC_TIME: 2400 (2400 seconds = 45 minutes)

Listing 3.1 shows the code to execute against the database to accomplish this.

Next, I will turn my attention to the OFF-PEAK plan, which is responsible for managing resources during off-peak business hours. I will need to set up plan directives that give any batch processing top priority, followed by DSS user sessions. I will also set up directives that give OLTP and ad hoc query processing relatively equal weights

I will accomplish this by dropping the OFF-PEAK plan and its related directives. Then I will create a new plan, BATCH, and create two plan directives, DSS and OTHER_GROUPS. (Remember, every plan, wherever it is in the plan hierarchy, must have a directive to OTHER_GROUPS as a default.) The new BATCH subplan will allocate 25% of its allocated CPU resources to user sessions in the DSS resource group, and 75% of its resources to any other group.

I will then create a resource plan directive for the BATCH subplan within the OFF-PEAK plan, and I will allocate 80% of all Level 1 CPU resources to that directive. This means that a user session assigned to the DSS resource consumer group under the BATCH subplan would get a maximum of 20% of all CPU resources available (25% of 80% = 20%).

To round out the OFF-PEAK plan, I will allocate a maximum of 15% CPU utilization for any user session assigned to the OLTP resource consumer group, but just 5% to ADHOC user sessions assigned to the ADHOC resource consumer group. Finally, just to keep the ADHOC resource consumer group from doing a lot of unauthorized inserting, updating or deleting, I will limit them to only 4M (4096K) of UNDO pool space.

I will set up the directives with the new values shown in Table 2 below:

Table 2. OFF-PEAK Resource Plan Directives

Plan / Subplan

Group or Subplan

Resource Plan Settings

OFF-PEAK

BATCH

New subplan

CPU Level 1 allocation: 80 (80%)

BATCH

OTHER_GROUPS

CPU Level 1 allocation: 0 (0%)

CPU Level 2 allocation: 75 (75%

BATCH

DSS

CPU Level 1 allocation: 0 (0%)

CPU Level 2 allocation: 25 (25%)

OFF-PEAK

OLTP

CPU Level 1 allocation: 15 (15%)

OFF-PEAK

ADHOC

CPU Level 1 allocation: 5 (5%)

Maximum UNDO Pool Usage: 4096 (4096K)

OFF-PEAK

OTHER_GROUPS

CPU Level 1 allocation: 0 (0%)

CPU Level 2 allocation: 0 (0%)

CPU Level 3 allocation: 100 (0%)

Listing 3.2 shows the code to execute against the database to accomplish this.

Finally, as a busy Oracle DBA, I like to let Oracle handle as much of the workload as possible, so I'll schedule two database jobs via the DBMS_JOB package so that the database can switch itself to off-peak processing mode at 18:00 every night, and switch itself back to peak processing mode at 06:00 every morning. Listing 3.3 shows an example of how to schedule the switchover and switchback of each resource plan via DBMS_JOB.



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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved