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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 15, 2004

Oracle Database Resource Manager, Part 1:Overview

By Jim Czuprynski

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 article provides a brief overview of how DRM works and then delves into the preparations necessary to implement a simple DRM configuration.

I have spent the last two weeks of 2003 reviewing the performance of our Oracle production database during the months of October and November, our peak processing season of the year. Perusing STATSPACK reports has shown me a few minor areas for improvement - an unexpected table scan here, a SQL query that needed a better hint, and a few indexes that needed rebuilding - yet I realized the database is pretty well tuned based on all the traditional metrics.

My next area of attack, then, is to improve application performance by allocating server resources more effectively. Our production cycle is not uncommon for customer service organizations: During peak demand periods for order taking and order-fulfillment services, on-line transaction processing (OLTP) user sessions must be given priority over sessions for all other online applications. While users running reports and ad hoc queries must be granted sufficient resources to perform their duties, I cannot allow read-only queries to grab server resources at the expense of my OLTP application users.

In addition, I need to balance the resources needed during peak versus off-peak periods. Like many customer service organizations, our peak periods spike in the late morning, drop off during lunchtime and then spike again in the mid-afternoon. However, late in the evenings, other batch processing tasks like bulk customer billing, data extraction, translation and loading (ETL) for our (eventual) data warehouse, and database backups and exports must take precedence.

Finally, it is really important that changes to database resource utilization happen either automatically or with only minimal DBA intervention. After all, our DBA team has better things to do than constantly monitor a few user sessions that are consuming resources beyond their measure! Fortunately, Oracle provides a solution for managing server and database resources to fulfill these requirements: Oracle Database Resource Management ("acronyzed" to DRM for the remainder of these articles).

DRM consists of four basic components:

Resource Consumer Groups. A resource consumer group is a collection of users with similar requirements for resource consumption. Users can be assigned to more than one resource consumer group, but each user's active session can only be assigned to one resource consumer group at a time. For example, I can construct separate consumer groups for OLTP users, one for decision support users and one for users who typically create ad hoc queries.

Resource Plans. In its simplest form, a resource plan describes the resources allocated to one or more resource consumer group(s). For example, I can specify the amount of CPU utilization permitted for a group of users.

A resource plan can also have multiple subplans that specify a finer grain of resource allocation detail. For example, I can create two subplans for my OLTP users, one for high-volume users and one for lower-volume users. Note that a subplan can have more than one parent plan, but Oracle will not permit a subplan to "loop back" upon itself.

Resource Plan Directives. Resource plan directives allocate resources among the resource consumer groups in the resource plan. Essentially, directives connect resource consumer groups or subplans to their resource plans. There is at least one resource plan directive for each entry in the resource plan.

Resource plan directives use resource allocation methods to determine how the DRM will allocate resources to a resource consumer group or resource plan. Several allocation methods are available:

CPU. As its name implies, this method controls how much CPU utilization will be permitted to user sessions for different resource consumer groups within a specific resource plan. CPU utilization is parceled out in declining levels from 1 to 8, and percentages are used to assign how much CPU should be granted to each consumer group at each level. User sessions for a consumer group with the highest level will receive resources before sessions assigned to a lower level are allowed to receive resources, so this method provides a mechanism to insure the highest-priority sessions receive at least some CPU resources.

Automatic Consumer Group Switching. In some circumstances, I would like to have a user session that has begun to consume too many resources get switched automatically to a lower priority (for example, an ad hoc query user starts a "not in your lifetime" query, or a user runs a large report during peak OLTP processing hours). Using this method, the DRM can be directed to automatically switch such a user session to a different resource consumer group if the session is actively consuming resources beyond a certain amount of time. Even more impressive, this method can estimate an approximate time for the completion of the query and switch the session to a different group before the query is executed.

Active Session Pool Queueing. In some cases, I would like to be able to limit the activity for some groups of users based upon their maximum number of active sessions. This method detects when a specified threshold of user sessions has been reached for a consumer group and queues sessions until a "slot" is available. This method can also detect if a queued task has exceeded a specified time threshold, automatically terminate the queued task and return an error.

Maximum Estimated Execution Time. The DRM can also be directed to terminate an operation that has exceeded a specified maximum execution time. DRM will terminate the session and return a trappable error.

Maximum Degree of Parallelism. The DRM can limit the maximum degree of parallel processing for any operation within the plan.

Undo Pool Quota. Finally, the DRM can be directed to monitor the amount of undo (i.e. rollback) space being used by a resource consumer group. When undo utilization reaches the specified threshold, the DRM will terminate the current DML generating the redo log entries; no other members of that consumer group will be allowed to continue DML operations until sufficient redo space is available.

SYSTEM_PLAN. Oracle supplies an initial, default resource plan named SYSTEM_PLAN. This plan implements a CPU utilization resource allocation method to divide and prioritize CPU resources to three resource consumer groups:

  • SYSTEM_GROUP. This is the initial consumer group for the SYS and SYSTEM users, and it assigns 100% of Level 1 CPU utilization to these users.

  • OTHER_GROUPS. This is the initial consumer group for all sessions not part of the active resource plan and it assigns 100% of Level 2 CPU utilization. There must always be a plan directive for this group in any plan.

  • LOW_GROUP. This consumer group is initially unassigned to any user; it assigns 100% of Level 3 CPU utilization.

Creating a Simple DRM Plan. To illustrate how easy it is to create a simple resource plan, I will utilize one of the CREATE_SIMPLE_PLAN procedures of the DBMS_RESOURCE_MANAGER Oracle-supplied package for maintaining DRM plans.

I will create a new plan named PEAKTIME that allocates resources using the CPU allocation method: 70% to my prime user group, OLTP, 20% to DSS, my decision support resource group, and 10% to my ad hoc query user group, ADHOC:

        simple_plan => 'PEAKTIME'
       ,consumer_group1 => 'OLTP'
       ,group1_cpu => 70
       ,consumer_group2 => 'DSS'
       ,group2_cpu => 20
       ,consumer_group3 => 'ADHOC'
       ,group3_cpu => 10

Querying DRM Settings. I have prepared some queries to prove the success of my initial DRM plan creation and for future DRM management efforts. Listing 1.2 displays all resource consumer groups and their attributes, and Listing 1.3 shows all resource plans and their attributes.

Finally, Listing 1.4 shows the results of setting up the PEAKTIME plan (shown in Listing 1.1 for sake of completeness). Note that when a plan is created using the supplied package procedure DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN, the two mandatory additional subplans - SYS_GROUP and OTHER _GROUPS - are created automatically, with their appropriate CPU Level 1 and Level 2 100% allocations.

Next Steps. So far, so good! The next article will delve into creating more complex DRM plans and explore methods for maintaining existing DRM plans via the Oracle-supplied packages DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS.

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

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