24 Using the Database Resource Manager

Oracle Database provides database resource management capability through its Database Resource Manager. This chapter introduces you to its use and contains the following topics:

What Is the Database Resource Manager?
Administering the Database Resource Manager
Creating a Simple Resource Plan
Creating Complex Resource Plans
Managing Resource Consumer Groups
Enabling the Database Resource Manager
Putting It All Together: Database Resource Manager Examples
Monitoring and Tuning the Database Resource Manager
Interaction with Operating-System Resource Control
Viewing Database Resource Manager Information

Note:
This chapter discusses the use of the Oracle-supplied DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS packages to administer the Database Resource Manager. An easier way to administer the Database Resource Manager is with the graphical user interface of Enterprise Manager.

What Is the Database Resource Manager?

The main goal of the Database Resource Manager is to give the Oracle Database server more control over resource management decisions, thus circumventing problems resulting from inefficient operating system management.

What Problems Does the Database 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 Database Resource Manager Address These Problems?

The Oracle Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated.

Specifically, using the Database Resource Manager, you can:

Guarantee certain users 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 may be given to ROLAP (relational on-line 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. This 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.
Allow automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.
Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit
Create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.
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 method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.
Allow the cancellation of long-running SQL statements and the termination of long-running sessions.

What Are the Elements of the Database Resource Manager?

The elements of database resource management, which you define through the Database Resource Manager packages, are described below.

Element	Description
Resource consumer group	User sessions grouped together based on resource processing requirements.
Resource plan	Contains directives that specify how resources are allocated to resource consumer groups.
Resource allocation method	The method/policy used by the Database Resource Manager when allocating for a particular resource; used by resource consumer groups and resource plans. The database provides the resource allocation methods that are available, but you determine which method to use.
Resource plan directive	Used by administrators to associate resource consumer groups with particular plans and allocate resources among resource consumer groups.

You will learn how to create and use these elements in later sections of this chapter.

Understanding Resource Plans

This section briefly introduces the concept of resource plans. Included are some illustrations of simple resource plans. More complex plans are included in the examples presented later ("Putting It All Together: Database Resource Manager Examples"), after it has been explained how to build and maintain the elements of the Database Resource Manager.

Resource plans specify the resource consumer groups belonging to the plan and contain directives for how resources are to be allocated among these groups. Plans can also contain subplans, and can designate that resources be allocated among both resource consumer groups and subplans. Subplans then allocate their share of the allocation among their own consumer groups and subplans. You can define any number of resource plans, but only one can be active at any time on a particular Oracle Database instance.

You use the DBMS_RESOURCE_MANAGER package to create and maintain the elements of the Database Resource Manager: resource plans, resource consumer groups, and resource plan directives. Plan information is stored in tables in the data dictionary. Several views are available for viewing plan data.

A Simple Resource Plan

The first illustration , shown in Figure 24-1, is of a simple plan, where the plan allocates resources among resource consumer groups only. The Great Bread Company has a plan called GREAT_BREAD that allocates CPU resources among three resource consumer groups. Specifically, SALES is allotted 60% of the CPU time, MARKET is allotted 20%, and DEVELOP receives the remaining 20%.

Figure 24-1 A Simple Resource Management Plan

Description of "Figure 24-1 A Simple Resource Management 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 SALES group does not use all of its allocation, the Resource Manager permits the MARKET group or DEVELOP group to use the unused allocation.

A Resource Plan with Subplans

In addition to containing resource consumer groups, a plan can contain subplans . A subplan can occupy the same position as a resource consumer group in the plan hierarchy. For example, perhaps the Great Bread Company chooses to divide their CPU resource as shown in Figure 24-2. The figure illustrates a plan schema , which contains a top plan (GREAT_BREAD) and all of its descendents.

Figure 24-2 A Resource Plan With Subplans

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

In this case, the GREAT_BREAD plan still allocates 20% of CPU resources to the consumer group MARKET. However, now it allocates CPU resources to subplans SALES_TEAM (60%), which in turn divides its share equally between the WHOLESALE and RETAIL groups, and DEVELOP_TEAM (20%), which in turn divides its resources equally between the BREAD and MUFFIN groups.

It is possible for a subplan or consumer group to have more than one parent (owning plan), but there cannot be any loops in a plan schema. An example of a subplan having more that one parent would be if the Great Bread Company had a night plan and a day plan. Both the night plan and the day plan contain the SALES_TEAM subplan as a member, but perhaps with a different CPU resource allocation in each instance.

Note:

As explained in subsequent sections, the plans described should also contain a plan directive for OTHER_GROUPS. To present a simplified view, however, this plan directive is not shown.

Resource Consumer Groups

Resource consumer groups are groups of users, or sessions, that are grouped together based on their processing needs. Resource plan directives, discussed next, specify how resources are allocated among consumer groups and subplans in a plan schema.

Resource Plan Directives

How resources are allocated to resource consumer groups is specified in resource allocation directives. The Database Resource Manager provides several means of allocating resources.

CPU Method

This method enables you to specify how CPU resources are to be allocated among consumer groups or subplans. Multiple levels of CPU resource allocation (up to eight levels) provide a means of prioritizing CPU usage within a plan schema . 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. The same rules apply to levels 3, 4, and so on. 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 24-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 designates the active session pool. When a session cannot be initiated because the pool is full, the session is placed into a queue. When an active session completes, the first session in the queue can then be scheduled for execution. You can also specify a timeout period after which a job in the execution queue (waiting for execution) will timeout, causing it to terminate with an error.

An entire parallel execution session is counted as one active session.

Degree of Parallelism Limit

Specifying a parallel degree limit enables you to control the maximum degree of parallelism for any operation within a consumer group.

Automatic Consumer Group Switching

This method enables you to control resources by specifying criteria that, if met, causes the automatic switching of sessions to another consumer group. The criteria used to determine switching are:

Switch group: specifies the consumer group to which this session is switched if the other (following) criteria are met
Switch time: specifies the length of time that a session can execute before it is switched to another consumer group
Switch time in call: specifies the length of time that a session can execute before it is switched to another consumer group. Once the top call finishes, the session is restored to its original consumer group.
Use estimate: specifies whether the database is to use its own estimate of how long an operation will execute

The Database Resource Manager switches a running session to switch group if the session is active for more than switch time seconds. Active means that the session is running and consuming resources, not waiting idly for user input or waiting for CPU cycles. The session is allowed to continue running, even if the active session pool for the new group is full. Under these conditions a consumer group can have more sessions running than specified by its active session pool. Once the session finishes its operation and becomes idle, it is switched back to its original group.

If use estimate is set to TRUE, the Database Resource Manager uses a predicted estimate of how long the operation will take to complete. If the database estimate is longer than the value specified as the switch time, then the database switches the session before execution starts. If this parameter is not set, the operation starts normally and only switches groups when other switch criteria are met.

Switch time in call is useful for three-tier applications where the middle tier server is using session pooling. At the end of every top call, a session is switched back to its original consumer group--that is, the group it would be in had it just logged in. A top call in PL/SQL is an entire PL/SQL block being treated as one call. A top call in SQL is an individual SQL statement issued separately by the client being treated as a one call.

You cannot specify both switch time in call and switch time.

Canceling SQL and Terminating Sessions

You can also specify directives to cancel long-running SQL queries or to terminate long-running sessions. You specify this by setting CANCEL_SQL or KILL_SESSION as the switch group.

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 amount of total undo 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 redo 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 will be terminated. You can further restrict such termination to only sessions that are blocking other sessions.

Administering the Database Resource Manager

You must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the Database Resource Manager. Typically, database administrators have this privilege with the ADMIN option as part of the DBA (or equivalent) role.

Being an administrator for the Database Resource Manager lets you execute all of the procedures in the DBMS_RESOURCE_MANAGER package. These are listed in the following table, and their use is explained in succeeding sections of this chapter.

Procedure	Description
`CREATE_SIMPLE_PLAN`	Creates a simple resource plan, containing up to eight consumer groups, in one step. This is the quickest way to get started when you use this package.
`CREATE_PLAN`	Creates a resource plan and specifies its allocation methods.
`UPDATE_PLAN`	Updates a resource plan.
`DELETE_PLAN`	Deletes a resource plan and its directives.
`DELETE_PLAN_CASCADE`	Deletes a resource plan and all of its descendents.
`CREATE_CONSUMER_GROUP`	Creates a resource consumer group.
`UPDATE_CONSUMER_GROUP`	Updates a consumer group.
`DELETE_CONSUMER_GROUP`	Deletes a consumer group.
`CREATE_PLAN_DIRECTIVE`	Specifies the resource plan directives that allocate resources to resource consumer groups or subplans in a plan.
`UPDATE_PLAN_DIRECTIVE`	Updates plan directives
`DELETE_PLAN_DIRECTIVE`	Deletes plan directives
`CREATE_PENDING_AREA`	Creates a pending area (scratch area) within which changes can be made to a plan schema
`VALIDATE_PENDING_AREA`	Validates the pending changes to a plan schema
`CLEAR_PENDING_AREA`	Clears all pending changes from the pending area
`SUBMIT_PENDING_AREA`	Submits all changes for a plan schema
`SET_INITIAL_CONSUMER_GROUP`	Sets the initial consumer group for a user. This procedure has been deprecated. The database recommends that you use the `SET_CONSUMER_GROUP_MAPPING` procedure to specify the initial consumer group.
`SWITCH_CONSUMER_GROUP_FOR_SESS`	Switches the consumer group of a specific session
`SWITCH_CONSUMER_GROUP_FOR_USER`	Switches the consumer group of all sessions belonging to a specific user
`SET_CONSUMER_GROUP_MAPPING`	Maps sessions to consumer groups
`SET_CONSUMER_GROUP_MAPPING_PRI`	Establishes session attribute mapping priorities

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 package. This package contains the procedures listed in the table below.

Procedure	Description
`GRANT_SYSTEM_PRIVILEGE`	Grants `ADMINISTER_RESOURCE_MANAGER` system privilege to a user or role.
`REVOKE_SYSTEM_PRIVILEGE`	Revokes `ADMINISTER_RESOURCE_MANAGER` system privilege from a user or role.
`GRANT_SWITCH_CONSUMER_GROUP`	Grants permission to a user, role, or `PUBLIC` to switch to a specified resource consumer group.
`REVOKE_SWITCH_CONSUMER_GROUP`	Revokes permission for a user, role, or `PUBLIC` to switch to a specified resource consumer group.

The following example 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.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE -
    (GRANTEE_NAME => 'scott', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', -
     ADMIN_OPTION => FALSE);

You can revoke this privilege using the REVOKE_SYSTEM_PRVILEGE procedure.

Note:

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

The other procedures in the DBMS_RESOURCE_MANAGER_PRIVS package are discussed in "Managing the Switch Privilege".

See Also:

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

DBMS_RESOURCE_MANAGER
DBMS_RESOURCE_MANAGER_PRIVS

Creating a Simple Resource Plan

You can quickly create a simple resource plan that will be adequate for many situations using the CREATE_SIMPLE_PLAN procedure. This procedure enables you to create consumer groups and allocate resources to them by executing a single statement. Using this procedure, you are not required to invoke the procedures that are described in succeeding sections for creating a pending area, creating each consumer group individually, and specifying resource plan directives.

You can specify the following parameters for the CREATE_SIMPLE_PLAN procedure:

Parameter	Description
`SIMPLE_PLAN`	Name of the plan
`CONSUMER_GROUP1`	Consumer group name for first group
`GROUP1_CPU`	CPU resource allocated to this group
`CONSUMER_GROUP2`	Consumer group name for second group
`GROUP2_CPU`	CPU resource allocated to this group
`CONSUMER_GROUP3`	Consumer group name for third group
`GROUP3_CPU`	CPU resource allocated to this group
`CONSUMER_GROUP4`	Consumer group name for fourth group
`GROUP4_CPU`	CPU resource allocated to this group
`CONSUMER_GROUP5`	Consumer group name for fifth group
`GROUP5_CPU`	CPU resource allocated to this group
`CONSUMER_GROUP6`	Consumer group name for sixth group
`GROUP6_CPU`	CPU resource allocated to this group
`CONSUMER_GROUP7`	Consumer group name for seventh group
`GROUP7_CPU`	CPU resource allocated to this group
`CONSUMER_GROUP8`	Consumer group name for eighth group
`GROUP8_CPU`	CPU resource allocated to this group

Up to eight consumer groups can be specified using this procedure and the only plan directive that can be specified is for CPU. The plan uses the EMPHASIS CPU allocation policy and each consumer group uses the ROUND_ROBIN scheduling policy. Each consumer group specified in the plan is allocated its CPU percentage at level 2. Also implicitly included in the plan are SYS_GROUP (a system-defined group that is the initial consumer group for the users SYS and SYSTEM) and OTHER_GROUPS.

Example: Using the CREATE_SIMPLE_PLAN Procedure

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
   CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 80,
   CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20);
END;

Executing the preceding statements creates the following plan:

Consumer Group	Level 1	Level 2	Level 3
`SYS_GROUP`	100%	-	-
`mygroup1`	-	80%	-
`mygroup2`	-	20%	-
`OTHER_GROUPS`	-	-	100%

Creating Complex Resource Plans

This section describes the actions and DBMS_RESOURCE_MANAGER procedures that you can use when your situation requires that you create more complex resource plans. It contains the following sections:

Using the Pending Area for Creating Plan Schemas
Creating Resource Plans
Creating Resource Consumer Groups
Specifying Resource Plan Directives

Using the Pending Area for Creating Plan Schemas

The first thing you must do to create or modify plan schemas is to create a pending area. This is a scratch area allowing you to stage your changes and to validate them before they are made active.

Creating a Pending Area

To create a pending area, you use the following statement:

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

In effect, you are making the pending area active and "loading" all existing, or active, plan schemas into the pending area so that they can be updated or new plans added. Active plan schemas are those schemas already stored in the data dictionary for use by the Database Resource Manager. If you attempt to update a plan or add a new plan without first activating (creating) the pending area, you will receive an error message notifying you that the pending area is not active.

Views are available for inspecting all active resource plan schemas as well as the pending ones. These views are listed in Viewing Database Resource Manager Information.

Validating Changes

At any time when you are making changes in the pending area you can call the validate procedure as shown here.

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

This procedure checks whether changes that have been made are valid. The following rules must be adhered to, and are checked by the validate procedure:

No plan schema can contain any loops.
All plans and resource consumer groups referred to by plan directives must exist.
All plans must have plan directives that point to either plans or resource consumer groups.
All percentages in any given level must not add up to greater than 100.
A plan that is currently being used as a top plan by an active instance cannot be deleted.
The following plan directive parameters can appear only in plan directives that refer to resource consumer groups (not other resource plans):
- PARALLEL_DEGREE_LIMIT_P1
- ACTIVE_SESS_POOL_P1
- QUEUEING_P1
- SWITCH_GROUP
- SWITCH_TIME
- SWITCH_ESTIMATE
- MAX_EST_EXEC_TIME
- UNDO_POOL
- MAX_IDLE_TIME
- MAX_IDLE_BLOCKER_TIME
- SWITCH_TIME_IN_CALL
There can be no more than 32 resource consumer groups in any active plan schema. Also, at most, a plan can have 32 children.
Plans and resource consumer groups cannot have the same name.
There must be a plan directive for OTHER_GROUPS somewhere in any active plan schema. This ensures that a session which is not part of any of the consumer groups included in the currently active plan is allocated resources (as specified by the OTHER_GROUPS directive).

You will receive an error message if any of the preceding rules are violated. You can then make changes to fix any problems and call the validate procedure again.

It is possible to create "orphan" consumer groups that have no plan directives referring to them. This allows the creation of consumer groups that will not currently be used, but may be part of some plan to be implemented in the future.

Submitting Changes

After you have validated your changes, call the submit procedure to make your changes active.

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

The submit procedure also performs validation, so you do not necessarily need to make separate calls to the validate procedure. However, if you are making major changes to plan schemas, debugging problems is often easier if you incrementally validate your changes. No changes are submitted (made active) until validation is successful on all of the changes in the pending area.

The SUBMIT_PENDING_AREA procedure clears (deactivates) the pending area after successfully validating and committing the changes.

Note:

A call to SUBMIT_PENDING_AREA may fail even if VALIDATE_PENDING_AREA succeeds. This can happen if, for example, a plan being deleted is loaded by an instance after a call to VALIDATE_PENDING_AREA, but before a call to SUBMIT_PENDING_AREA.

Clearing the Pending Area

There is also a procedure for clearing the pending area at any time. This statement causes all of your changes to be cleared from the pending area:

EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

You must call the CREATE_PENDING_AREA procedure before you can again attempt to make changes.

Creating Resource Plans

When you create a resource plan, you can specify the parameters shown in the following table. The first parameter is required; the remainder are optional.

Parameter	Description
`PLAN`	Name of the plan.
`COMMENT`	Any descriptive comment.
`CPU_MTH`	Resource allocation method for specifying how much CPU each consumer group or subplan gets. `EMPHASIS`, the default method, is for multilevel plans that use percentages to specify how CPU is distributed among consumer groups. `RATIO` is for single-level plans that use ratios to specify how CPU is distributed.
`ACTIVE_SESS_POOL_MTH`	Active session pool resource allocation method. Limits the number of active sessions. All other sessions are inactive and wait in a queue to be activated. `ACTIVE_SESS_POOL_ABSOLUTE` is the default and only method available.
`PARALLEL_DEGREE_LIMIT_MTH`	Resource allocation method for specifying a limit on the degree of parallelism of any operation. `PARALLEL_DEGREE_LIMIT_ABSOLUTE` is the default and only method available.
`QUEUEING_MTH`	Queuing resource allocation method. Controls order in which queued inactive sessions will execute. `FIFO_TIMEOUT` is the default and only method available.

Oracle Database provides one resource plan, SYSTEM_PLAN, that contains a simple structure that may be adequate for some environments. It is illustrated later in "An Oracle-Supplied Plan".

Creating a Plan

You create a plan using the CREATE_PLAN procedure. The following creates a plan called great_bread. You choose to use the default resource allocation methods.

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'great_bread', -
    COMMENT => 'great plan');

Updating a Plan

Use the UPDATE_PLAN procedure to update plan information. If you do not specify the arguments for the UPDATE_PLAN procedure, they remain unchanged in the data dictionary. The following statement updates the COMMENT parameter.

EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'great_bread', -
     NEW_COMMENT => 'great plan for great bread');

Deleting a Plan

The DELETE_PLAN procedure deletes the specified plan as well as all the plan directives associated with it. The following statement deletes the great_bread plan and its directives.

EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'great_bread');

The resource consumer groups themselves are not deleted, but they are no longer associated with the great_bread plan.

The DELETE_PLAN_CASCADE procedure deletes the specified plan as well as all its descendants (plan directives, subplans, resource consumer groups). If DELETE_PLAN_CASCADE encounters an error, it will roll back, leaving the plan schema unchanged.

Using the Ratio Policy

the RATIO policy is a single-level CPU allocation method. Instead of percentages, you specify numbers corresponding to the ratio of CPU you want to give to the consumer group. For example, given three consumer groups GOLD_CG, SILVER_CG, and BRONZE_CG, assume that we specify the following plan directives:

DBMS_RESOURCE_MANAGER.CREATE_PLAN
   (PLAN => 'service_level_plan',
    CPU_MTH -> 'RATIO', 
    COMMENT => 'service level plan');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
   (PLAN => 'service_level_plan',
    GROUP_OR_SUBPLAN => 'GOLD_CG', 
    COMMENT => 'Gold service level customers',
    CPU_P1 => 10);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
   (PLAN => 'service_level_plan',
    GROUP_OR_SUBPLAN => 'SILVER_CG', 
    COMMENT => 'Silver service level customers',  
    CPU_P1 => 5);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
   (PLAN => 'service_level_plan',
    GROUP_OR_SUBPLAN => 'BRONZE_CG', 
    COMMENT => 'Bonze service level customers',
    CPU_P1 => 2);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
    (PLAN => 'service_level_plan', 
    GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
    COMMENT => 'Lowest priority sessions',
    CPU_P1 => 1);

The ratio of CPU allocation would be 10:5:2:1 for the GOLD_CG, SILVER_CG, BRONZE_CG, and OTHER_GROUPS consumer groups, respectively.

If sessions exists only in the GOLD_CG and SILVER_CG consumer groups, then the ratio of CPU allocation would be 10:5 between the two groups.

Creating Resource Consumer Groups

When you create a resource consumer group, you can specify the following parameters:

Parameter	Description
`CONSUMER_GROUP`	Name of the consumer group.
`COMMENT`	Any comment.
`CPU_MTH`	The resource allocation method for distributing CPU among sessions in the consumer group. The default is `ROUND_ROBIN`, which uses a round-robin scheduler to ensure that sessions are fairly executed. `RUN_TO_COMPLETION` specifies that sessions with the largest active time are scheduled ahead of other sessions.

There are two special consumer groups that are always present in the data dictionary, and they cannot be modified or deleted. These are:

DEFAULT_CONSUMER_GROUP

This is the initial consumer group for all users/sessions that have not been explicitly assigned an initial consumer group. DEFAULT_CONSUMER_GROUP has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this consumer group (see "Managing the Switch Privilege").
OTHER_GROUPS

This consumer group cannot be explicitly assigned to a user. OTHER_GROUPS must have a resource directive specified in the schema of any active plan. This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema, including DEFAULT_CONSUMER_GROUP.

Additionally, two other groups, SYS_GROUP and LOW_GROUP, are provided as part of the Oracle-supplied SYSTEM_PLAN that is described in "An Oracle-Supplied Plan".

Creating a Consumer Group

You create a consumer group using the CREATE_CONSUMER_GROUP procedure. The following creates a consumer group called sales. Remember, the pending area must be active to execute this statement successfully.

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'sales', -
    COMMENT => 'retail and wholesale sales');

Updating a Consumer Group

Use the UPDATE_CONSUMER_GROUP procedure to update consumer group information. If you do not specify the arguments for the UPDATE_CONSUMER_GROUP procedure, they remain unchanged in the data dictionary.

Deleting a Consumer Group

The DELETE_CONSUMER_GROUP procedure deletes the specified consumer group. Upon deletion of a consumer group, all users having the deleted group as their initial consumer group will have the DEFAULT_CONSUMER_GROUP set as their initial consumer group. All currently running sessions belonging to a deleted consumer group will be switched to DEFAULT_CONSUMER_GROUP.

Specifying Resource Plan Directives

Resource plan directives assign consumer groups to resource plans and provide the parameters for each resource allocation method. When you create a resource plan directive, you can specify the following parameters

Parameter	Description
`PLAN`	Name of the resource plan.
`GROUP_OR_SUBPLAN`	Name of the consumer group or subplan.
`COMMENT`	Any comment.
`CPU_P1`	For `EMPHASIS`, specifies the CPU percentage at the first level. For `RATIO`, specifies the weight of CPU usage. Default is `NULL` for all `CPU` parameters.
`CPU_P2`	For `EMPHASIS`, specifies CPU percentage at the second level. Not applicable for `RATIO`.
`CPU_P3`	For `EMPHASIS`, specifies CPU percentage at the third level. Not applicable for `RATIO`.
`CPU_P4`	For `EMPHASIS`, specifies CPU percentage at the fourth level. Not applicable for `RATIO`.
`CPU_P5`	For `EMPHASIS`, specifies CPU percentage at the fifth level. Not applicable for `RATIO`.
`CPU_P6`	For `EMPHASIS`, specifies CPU percentage at the sixth level. Not applicable for `RATIO`.
`CPU_P7`	For `EMPHASIS`, specifies CPU percentage at the seventh level. Not applicable for `RATIO`.
`CPU_P8`	For `EMPHASIS`, specifies CPU percentage at the eighth level. Not applicable for `RATIO`.
`ACTIVE_SESS_POOL_P1`	Specifies maximum number of concurrently active sessions for a consumer group. Default is `UNLIMITED`.
`QUEUEING_P1`	Specified time (in seconds) after which a job in an inactive session queue (waiting for execution) will time out. Default is `UNLIMITED`.
`PARALLEL_DEGREE_LIMIT_P1`	Specifies a limit on the degree of parallelism for any operation. Default is `UNLIMITED`.
`SWITCH_GROUP`	Specifies consumer group to which this session is switched if other switch criteria are met. If the group name is '`CANCEL_SQL`', then the current call will be canceled when other switch criteria are met. If the group name is '`KILL_SESSION`', then the session will be killed when other switch criteria are met. Default is `NULL`.
`SWITCH_TIME`	Specifies time (in seconds) that a session can execute before an action is taken. Default in `UNLIMITED`. You cannot specify both `SWITCH_TIME` and `SWITCH_TIME_IN_CALL`.
`SWITCH_ESTIMATE`	If `TRUE`, tells the database to use its execution time estimate to automatically switch the consumer group of an operation prior to beginning its execution. Default is `FALSE`.
`MAX_EST_EXEC_TIME`	Specifies the maximum execution time (in seconds) allowed for a session. If the optimizer estimates that an operation will take longer than `MAX_EST_EXEC_TIME`, the operation is not started and `ORA-07455` is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is `UNLIMITED`.
`UNDO_POOL`	Sets a maximum in kilobytes (K) on the total amount of undo generated by a consumer group. Default is `UNLIMITED`.
`MAX_IDLE_TIME`	Indicates the maximum session idle time. Default is `NULL`, which implies unlimited.
`MAX_IDLE_BLOCKER_TIME`	Indicates the maximum session idle time of a blocking session. Default is `NULL`, which implies unlimited.
`SWITCH_TIME_IN_CALL`	Specifies the time (in seconds) that a session can execute before an action is taken. At the end of the call, the consumer group of the session is restored to its original consumer group. Default is `UNLIMITED`. You cannot specify both `SWITCH_TIME_IN_CALL` and `SWITCH_TIME`.

Creating a Resource Plan Directive

You use the CREATE_PLAN_DIRECTIVE to create a resource plan directive. The following statement creates a resource plan directive for plan great_bread.

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', -
     GROUP_OR_SUBPLAN => 'sales', COMMENT => 'sales group', -
     CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 => 4);

To complete the plan, similar to that shown in Figure 24-1, execute the following statements:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', 
     GROUP_OR_SUBPLAN => 'market', COMMENT => 'marketing group', 
     CPU_P1 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', 
     GROUP_OR_SUBPLAN => 'develop', COMMENT => 'development group',
     CPU_P1 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', 
     GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'this one is required',
     CPU_P1 => 0, CPU_P2 => 100);
END;

In this plan, consumer group sales has a maximum degree of parallelism of 4 for any operation, while none of the other consumer groups are limited in their degree of parallelism. Also, whenever there are leftover level 1 CPU resources, they are allocated (100%) to OTHER_GROUPS.

Updating Resource Plan Directives

Use the UPDATE_PLAN_DIRECTIVE procedure to update plan directives. This example changes CPU allocation for resource consumer group develop.

EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'great_bread', -
     GROUP_OR_SUBPLAN => 'develop', NEW_CPU_P1 => 15);

If you do not specify the arguments for the UPDATE_PLAN_DIRECTIVE procedure, they remain unchanged in the data dictionary.

Deleting Resource Plan Directives

To delete a resource plan directive, use the DELETE_PLAN_DIRECTIVE procedure

How Resource Plan Directives Interact

If there are multiple resource plan directives that refer to the same consumer group, then the following rules apply for specific cases:

The parallel degree limit for the consumer group will be the minimum of all the incoming values.
The active session pool for the consumer group will be the sum of all the incoming values and the queue timeout will be the minimum of all incoming timeout values.
If there is more than one switch group and more than one switch time, the Database Resource Manager will choose the most restrictive of all incoming values. Specifically:
- SWITCH_TIME = min (all incoming over_switch_time values)
- SWITCH_ESTIMATE = TRUE overrides SWITCH_ESTIMATE = FALSE
  Notes:
  - If both plan directives specify the same switch time, but different switch groups, then the choice as to which group to switch to is statically but arbitrarily decided by the Database Resource Manager.
  - You cannot specify both SWITCH_TIME and SWITCH_TIME_IN_CALL plan directives for a single consumer group.
If a session is switched to another consumer group because it exceeds its switch time, that session will execute even if the active session pool for the new consumer group is full.
The maximum estimated execution time will be the most restrictive of all incoming values. Specifically:

MAX_EST_EXEC_TIME = min (all incoming MAX_EST_EXEC_TIME values)

Managing Resource Consumer Groups

Before you enable the Database Resource Manager, you must assign resource consumer groups to users. This can be done manually, or you can provide mappings that enable the database to automatically assign user sessions to consumer groups depending upon session attributes.

In addition to providing procedures to create, update, or delete the elements used by the Database Resource Manager, the DBMS_RESOURCE_MANAGER package contains procedures that effect the assigning of resource consumer groups to users. It also provides procedures that allow you to temporarily switch a user session to another consumer group.

The DBMS_RESOURCE_MANAGER_PRIVS package, described earlier for granting the Database Resource Manager system privilege, can also be used to grant the switch privilege to another user, who can then alter their own consumer group.

Of the procedures discussed in this section, you use a pending area only for the SET_CONSUMER_GROUP_MAPPING and SET_CONSUMER_GROUP_MAPPING_PRI procedures. These procedures are used for the automatic assigning of sessions to consumer groups.

This section contains the following topics:

Assigning an Initial Resource Consumer Group
Changing Resource Consumer Groups
Managing the Switch Privilege
Automatically Assigning Resource Consumer Groups to Sessions

Assigning an Initial Resource Consumer Group

The initial consumer group of a session is determined by mapping the attributes of the session to a consumer group. For more information on how to configure the mapping, see the section "Automatically Assigning Resource Consumer Groups to Sessions".

Changing Resource Consumer Groups

There are two procedures, which are part of the DBMS_RESOURCE_MANAGER package, that allow administrators to change the resource consumer group of running sessions. Both of these procedures can also change the consumer group of any parallel execution server sessions associated with the coordinator session. The changes made by these procedures pertain to current sessions only; they are not persistent. They also do not change the initial consumer groups for users.

Instead of killing a session of a user who is using excessive CPU, an administrator can instead change that user's consumer group to one that is allowed less CPU. Or, this switching can be enforced automatically, using automatic consumer group switching resource plan directives.

Switching a Session

The SWITCH_CONSUMMER_GROUP_FOR_SESS causes the specified session to immediately be moved into the specified resource consumer group. In effect, this statement can raise or lower priority. The following statement changes the resource consumer group of a specific session to a new consumer group. The session identifier (SID) is 17, the session serial number (SERIAL#) is 12345, and the session is to be changed to the high_priority consumer group.

EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345', -
   'high_priorty');

The SID, session serial number, and current resource consumer group for a session are viewable using the V$SESSION data dictionary view.

Switching Sessions for a User

The SWITCH_CONSUMER_GROUP_FOR_USER procedure changes the resource consumer group for all sessions with a given user name.

EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('scott', -
    'low_group');

Using the DBMS_SESSION Package to Switch Consumer Group

If granted the switch privilege, users can switch their current consumer group using the SWITCH_CURRENT_CONSUMER_GROUP procedure in the DBMS_SESSION package.

This procedure enables users to switch to a consumer group for which they have the switch privilege. If the caller is another procedure, then this procedure enables users to switch to a consumer group for which the owner of that procedure has switch privileges.

The parameters for this procedure are:

Parameter Description

NEW_CONSUMER_GROUP The consumer group to which the user is switching.

OLD_CONSUMER_GROUP An output parameter. Stores the name of the consumer group from which the user switched. Can be used to switch back later.

Parameter	Description
`NEW_CONSUMER_GROUP`	The consumer group to which the user is switching.
`OLD_CONSUMER_GROUP`	An output parameter. Stores the name of the consumer group from which the user switched. Can be used to switch back later.
`INITIAL_GROUP_ON_ERROR`	Controls behavior if a switching error occurs. If `TRUE`, in the event of an error, the user is switched to the initial consumer group. If `FALSE`, raise an error.

INITIAL_GROUP_ON_ERROR

Controls behavior if a switching error occurs.

If TRUE, in the event of an error, the user is switched to the initial consumer group.

If FALSE, raise an error.

The following example illustrates switching to a new consumer group. By printing the value of the output parameter old_group, we illustrate how the old consumer group name has been saved.

SET serveroutput on
DECLARE
    old_group varchar2(30);
BEGIN
DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('sales', old_group, FALSE);
DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group);
END;

The following line is output:

OLD GROUP = DEFAULT_CONSUMER_GROUP

The DBMS_SESSION package can be used from within a PL/SQL application, thus allowing the application to change consumer groups, or effectively priority, dynamically.

Note that the Database Resource Manager considers a switch to have taken place even if the SWITCH_CURRENT_CONSUMER_GROUP procedure is called to switch the session to the consumer group that it is already in.

Note:

The Database Resource Manager also works in environments where a generic database user name is used to log on to an application. The DBMS_SESSION package can be called to switch the consumer group assignment of a session at session startup, or as particular modules are called.

See Also:

Oracle Database PL/SQL Packages and Types Reference for additional examples and more information about the DBMS_SESSION package

Managing the Switch Privilege

Using the DBMS_RESOURCE_MANAGER_PRIVS package, you can grant or revoke the switch privilege to a user, role, or PUBLIC. The switch privilege gives users the privilege to switch their current resource consumer group to a specified resource consumer group. The package also enables you to revoke the switch privilege.

The actual switching is done by executing a procedure in the DBMS_SESSION package. A user who has been granted the switch privilege (or a procedure owned by that user) can use the SWITCH_CURRENT_CONSUMER_GROUP procedure to switch to another resource consumer group. The new group must be one to which the user has been specifically authorized to switch.

Granting the Switch Privilege

The following example grants the privilege to switch to a consumer group. User scott is granted the privilege to switch to consumer group bug_batch_group.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('scott', - 
     'bug_batch_group', TRUE);

User scott is also granted permission to grant switch privileges for bug_batch_group to others.

If you grant a user permission to switch to a particular consumer group, then that user can switch their current consumer group to the new consumer group.

If you grant a role permission to switch to a particular resource consumer group, then any users who have been granted that role and have enabled that role can immediately switch their current consumer group to the new consumer group.

If you grant PUBLIC the permission to switch to a particular consumer group, then any user can switch to that group.

If the GRANT_OPTION argument is TRUE, then users granted switch privilege for the consumer group can also grant switch privileges for that consumer group to others.

Revoking Switch Privileges

The following example revokes user scott's privilege to switch to consumer group bug_batch_group.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP ('scott', - 
     'bug_batch_group');

If you revoke a user's switch privileges to a particular consumer group, then any subsequent attempts by that user to switch to that consumer group will fail. If you revoke the initial consumer group from a user, then that user will automatically be part of the DEFAULT_CONSUMER_GROUP when logging in.

If you revoke from a role the switch privileges to a consumer group, then any users who only had switch privilege for the consumer group through that role will not be able to subsequently switch to that consumer group.

If you revoke switch privileges to a consumer group from PUBLIC, then any users other than those who are explicitly assigned switch privileges either directly or through PUBLIC, will not be able to subsequently switch to that consumer group.

Automatically Assigning Resource Consumer Groups to Sessions

You can configure the Database Resource Manager to automatically assign consumer groups to sessions by providing mappings between session attributes and consumer groups. Further, you can prioritize the mappings so as to indicate which mapping has precedence in case of conflicts.

There are two types of session attributes: login attributes and runtime attributes. The login attributes are meaningful only at session login time, when the Database Resource Manager determines the initial consumer group of the session. In contrast, a session that has already logged in can later be reassigned to another consumer group based on its run-time attributes.

You use the SET_CONSUMER_GROUP_MAPPING and SET_CONSUMER_GROUP_MAPPING_PRI procedures to configure the automatic assigning of sessions to consumer groups. You must use a pending area for these procedures.

Creating Consumer Group Mappings

The mapping for a session consists of a set of attribute/consumer group pairs that determine how a session is matched to a consumer group. You use the SET_CONSUMER_GROUP_MAPPING procedure to map a single session attribute to a consumer group. The parameters for this procedure are:

Parameter	Description
`ATTRIBUTE`	The login or runtime session attribute type
`VALUE`	The value of the attribute being mapped
`CONSUMER_GROUP`	The consumer group to map to.

The attribute can be one of the following:

Attribute	Type	Description
`ORACLE_USER`	Login	The Oracle Database user name
`SERVICE_NAME`	Login	The service name used by the client to establish a connection
`CLIENT_OS_USER`	Login	The operating system user name of the client that is logging in
`CLIENT_PROGRAM`	Login	The name of the client program used to log into the server
`CLIENT_MACHINE`	Login	The name of the machine from which the client is making the connection
`MODULE_NAME`	Runtime	The module name in the application currently executing as set by the `DBMS_APPLICATION_INFO.SET_MODULE` procedure or the equivalent OCI attribute setting
`MODULE_NAME_ACTION`	Runtime	A combination of the current module and the action being performed as set by either of the following procedures or their equivalent OCI attribute setting: `DBMS_APPLICATION_INFO.SET_MODULE` `DBMS_APPLICATION_INFO.SET_ACTION` The attribute is specified as the module name followed by a period (.), followed by the action name (`module_name.action_name`).
`SERVICE_MODULE`	Runtime	A combination of service and module names in this form: `service_name.module_name`
`SERVICE_MODULE_ACTION`	Runtime	A combination of service name, module name, and action name, in this form: `service_name.module_name.action_name`

For example, the following statement causes user scott to map to the dev_group consumer group every time he logs in:

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING  
     (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'scott', 'dev_group');
END;

Creating Attribute Mapping Priorities

To resolve conflicting mappings, you can establish a priority ordering of the attributes from most important to least important. You use the SET_CONSUMER_GROUP_MAPPING_PRI procedure to set the priority of each attribute to a unique integer from 1 (most important) to 10 (least important). The following example illustrates this setting of priorities:

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
    EXPLICIT => 1,    SERVICE_MODULE_ACTION => 2,    SERVICE_MODULE => 3,    MODULE_NAME_ACTION => 4,    MODULE_NAME => 5,    SERVICE_NAME => 6,    ORACLE_USER => 7,    CLIENT_PROGRAM => 8,    CLIENT_OS_USER => 9,    CLIENT_MACHINE => 10);
END;

In this example, the priority of the database user name is set to 7 (less important), while the priority of the module name is set to 5 (more important).

Note:

SET_CONSUMER_GROUP_MAPPING_PRI requires that you include the pseudo-attribute EXPLICIT as an argument. It must be set to 1. It indicates that explicit consumer group switches have the highest priority. You explicitly switch consumer groups with these package procedures, which are described in detail in Oracle Database PL/SQL Packages and Types Reference:

DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER

To illustrate how mapping priorities work, assume that in addition to the mapping of user scott to the dev_group consumer group, there is also a module name mapping as follows:

EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING - 
     (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'backup', 'low_priority');

Now if the session sets its module name to backup, the session would be reassigned to the low_priority consumer group, because module name mapping has a higher priority than database user mapping.

To prevent unauthorized clients from setting their session attributes so that they map to higher priority consumer groups, user switch privileges for consumer groups are enforced. This means that even though the attribute of a given session matches a mapping pair, the mapping is only considered valid if the session has the switching privilege for that particular consumer group. Sessions are automatically switched only to consumer groups for which they have been granted switch privileges.

Automatic Group Switching

Each session can be switched automatically to another consumer group via the mappings at distinct points in time:

When the session first logs in, the mapping is evaluated to determine the initial group of the session.
If the current mapping attribute of a session is A, then if the attribute A is set to a new value (only possible for runtime attributes) then the mapping is reevaluated and the session is switched to the appropriate consumer group.
If a runtime session attribute is modified such that the current mapping becomes a different attribute B, then the session is switched.
Whenever the client ID is set to a different value, the mapping is reevaluated and the session is switched.

Two things to note about the preceding rules are:

If a runtime attribute for which a mapping is provided is set to the same value it already has, or if the client ID is set to the same value it already has, then no switching takes place.
A session can be switched to the same consumer group it is already in. The effect of switching in this case is to zero out session statistics that are typically zeroed out during a switch to a different group (for example, the ACTIVE_TIME_IN_GROUP value of the session).

Enabling the Database Resource Manager

You enable the Database Resource Manager by setting the RESOURCE_MANAGER_PLAN initialization parameter. This parameter specifies the top plan , identifying the plan schema to be used for this instance. If no plan is specified with this parameter, the Database Resource Manager is not activated.

Note:

The Resource Manager automatically activates if a Scheduler window that specifies a resource plan opens.

The following example activates the Database Resource Manager and assigns the top plan as mydb_plan.

RESOURCE_MANAGER_PLAN = mydb_plan

You can also activate or deactivate the Database Resource Manager, or change the current top plan, using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure or the ALTER SYSTEM statement. In this example, the top plan is specified as mydb_plan:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';

An error message is returned if the specified plan does not exist in the data dictionary.

To deactivate the Database Resource Manager, issue the following statement:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

The Scheduler can automatically change the Resource Manager plan at Scheduler window boundaries. In some cases, this may be unacceptable. For example, if you have an important task to finish, and if you set the Resource Manager plan to give your task priority, then you expect that the plan will remain the same until you change it. However, because a Scheduler window could become activated after you have set your plan, the Resource Manager plan may change while your task is running.

To prevent this situation, you can set the RESOURCE_MANAGER_PLAN parameter to the name of the plan you want for the system and prepend the name with "FORCE:". Using the prefix FORCE indicates that the current Resource Manager plan can be changed only when the database administrator changes the value of the RESOURCE_MANAGER_PLAN parameter. This restriction can be lifted by reexecuting the command without prepending the plan name with "FORCE:".

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';

The DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure has a similar capability.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information on DBMS_RESOURCE_MANAGER.SWITCH_PLAN.

Putting It All Together: Database Resource Manager Examples

This section provides some examples of resource plan schemas. The following examples are presented:

Multilevel Schema Example
Example of Using Several Resource Allocation Methods
An Oracle-Supplied Plan

Multilevel Schema Example

The following statements create a multilevel schema as illustrated in Figure 24-3. They use default plan and resource consumer group methods.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan', 
   COMMENT => 'Resource plan/method for bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan', 
   COMMENT => 'Resource plan/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan', 
   COMMENT => 'Resource plan/method for bug and mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Online_group', 
   COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Batch_group', 
   COMMENT => 'Resource consumer group/method for batch job bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maint_group',
   COMMENT => 'Resource consumer group/method for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Users_group', 
   COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Postman_group',
   COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maint_group', 
   COMMENT => 'Resource consumer group/method for users sessions for mail db maint');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
   GROUP_OR_SUBPLAN => 'Online_group',
   COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
   PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', 
   GROUP_OR_SUBPLAN => 'Batch_group', 
   COMMENT => 'batch bug users sessions at level 1', CPU_P1 => 20, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', 
   GROUP_OR_SUBPLAN => 'Bug_Maint_group',
   COMMENT => 'bug maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 100,
   PARALLEL_DEGREE_LIMIT_P1 => 3);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', 
   GROUP_OR_SUBPLAN => 'OTHER_GROUPS', 
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
   CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', 
   GROUP_OR_SUBPLAN => 'Postman_group',
   COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'Users_group',
   COMMENT => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'Mail_Maint_group',
   COMMENT => 'mail maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 20,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'OTHER_GROUPS', 
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
   CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', 
   GROUP_OR_SUBPLAN => 'maildb_plan', 
   COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', 
   GROUP_OR_SUBPLAN => 'bugdb_plan', 
   COMMENT => 'all bug users sessions at level 1', CPU_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

The preceding call to VALIDATE_PENDING_AREA is optional because the validation is implicitly performed in SUBMIT_PENDING_AREA.

Figure 24-3 Multilevel Schema

Description of "Figure 24-3 Multilevel Schema"

Note that under maildb_plan, because only 40% CPU is allocated to Postman_group at level 1, there is an implied 60% remaining at level 1. This 60% is then shared by Users_group and Mail_Maint_group at level 2.

Example of Using Several Resource Allocation Methods

The example presented here could represent a plan for a database supporting a packaged ERP (Enterprise Resource Planning) or CRM (Customer Relationship Management). The work in such an environment can be highly varied. There may be a mix of short transactions and quick queries, in combination with longer running batch jobs that include large parallel queries. The goal is to give good response time to OLTP (Online Transaction Processing), while allowing batch jobs to run in parallel.

The plan is summarized in the following table.

Group CPU Resource Allocation % Active Session Pool Parameters Automatic Switching Parameters Maximum Estimated Execution Time Undo Pool

Group	CPU Resource Allocation %	Active Session Pool Parameters	Automatic Switching Parameters	Maximum Estimated Execution Time	Undo Pool
`oltp`	Level 1: 80%		Switch to group: `batch` Switch time: 3 Use estimate: `TRUE`	--	Size: 200K
`batch`	Level 2: 100%	Pool size: 5 Timeout: 600	--	Time: 3600	--
`OTHER_GROUPS`	Level 3: 100%	--	--	--	--

oltp

Level 1: 80%

Switch to group: batch

Switch time: 3

Use estimate: TRUE

Size: 200K

batch

Level 2: 100%

Pool size: 5

Timeout: 600

Time: 3600

OTHER_GROUPS Level 3: 100% -- -- -- --

The following statements create the preceding plan, which is named erp_plan:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'erp_plan', 
  COMMENT => 'Resource plan/method for ERP Database');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp', 
  COMMENT => 'Resource consumer group/method for OLTP jobs');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch', 
  COMMENT => 'Resource consumer group/method for BATCH jobs');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', 
  GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80, 
  SWITCH_GROUP => 'batch', SWITCH_TIME => 3,SWITCH_ESTIMATE => TRUE, 
  UNDO_POOL => 200);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', 
  GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 100, 
  ACTIVE_SESS_POOL_P1 => 5, QUEUEING_P1 => 600, 
  MAX_EST_EXEC_TIME => 3600);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', 
  GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

An Oracle-Supplied Plan

Oracle Database provides one default resource manager plan, SYSTEM_PLAN, which gives priority to system sessions. SYSTEM_PLAN is defined as follows:

Resource Consumer Group	CPU Resource Allocation
Resource Consumer Group	Level 1	Level 2	Level 3
`SYS_GROUP`	100%	0%	0%
`OTHER_GROUPS`	0%	100%	0%
`LOW_GROUP`	0%	0%	100%

The database-provided groups in this plan are:

SYS_GROUP is the initial consumer group for the users SYS and SYSTEM.
OTHER_GROUPS applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema.
LOW_GROUP provides a group having lower priority than SYS_GROUP and OTHER_GROUPS in this plan. It is up to you to decide which user sessions will be part of LOW_GROUP. Switch privilege is granted to PUBLIC for this group.

These groups can be used, or not used, and can be modified or deleted.

You can use this simple database-supplied plan if it is appropriate for your environment.

Monitoring and Tuning the Database Resource Manager

To effectively monitor and tune the Database Resource Manager, you must design a representative environment. The Database Resource Manager works best in large production environments in which system utilization is high. If a test places insufficient load on the system, measured CPU allocations can be very different from the allocations specified in the active resource plan. This is because the Database Resource Manager does not attempt to enforce CPU allocation percentage limits as long as consumer groups are getting the resources they need.

Creating the Environment

To create a representative environment, there must be sufficient load (demand for CPU resources) to make CPU resources scarce. If the following rules are followed, the test environment should generate actual (measured) resource allocations that match those specified in the active resource plan.

Create the minimum number of concurrently running processes required to generate sufficient load. This is the larger of:
- Four processes for each consumer group
- 1.5 * (number of processors) for each consumer group. If the result is not an integer, round up.

Each and every process must be capable of consuming all of the CPU resources allocated to the consumer group in which it runs. Write resource intensive programs that continue to spin no matter what happens. This can be as simple as:

BEGIN
DECLARE
    m NUMBER;
  BEGIN
    FOR i IN 1..100000 LOOP
      FOR j IN 1..100000 LOOP
        /*
         * The following query does a cartesian product without
         * a predicate and takes up significant CPU time.
         */
         select count(*) into m from v$sysstat, v$system_event;
      END LOOP;
    END LOOP;
  END;
END;
/

Why Is This Necessary to Produce Expected Results?

When every group can secure as much CPU resources as it demands, the Database Resource Manager first seeks to maximize system throughput, not to enforce allocation percentages. For example, consider the following conditions:

There is only one process available to run for each consumer group.
Each process runs continuously.
There are four CPUs.

In this case, the measured CPU allocation to each consumer group will be 25%, no matter what the allocations specified in the active resource plan.

Another factor determines the calculation in (1) in the preceding section. Processor affinity scheduling at the operating system level can distort CPU allocation on underutilized systems. This is explained in the following paragraphs.

Until the number of concurrently running processes reaches a certain level, typical operating system scheduling algorithms will prevent full utilization. The Database Resource Manager controls CPU usage by restricting the number of running processes. By deciding which processes are allowed to run and for what duration, the Database Resource Manager controls CPU resource allocation. When a CPU has resources available, and other processors are fully utilized, the operating system migrates processes to the underutilized processor, but not immediately.

With processor affinity, the operating system waits (for a time) to migrate processes, "hoping" that another process will be dispatched to run instead of forcing process migration from one CPU to another. On a fully loaded system with enough processes waiting, this strategy will work. In large production environments, processor affinity increases performance significantly, because invalidating the current CPU cache and then loading the new one is quite expensive. Since processes have processor affinity on most platforms, more processes than CPUs for each consumer group must be run. Otherwise, full system utilization is not possible.

Monitoring Results

Use these views to help you monitor the results of your Resource Manager settings.

V$RSRC_CONSUMER_GROUP
V$RSRC_SESSION_INFO
V$RSRC_PLAN_HISTORY

V$RSRC_CONSUMER_GROUP Use the V$RSRC_CONSUMER_GROUP view to monitor CPU usage. It provides the cumulative amount of CPU time consumed by all sessions in each consumer group. It also provides a number of other measures helpful for tuning.

SQL> SELECT NAME, CONSUMED_CPU_TIME FROM V$RSRC_CONSUMER_GROUP;

NAME                             CONSUMED_CPU_TIME
-------------------------------- -----------------
OTHER_GROUPS                                 14301
TEST_GROUP                                    8802
TEST_GROUP2                                      0

3 rows selected.

V$RSRC_SESSION_INFO Use this view to monitor the status of a particular session. The view shows how the session has been affected by the Resource Manager. It provides information such as:

The consumer group that the session currently belongs to
The consumer group that the session originally belonged to
The session attribute that was used to map the session to the consumer group
Session state (RUNNING, WAIT_FOR_CPU, QUEUED, and so on)
Current and cumulative statistics for metrics, such as CPU consumed, wait times, and queued time

SQL> SELECT se.sid sess_id, co.name consumer_group, 
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id

SESS_ID CONSUMER_GROUP  STATE      CPU_TIME CPU_WAIT_TIME QUEUED_TIME
------- --------------- -------- ---------- ------------- -----------
    145 SYS_GROUP       RUNNING      572217             0           0
    158 OTHER_GROUPS    WAITING         220             0           0
    142 OTHER_GROUPS    WAITING      146453             0           0
    141 OTHER_GROUPS    WAITING      148228             0           0

V$RSRC_PLAN_HISTORY This view shows when resource manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time. For each entry in the view, the V$RSRC_CONS_GROUP_HISTORY view has a corresponding entry for each consumer group in the plan that shows the cumulative statistics for the consumer group.

See Also:

Oracle Database Reference for information on these and other Resource Manager views.

Interaction with Operating-System Resource Control

