Oracle9i Database Resource Manager

The ability to easily and accurately perform system and resource management is critical to maintaining application and database performance, scalability and availability. Oracle8i introduced the Database Resource Manager to enable the prioritization of database operations by applying system resources according to business priorities defined by the database administrator. Oracle9i features a significantly enhanced Database Resource Manager with new capabilities which enable automated and proactive management of database resources to achieve service level objectives.

Availability of the database encompasses both its functionality and performance. If the database is available, but users are not getting the level of performance they need, availability and service level objectives are not being met. Application performance, to a large extent, is impacted by how resources are distributed among various applications accessing the database. The Oracle8i Database Resource Manager enables administrators to allocate CPU resources among database users and applications as well as limit the degree of parallelism of any operation. Using the Database Resource Manager, it is possible to balance one user˳ consumption against other users and partition system resources among tasks of varying importance to achieve overall enterprise goals. In Oracle9i, the Database Resource Manager can automatically detect long running operations and limit their resource consumption thereby preventing system wide performance degradation. Under certain extreme cases, it can even prevent an operation from starting if it is expected to run for an unacceptably long time consuming excessive system resources.

The Database Resource Manager allocates resources among users or applications based on a resource plan specified by the database administrator. A resource plan consists of directives specifying how resources should be distributed among various active users or applications. Users or applications are grouped into different resource consumer groups based on their resource requirements and business priority.

In a Real Application Clusters (RAC) environment, member instances can be assigned different resource plans to tailor them to support different workloads. For example, in a two node cluster, one instance can use a resource plan which allocates most of its resources to online users while the other instance can use a different plan allocating most of its resources to batch operations. This allows different applications or users to use the same database without impacting each other.

Using resource plan directives, an administrator can define how resources should be distributed among various resource consumer groups. For example, a resource plan can be created to distribute system CPU resources among High, Medium and Low priority resource consumer groups in 80, 15 and 5 ratio. The resource available to an user or application depends on resources allocated to the resource consumer group it belongs to. The grouping of various database users or applications among different resource consumer groups is done when a resource plan is created. However it can also be dynamically changed by the Database Resource Manager based on an administrator defined criteria. For example, a database session can be automatically moved from the High to Medium priority resource consumer group if the operation it is executing is not completed within 5 minutes.

It is also possible to limit the number of long running operations active simultaneously. It is therefore possible to direct the Database Resource Manager to ensure that not more than, let us say 3, Medium or Low priority operations are running concurrently. If the number of active operations belonging to these groups exceeds 3, new operations will be queued and allowed to run only when one of the three running operations has completed. Additionally, since the Database Resource Manager has the ability to estimate the execution time of an operation before beginning its execution. Database administrators can use it prevent unacceptably long resource intensive operations from starting by specifying a maximum estimated execution time limit at the resource consumer group level.

These capabilities allow the administrator to proactively limit the resources consumed by long batch operations and hence prevent them from impacting online users.

Plans within plans, or subplans can be defined. Subplans can be used to further re-partition resources within an application. Available resources can also be allocated within a resource plan at multiple levels. Levels are used to specify resource consumer group allocation limits and how unused resources should be distributed.

These capabilities of the Database Resource Manager provide database administrators unprecedented control over the distribution of database resources and enable them to implement very simple, yet powerful resource allocation policies.

To illustrate how the database resource manager can be used in the real world, lets examine how this might be used with an ATM banking application. The primary goal of such an application is to ensure a quick and predictable response time to ATM users. At the same time the database that supports this application can also be used to perform other batch processing such as summarizing monthly and/or yearly activities. In order to ensure that these operations do not have any undesirable impact on the response time for ATM users, a resource plan such as the following can be created:

Table 1: Banking Application Resource Plan

The plan shown above ensures that at least 90% of system CPU resources are made available to service ATM users by capping the resource utilization of batch processing operations to 10%. However if there are no other activities going on the system (i.e. batch operations are not consuming any CPU resources), 100% of CPU resources will be used to service ATM users.

The previous plan can be further refined by specifying how resources are allocated among different classes of ATM users. Let us assume that the bank categorizes its customers into two groups i.e. premium and standard based on the amount of their assets deposited at the bank. In order to ensure better service to premium customers, an "ATM Users" subplan is created. This subplan governs how resources allocated to ATM users will be sub-divided. In this case, premium users are allocated 80% of ATM users resources while standard users get the remaining 20%.

Using a subplan is different than creating two different ATM consumer groups in the primary plan. If we had done that, any resources allocated but not used by premium customers would have been distributed across all other resource consumer groups including "Batch Users". ATM users subplan ensures that any resources unused by premium users resource consumer group are first offered to standard ATM users before being passed on to other resource consumer groups.

The resource plan discussed so far allocates resources at a single level i.e. any allocation not used by a subplan or resource consumer group is proportionally distributed among other subplans or resource consumer groups. If the bank introduces a third resource consumer group to this plan to ensure availability of at least 5% of CPU resources for printing statements for its customers. It is required that any resources not used by ATM users should be first offered for printing statements before being made available for batch processing. This can be accomplished by creating a multilevel plan such as the one shown below:

