|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-11
|PDF · Mobi · ePub|
Oracle Database Resource Manager (the Resource Manager) enables you to manage multiple workloads within a database that are contending for system and database resources.
The following sections provide an overview of the Resource Manager:
When database resource allocation decisions are left to the operating system, you may encounter the following problems with workload management:
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 cannot prioritize one task over another.
Inability to manage database-specific resources, such as parallel execution servers and active sessions
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 user 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 CPU regardless of the load on the system and the number of users.
Distribute available CPU 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.
Manage the order of parallel statements in the parallel statement queue. Parallel statements from a critical application can be enqueued ahead of parallel statements from a low priority group of users.
Limit the number of parallel servers that a group of users can use. This ensures that all the available parallel servers are not allocated to only one 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 in the following ways:
By placing an absolute limit on the percentage of CPU that a group can consume
By detecting when a session or call consumes more than a specified amount of CPU or I/O, and then automatically either terminating the session or call, or switching it to a consumer group that is allocated a small amount of CPU, which would in effect mitigate the impact of the runaway session or call
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.
Allow a database to use different resource plans, based on changing workload requirements. You can dynamically change the resource plan, for example, from a daytime resource plan to a nighttime resource plan, without having to shut down and restart the instance. You can also schedule a resource plan change with Oracle Scheduler. See Chapter 28, "Oracle Scheduler Concepts" for more information.
The elements of the Resource Manager are described in the following table.
|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.
See Also:"Resource Manager Data Dictionary Views"
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 determined by the allocation for the consumer group.
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 consumer group contains all sessions that have not been assigned to a consumer group. Every resource plan must contain a directive to
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 several 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 "The Types of Resources Managed by the Resource Manager" for more information.
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."
Figure 27-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%.
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 allocations 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 allocations to ensure that all sessions get their designated resource allocation.
In addition, when allocations 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. To create a plan that is to be used only as a subplan, you use the
SUB_PLAN argument in the package procedure
In any top level plan, you can reference a subplan only once. A subplan is not required to have a directive to
OTHER_GROUPS and cannot be set as a resource plan.
In this example, the Great Bread Company allocates the CPU resource as shown in Figure 27-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.
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
RETAIL consumer groups
20% of CPU resources to subplan
DEVELOP_TEAM, which in turn divides its resources equally between the
MUFFIN consumer groups
It is possible for a subplan or consumer group to have multiple parents. 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.
You may, as an administrator with the
ADMIN option, choose to grant the administrative privilege to other users or roles. To do so, use 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
HR, but does not grant
ADMIN option. Therefore,
HR can execute all of the procedures in the
DBMS_RESOURCE_MANAGER package, but
HR cannot use the
GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privilege to others.
BEGIN DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE( GRANTEE_NAME => 'HR', 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: