Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions. The following sections provide an overview of the Resource Manager:
What Problems Does the Resource Manager Address?
When database resource allocation decisions are left to the operating system, you may encounter the following problems:
Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.
The operating system deschedules database servers while they hold latches, which is inefficient.
Inappropriate allocation of resources
The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.
Inability to manage database-specific resources, such as parallel execution servers and active sessions
How Does the Resource Manager Address These Problems?
The Resource Manager helps to overcome these problems by allowing the database more control over how hardware resources are allocated. In an environment with multiple concurrent users sessions that run jobs with differing priorities, all sessions should not be treated equally. The Resource Manager enables you to classify sessions into groups based on session attributes, and to then allocate resources to those groups in a way that optimizes hardware utilization for your application environment.
With the Resource Manager, you can:
Guarantee certain sessions a minimum amount of processing resources regardless of the load on the system and the number of users.
Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage can be given to ROLAP (relational online analytical processing) applications than to batch jobs.
Limit the degree of parallelism of any operation performed by members of a group of users.
Create an active session pool. An active session pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate. The active session pool limits the total number of sessions actively competing for resources, thereby enabling active sessions to make faster progress.
Manage runaway sessions or calls by detecting when they consume more than a specified amount of CPU or I/O. Such sessions can be automatically terminated or switched into a different (lower priority) group.
Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit.
Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.
Configure an instance to use a particular scheme for allocating resources. You can dynamically change the scheme, for example, from a daytime scheme to a nighttime scheme, without having to shut down and restart the instance. You can also schedule a scheme change with Oracle Scheduler. See Chapter 26, "Oracle Scheduler Concepts" for more information.
Elements of the Resource Manager
The elements of the Resource Manager are described in the following table.
You use the
DBMS_RESOURCE_MANAGER PL/SQL package to create and maintain these elements. The elements are stored in tables in the data dictionary. You can view information about them with data dictionary views.
See Also:"Resource Manager Data Dictionary Views"
About Resource Consumer Groups
A resource consumer group (consumer group) is a collection of user sessions that are grouped together based on their processing needs. When a session is created, it is automatically mapped to a consumer group based on mapping rules that you set up. As a database administrator (DBA), you can manually switch a session to a different consumer group. Similarly, an application can run a PL/SQL package procedure that switches its session to a particular consumer group.
Because the Resource Manager allocates resources (such as CPU) only to consumer groups, when a session becomes a member of a consumer group, its resource allocation is then determined by the allocation for the consumer group. By default, each session in a consumer group shares the resources allocated to that group with other sessions in the group in a round robin fashion.
There are three special consumer groups that are always present in the data dictionary. They cannot be modified or deleted. They are:
This is the initial consumer group for all sessions created by user accounts
SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules.
This is the initial consumer group for all sessions started by user accounts other than
SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules.
DEFAULT_CONSUMER_GROUPcannot be named in a resource plan directive.
This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan, including sessions that belong to
OTHER_GROUPSmust have a resource plan directive specified in every plan. It cannot be explicitly assigned to sessions through mapping rules.
About Resource Plan Directives
The Resource Manager allocates resources to consumer groups according to the set of resource plan directives (directives) that belong to the currently active resource plan. There is a parent-child relationship between a resource plan and its resource plan directives. Each directive references one consumer group, and no two directives for the currently active plan can reference the same consumer group.
A directive has a number of ways in which it can limit resource allocation for a consumer group. For example, it can control how much CPU the consumer group gets as a percentage of total CPU, and it can limit the total number of sessions that can be active in the consumer group. See "About Resource Allocation Methods" for more information.
About Resource Plans
In addition to the resource plans that are predefined for each Oracle database, you can create any number of resource plans. However, only one resource plan is active at a time. When a resource plan is active, each of its child resource plan directives controls resource allocation for a different consumer group. Each plan must include a directive that allocates resources to the consumer group named
OTHER_GROUPS applies to all sessions that belong to a consumer group that is not part of the currently active plan.
Note:Although the term "resource plan" (or just "plan") denotes one element of the Resource Manager, in this chapter it is also used to refer to a complete resource plan schema, which includes the resource plan element itself, its resource plan directives, and the consumer groups that the directives reference. For example, when this chapter refers to the
DAYTIMEresource plan, it could mean either the resource plan element named
DAYTIME, or the particular resource allocation schema that the
DAYTIMEresource plan and its directives define. Thus, for brevity, it is acceptable to say, "the
DAYTIMEplan favors interactive applications over batch applications."
Example: A Simple Resource Plan
Figure 25-1 shows a simple resource plan for an organization that runs online transaction processing (OLTP) applications and reporting applications simultaneously during the daytime. The currently active plan,
DAYTIME, allocates CPU resources among three resource consumer groups. Specifically,
OLTP is allotted 75% of the CPU time,
REPORTS is allotted 15%, and
OTHER_GROUPS receives the remaining 10%.
Description of "Figure 25-1 A Simple Resource Plan"
Oracle Database provides a procedure (
CREATE_SIMPLE_PLAN) that enables you to quickly create a simple resource plan. This procedure is discussed in "Creating a Simple Resource Plan".
Note:The currently active resource plan does not enforce allocation limits until CPU usage is at 100%. If the CPU usage is below 100%, the database is not CPU-bound and hence there is no need to enforce limits to ensure that all sessions get their designated resource allocation.
In addition, when limits are enforced, unused allocation by any consumer group can be used by other consumer groups. In the previous example, if the
OLTP group does not use all of its allocation, the Resource Manager permits the
REPORTS group or
OTHER_GROUPS group to use the unused allocation.
Instead of referencing a consumer group, a resource plan directive (directive) can reference another resource plan. In this case, the plan is referred to as a subplan. The subplan itself has directives that allocate resources to consumer groups and other subplans. The resource allocation scheme then works like this: The top resource plan (the currently active plan) divides resources among consumer groups and subplans. Each subplan allocates its portion of the total resource allocation among its consumer groups and subplans. You can create hierarchical plans with any number of subplans.
You create a resource subplan in the same way that you create a resource plan. There is no difference between a plan and a subplan. A plan becomes a subplan only because you use it as such.
Example: A Resource Plan with Subplans
In this example, the Great Bread Company allocates the CPU resource as shown in Figure 25-2. The figure illustrates a top plan (
GREAT_BREAD) and all of its descendents. For simplicity, the requirement to include the
OTHER_GROUPS consumer group is ignored, and resource plan directives are not shown, even though they are part of the plan. Rather, the CPU percentages that the directives allocate are shown along the connecting lines between plans, subplans, and consumer groups.
Description of "Figure 25-2 A Resource Plan With Subplans"
GREAT_BREAD plan allocates resources as follows:
20% of CPU resources to the consumer group
60% of CPU resources to subplan
SALES_TEAM, which in turn divides its share equally between the
20% of CPU resources to subplan
DEVELOP_TEAM, which in turn divides its resources equally between the
It is possible for a subplan or consumer group to have more than one parent. An example would be if the
MARKET group were included in the
SALES_TEAM subplan. However, a plan cannot contain any loops. For example, the
SALES_TEAM subplan cannot have a directive that references the
See Also:"Putting It All Together: Oracle Database Resource Manager Examples" for an example of a more complex resource plan.
About Resource Allocation Methods
Resource plan directives specify how resources are allocated to resource consumer groups or subplans. Each directive can specify a number of different methods for allocating resources to its consumer group or subplan. The following sections summarize these resource allocation methods:
This method enables you to specify how CPU resources are to be allocated among consumer groups and subplans. Multiple levels of CPU resource allocation (up to eight levels) provide a means of prioritizing CPU usage within a plan. Consumer groups and subplans at level 2 get resources that were not allocated at level 1 or were not consumed by a consumer group or subplan at level 1. Similarly, resource consumers at level 3 are allocated resources only when some allocation remains from levels 1 and 2. The same rules apply to levels 4 through 8. Multiple levels not only provide a way of prioritizing, but they provide a way of explicitly specifying how all primary and leftover resources are to be used.
See Figure 25-3 for an example of a multilevel plan schema.
Note:When there is only one level, unused allocation by any consumer group or subplan can be used by other consumer groups or subplans in the level.
Active Session Pool with Queuing
You can control the maximum number of concurrently active sessions allowed within a consumer group. This maximum defines the active session pool. An active session is a session that is in a call. It is considered active even if it is blocked, for example waiting for an I/O request to complete. When the active session pool is full, a session that is trying to process a call is placed into a queue. When an active session completes, the first session in the queue can then be removed from the queue and scheduled for execution. You can also specify a period after which a session in the execution queue times out, causing the call to terminate with an error.
An entire parallel execution session is counted as one active session.
This feature is useful if you want to limit the number of sessions in a consumer group that are competing for resources. For example, if a consumer group is used for processing long-running, parallel queries for reporting, you may decide to limit the number of active sessions to one to allow one report to complete as quickly as possible, without competing with other reports for CPU or for parallel query slaves.
Degree of Parallelism Limit
You can limit the maximum degree of parallelism for any operation within a consumer group. This limit applies to one operation within a consumer group; it does not limit the total degree of parallelism across all operations within the consumer group. However, you can combine both the degree of parallelism limit and the active session pool features to achieve the desired control.
This method enables you to control resource allocation by specifying criteria that, if met, causes the automatic switching of a session to a specified consumer group. Typically, this method is used to switch a session from a high-priority consumer group—one that receives a high proportion of system resources—to a lower priority consumer group because that session exceeded the expected resource consumption for a typical session in the group.
See "Specifying Automatic Switching by Setting Resource Limits" for more information.
Canceling SQL and Terminating Sessions
You can also specify directives to cancel long-running SQL queries or to terminate long-running sessions based on the amount of system resources consumed. See "Specifying Automatic Switching by Setting Resource Limits" for more information.
You can specify a maximum execution time allowed for an operation. If the database estimates that an operation will run longer than the specified maximum execution time, the operation is terminated with an error. This error can be trapped and the operation rescheduled.
You can specify an undo pool for each consumer group. An undo pool controls the total amount of undo for uncommitted transactions that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current DML statement generating the undo is terminated. No other members of the consumer group can perform further data manipulation until undo space is freed from the pool.
Idle Time Limit
You can specify an amount of time that a session can be idle, after which it is terminated. You can also specify a more stringent idle time limit that applies to sessions that are idle and blocking other sessions.
You may, as an administrator with the
ADMIN option, choose to grant the administrative privilege to other users or roles. This is possible using the
DBMS_RESOURCE_MANAGER_PRIVS PL/SQL package. The relevant package procedures are listed in the following table.
The following PL/SQL block grants the administrative privilege to user
SCOTT, but does not grant
ADMIN option. Therefore,
SCOTT can execute all of the procedures in the
DBMS_RESOURCE_MANAGER package, but
SCOTT cannot use the
GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privilege to others.
BEGIN DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE( GRANTEE_NAME => 'SCOTT', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', ADMIN_OPTION => FALSE); END;
You can revoke this privilege using the
ADMINISTER_RESOURCE_MANAGERsystem privilege can only be granted or revoked using the
DBMS_RESOURCE_MANAGER_PRIVSpackage. It cannot be granted or revoked through the SQL
See Also:Oracle Database PL/SQL Packages and Types Reference. contains detailed information about the Resource Manager packages: