About Oracle Database Resource Manager

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.

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.

Example: A Simple Resource Plan

Figure 26-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 26-1 A Simple Resource Plan

Description of "Figure 26-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".

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 26-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 26-2 A Resource Plan With Subplans

Description of "Figure 26-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.

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 that were allocated at level 1 but were not completely consumed by the consumer groups or subplans 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.

Table 26-1 illustrates a simple resource plan with three levels.

High priority applications run within HIGH_GROUP, which is allocated 80% of CPU. Because HIGH_GROUP is at level one, it gets priority for CPU utilization, but only up to 80% of CPU. This leaves a remaining 20% of CPU to be shared 50-50 by LOW_GROUP and the MAINT_SUPLAN at level 2. Any unused allocation from levels 1 and 2 are then available to OTHER_GROUPS at level 3. Because OTHER_GROUPS has no sibling consumer groups or subplans at its level, 100% is specified.

Within a particular level, CPU allocations are not fixed. If there is not sufficient load in a particular consumer group or subplan, residual CPU can be allocated to remaining consumer groups or subplans. Thus, when there is only one level, unused allocation by any consumer group or subplan can be redistributed to other "sibling" consumer groups or subplans. If there is more than one level, then the unused allocation is distributed to the consumer groups or subplans at the next level. If the last level has unused allocations, these allocations can be redistributed to all other levels in proportion to their designated allocations.

As an example of redistribution of unused allocations from one level to another, if during a particular period, HIGH_GROUP consumes only 25% of CPU, then 75% is available to be shared by LOW_GROUP and MAINT_SUBPLAN. Any unused portion of the 75% at level 2 is then made available to OTHER_GROUPS at level 3. However, if OTHER_GROUPS has no session activity at level 3, the 75% at level 2 is can be redistributed to all other consumer groups and subplans in the plan proportionally.

Maximum Utilization Limit

In the previous scenario, suppose that due to inactivity elsewhere, LOW_GROUP acquires 90% of CPU. Suppose that you do not want to allow LOW_GROUP to use 90% of the server because you do not want non-critical sessions to inundate the CPUs. The MAX_UTILIZATION_LIMIT attribute of resource plan directives can prevent this situation. This attribute enables you to impose an absolute upper limit on CPU utilization for a resource consumer group. This absolute limit overrides any redistribution of CPU within a plan. Table 26-2 shows a variation of the previous plan. In this plan, using MAX_UTILIZATION_LIMIT, CPU utilization is capped at 75% for LOW_GROUP and 75% for OTHER_GROUPS. (Note that the sum of all maximum utilization limits can exceed 100%. Each limit is applied independently.)

You can also use the MAX_UTILIZATION_LIMIT attribute as the sole means of limiting CPU utilization for consumer groups, without specifying level limits.

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 actively processing a transaction or SQL statement. Specifically, an active session is either in a transaction, holding a user enqueue, or has an open cursor and has not been idle for over 5 seconds. An active session 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. A long-running query is defined as query that runs for several minutes or hours.

Active session limits should be used only for decision support workloads, not OLTP workloads. In addition, active session limits should not be used to implement connection pooling.

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.