The Oracle Database server expects a static configuration and allocates internal resources, such as latches, from available resources detected at database startup. The database might not perform optimally and can become unstable if resource configuration changes very frequently.

Guidelines for Using Operating-System Resource Control

If you do choose to use Operating-system resource control with Oracle Database, then it should be used judiciously, according to the following guidelines:

Operating-system resource control should not be used concurrently with the Database Resource Manager, because neither of them are aware of each other's existence. As a result, both the operating system and Database Resource Manager try to control resource allocation in a manner that causes unpredictable behavior and instability of Oracle Database.
- If you want to control resource distribution within an instance, use the Database Resource Manager and turn off operating-system resource control.
- If you have multiple instances on a node and you want to distribute resources among them, use operating-system resource control, not the Database Resource Manager.
  
  Note:
  Oracle Database currently does not support the use of both tools simultaneously. Future releases might allow for their interaction on a limited scale.
In an Oracle Database environment, the use of an operating-system resource manager, such as Hewlett Packard's Process Resource Manager (PRM) or Sun's Solaris Resource Manager (SRM), is supported only if all of the following conditions are met:
- Each instance must be assigned to a dedicated operating-system resource manager group or managed entity.
- The dedicated entity running all the instance's processes must run at one priority (or resource consumption) level.
- Process priority management must not be enabled.
  
  Caution:
  Management of individual database processes at different priority levels (for example, using the nice command on UNIX platforms) is not supported. Severe consequences, including instance crashes, can result. You can expect similar undesirable results if operating-system resource control is permitted to manage memory on which an Oracle Database instance is pinned.
If you chose to use operating-system resource control, make sure you turn off the Database Resource Manager. By default, the Database Resource Manager is turned off. If it is not, you can turn it off by issuing the following statement:
```
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='';
```
Also remember to reset this parameter in your initialization parameter file, so that the Database Resource Manager is not activated the next time the database is started up.

Dynamic Reconfiguration

Tools such as Sun's processor sets and dynamic system domains work well with an Oracle Database. There is no need to restart an instance if the number of CPUs changes.

The database dynamically detects any change in the number of available CPUs and reallocates internal resources. On most platforms, the database automatically adjusts the value of the CPU_COUNT initialization parameter to the number of available CPUs.

Viewing Database Resource Manager Information

The following table lists views that are associated with Database Resource Manager:

View	Description
`DBA_RSRC_CONSUMER_GROUP_PRIVS` `USER_RSRC_CONSUMER_GROUP_PRIVS`	`DBA` view lists all resource consumer groups and the users and roles to which they have been granted. `USER` view lists all resource consumer groups granted to the user.
`DBA_RSRC_CONSUMER_GROUPS`	Lists all resource consumer groups that exist in the database.
`DBA_RSRC_MANAGER_SYSTEM_PRIVS` `USER_RSRC_MANAGER_SYSTEM_PRIVS`	`DBA` view lists all users and roles that have been granted Database Resource Manager system privileges. `USER` view lists all the users that are granted system privileges for the `DBMS_RESOURCE_MANAGER` package.
`DBA_RSRC_PLAN_DIRECTIVES`	Lists all resource plan directives that exist in the database.
`DBA_RSRC_PLANS`	Lists all resource plans that exist in the database.
`DBA_RSRC_GROUP_MAPPINGS`	Lists all of the various mapping pairs for all of the session attributes
`DBA_RSRC_MAPPING_PRIORITY`	Lists the current mapping priority of each attribute
`DBA_USERS` `USERS_USERS`	`DBA` view contains information about all users of the database. Specifically, for the Database Resource Manager, it contains the initial resource consumer group for the user. `USER` view contains information about the current user, and specifically, for the Database Resource Manager, it contains the current user's initial resource consumer group.
`V$ACTIVE_SESS_POOL_MTH`	Displays all available active session pool resource allocation methods.
`V$BLOCKING_QUIESCE`	Lists all sessions that could potentially block a quiesce operation. Includes sessions that are active and not in the `SYS_GROUP` consumer group.
`V$PARALLEL_DEGREE_LIMIT_MTH`	Displays all available parallel degree limit resource allocation methods.
`V$QUEUEING_MTH`	Displays all available queuing resource allocation methods.
`V$RSRC_CONS_GROUP_HISTORY`	For each entry in the view `V$RSRC_PLAN_HISTORY`, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.
`V$RSRC_CONSUMER_GROUP`	Displays information about active resource consumer groups. This view can be used for tuning.
`V$RSRC_CONSUMER_GROUP_CPU_MTH`	Displays all available CPU resource allocation methods for resource consumer groups.
`V$RSRC_PLAN`	Displays the names of all currently active resource plans.
`V$RSRC_PLAN_CPU_MTH`	Displays all available CPU resource allocation methods for resource plans.
`V$RSRC_PLAN_HISTORY`	Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.
`V$RSRC_SESSION_INFO`	Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.
`V$SESSION`	Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.

You can use these views for viewing privileges, viewing plan schemas , or you can monitor them to gather information for tuning the Database Resource Manager. Some examples of their use follow.

See Also:

Oracle Database Reference for detailed information about the contents of each of these views

Viewing Consumer Groups Granted to Users or Roles

The DBA_RSRC_CONSUMER_GROUP_PRIVS view displays the consumer groups granted to users or roles. Specifically, it displays the groups to which a user or role is allowed to belong or be switched. For example, in the view shown below, user scott can belong to the consumer groups market or sales, he has the ability to assign (grant) other users to the sales group but not the market group. Neither group is his initial consumer group.

SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
PUBLIC                         LOW_GROUP                      NO  NO
SCOTT                          MARKET                         NO  NO
SCOTT                          SALES                          YES NO
SYSTEM                         SYS_GROUP                      NO  YES

Scott was granted the ability to switch to these groups using the DBMS_RESOURCE_MANAGER_PRIVS package.

Viewing Plan Schema Information

This example shows using the DBA_RSRC_PLANS view to display all of the resource plans defined in the database. All of the plans displayed are active, meaning they are not staged in the pending area

SQL> SELECT PLAN,COMMENTS,STATUS FROM DBA_RSRC_PLANS;

PLAN         COMMENTS                                                 STATUS
-----------  -------------------------------------------------------  ------
SYSTEM_PLAN  Plan to give system sessions priority                    ACTIVE
BUGDB_PLAN   Resource plan/method for bug users sessions              ACTIVE
MAILDB_PLAN  Resource plan/method for mail users sessions             ACTIVE
MYDB_PLAN    Resource plan/method for bug and mail users sessions     ACTIVE
GREAT_BREAD  Great plan for great bread                               ACTIVE
ERP_PLAN     Resource plan/method for ERP Database                    ACTIVE

6 rows selected.

Viewing Current Consumer Groups for Sessions

You can use the V$SESSION view to display the consumer groups that are currently assigned to sessions.

SQL> SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION;

SID    SERIAL#  USERNAME                  RESOURCE_CONSUMER_GROUP
-----  -------  ------------------------  --------------------------------
.
.
.
   11       136 SYS                       SYS_GROUP
   13     16570 SCOTT                     SALES

10 rows selected.

Viewing the Currently Active Plans

This example sets mydb_plan, as created by the statements shown earlier in "Multilevel Schema Example", as the top level plan. The V$RSRC_PLAN view is queried to display the currently active plans.

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;

System altered.

SQL> SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;

NAME                            IS_TO
-------------------------------------
MYDB_PLAN                       TRUE
MAILDB_PLAN                     FALSE
BUGDB_PLAN                      FALSE