About Oracle Database Resource Manager

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?
How Does the Resource Manager Address These Problems?
Elements of the Resource Manager
About Resource Allocation Methods
About Resource Manager Administration Privileges

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

Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.
Inefficient scheduling

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.

Element	Description
Resource consumer group	A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.
Resource plan	A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.
Resource plan directive	Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.

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.

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:

SYS_GROUP

This is the initial consumer group for all sessions created by user accounts SYS or SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules.
DEFAULT_CONSUMER_GROUP

This is the initial consumer group for all sessions started by user accounts other than SYS and SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules. DEFAULT_CONSUMER_GROUP cannot be named in a resource plan directive.
OTHER_GROUPS

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 DEFAULT_CONSUMER_GROUP. OTHER_GROUPS must have a resource plan directive specified in every plan. It cannot be explicitly assigned to sessions through mapping rules.

See Also:

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. 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 DAYTIME resource plan, it could mean either the resource plan element named DAYTIME, or the particular resource allocation schema that the DAYTIME resource plan and its directives define. Thus, for brevity, it is acceptable to say, "the DAYTIME plan 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%.

Figure 25-1 A Simple Resource Plan

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.

About Subplans

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.

Figure 25-2 A Resource Plan With Subplans

Description of "Figure 25-2 A Resource Plan With Subplans"

The GREAT_BREAD plan allocates resources as follows:

20% of CPU resources to the consumer group MARKET
60% of CPU resources to subplan SALES_TEAM, which in turn divides its share equally between the WHOLESALE and RETAIL consumer groups
20% of CPU resources to subplan DEVELOP_TEAM, which in turn divides its resources equally between the BREAD and MUFFIN consumer groups.

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 GREAT_BREAD plan.

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:

CPU
Active Session Pool with Queuing
Degree of Parallelism Limit
Automatic Consumer Group Switching
Canceling SQL and Terminating Sessions
Execution Time Limit
Undo Pool
Idle Time Limit

CPU

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.

Automatic Consumer Group Switching

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.

Execution Time Limit

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.

Undo Pool

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.

About Resource Manager Administration Privileges

You must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the Resource Manager. This privilege (with the ADMIN option) is granted to database administrators through the DBA role.

Being an administrator for the Resource Manager enables you to execute all of the procedures in the DBMS_RESOURCE_MANAGER PL/SQL package.

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.

Procedure	Description
`GRANT_SYSTEM_PRIVILEGE`	Grants the `ADMINISTER_RESOURCE_MANAGER` system privilege to a user or role.
`REVOKE_SYSTEM_PRIVILEGE`	Revokes the `ADMINISTER_RESOURCE_MANAGER` system privilege from a user or role.

The following PL/SQL block grants the administrative privilege to user SCOTT, but does not grant SCOTT the 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 REVOKE_SYSTEM_PRVILEGE procedure.

Note:

The ADMINISTER_RESOURCE_MANAGER system privilege can only be granted or revoked using the DBMS_RESOURCE_MANAGER_PRIVS package. It cannot be granted or revoked through the SQL GRANT or REVOKE statements.

See Also:

Oracle Database PL/SQL Packages and Types Reference. contains detailed information about the Resource Manager packages:

DBMS_RESOURCE_MANAGER
DBMS_RESOURCE_MANAGER_PRIVS