The revised plan ensures that at least 5% of CPU resources are made available for printing customer statements by limiting the resource allocation at level 1 to 95%. Any resources which are either not allocated or not consumed at level 1 is made available to level 2 resource consumer groups/sub plans. Any unused resources from the ATM users quota is, therefore, first made available for printing statements before batch operations can use it. If we had created a single level plan with CPU resources divided in 90, 5, 5 ratio, unused resources allocated to ATM users would have been equally divided between printing statements and back office users.

For a database supporting a packaged ERP or CRM application, the requirements could be very different than those of the banking database. Unlike the database supporting ATM application where transactions tend to be short and predictable in nature, the work load of an ERP database can be highly varied. There may be a mixture of short transactions and long running batch jobs such as large parallel queries. Also, unlike the ATM database, it may not be possible to classify transactions beforehand. In such a system, the goal is to provide quick response time to OLTP users by limiting the resources consumed by batch jobs. In order to meet this goal, the following plan may be created:

Table 3 : Packaged Application Plan

This plan classifies the workload into two distinct groups, OLTP and BATCH. The high priority OLTP group is allocated 70% of CPU resources while the BATCH group gets the remaining 30% as well as the unused OLTP allocation. An operation is allowed to run in the OLTP group for a maximum of 3 minutes. If the operation is not complete in this time interval, it is automatically switched to the BATCH group thereby reducing its allocation of CPU resources. This automatic resource consumer group switching feature enables the Oracle9i Database Resource Manager to detect long running operations and limit their impact on the overall system performance.

With a small portion of CPU resources available to them, batch jobs may not get completed in time if too many of them are being executed concurrently. In order to prevent this, the plan restricts the maximum number of concurrently active batch jobs to 5. Once this maximum is reached, all other batch jobs will be queued and will be allowed to run only after one of the five running jobs gets completed. Finally, any batch job which is likely to run for more than 12 hours is not allowed to begin execution allowing the database to block potentially rogue operations proactively.

This example demonstrates the immense power the Database Resource Manager provides to database administrators in maximizing the return on their hardware investment by letting them take control of how system resources are deployed. In addition, it automatically and proactively governs resource utilization to achieve service level agreements with minimal human intervention.

In a data warehouse, the primary goal is to maximize throughput, and responsiveness may not be as critical as in an OLTP or ERP application environment. A data warehouse might support several short, critical operations, but most of its workload is made up of long running queries.

Table 4: Data Warehouse Plan

Using the plan shown above, critical operations are executed as a part of the "High Priority" resource consumer group and are allocated 60% of CPU resources. Since it may not always be possible for administrators to know beforehand whether a job should be run at medium or low priority, all "non-critical" operations start in the "Medium Priority" group. If they are not completed within an hour or if they are estimated to run for more than an hour, they will be switched to the "Low Priority" group. Any non-critical job which is estimated to run for more than 15 hours will not be allowed to start at all.

The only constant is change. Resource plans and policies that achieve the desired results in the morning, may not do so in the evening. All aspects of the Database Resource Manager configuration, therefore, may be changed while the database is running. Plans, subplans, levels, membership in resource consumer groups and resource allocation directives can all be changed dynamically without restarting the database instance.

A database administrator can therefore create many different resource plans and resource consumer groups in a database. Any one of these plans can be made the default which gets activated automatically at the time the database is started. Other plans can act as alternate plans for different times of the day, month, quarter or other times that require a different resource allocation scheme. Using this ability, the jobs blocked from running in the previous examples due to the maximum estimated execution time specification may be allowed to run at a later time.

The resource allocation mechanism provided by the Database Resource Manager is significantly superior to a conventional priority based scheme. Using percentages to allocate CPU ensures that all resource consumer groups receive a certain minimum resource and hence can not be starved by a high demand from other groups. In addition, resource allocation policies remain constant across hardware changes. The distribution of resources among users or applications, therefore, remains the same no matter which hardware or operating system the database operates on.

The Database Resource Manager is fully integrated into the database security system. Administrators can create, update or delete resource plans and resource consumer groups using either a PL/SQL interface or Oracle9i Enterprise Manager console. The administrator assigns a user a default consumer group and required privileges. A user can switch his or her session˳ resource consumer group to change the resources available to it if the user has been granted the necessary privileges. In addition, the administrator can change a user˳ default consumer group or move any active session from one group to another dynamically.

Oracle9i continues to support the use of user profiles to implement hard resource limits. The Database Resource Manager provides a more sophisticated way of managing database resources since it can balance different requests for service against each other within the defined resource allocation plan and proactively control the resource consumption of long running resource intensive processes.

Finally, the Adaptive Degree of Parallelism (ADOP) feature takes the Database Resource Manager allocations into account while choosing the optimal degree of parallelism for a parallel operation. ADOP attempts to optimize system utilization by automatically adjusting the degree of parallelism for parallel queries and parallel DML operations.

Fig. 2 : Oracle Enterprise Manger interface for managing the Database Resource Manager

Traditionally, all users and applications have been provided equal access to database resources. While this approach works in many cases, it fails to recognize a fundamental business need i.e. some activities in the enterprise are more important than others. With the introduction of the Database Resource Manager in Oracle8i, database administrators were, for the first time, able to implement resource allocation policies to guarantee measured database service to enterprise applications and users. Enhancements made in Oracle9i make the Database Resource Manager more automated, powerful and proactive. Oracle9i Database Resource Manager makes it extremely easy to deliver predictable service level with minimal human intervention and facilitates almost unlimited system scalability without compromising performance.