Skip Headers

Oracle® Database Administrator's Guide
10g Release 1 (10. 1)
Part Number B10739-01
Go t o Documentation Home
Home		 Go to Book List
Book List< /td> 		Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Ma ster Index		 Go to Feedback page
Feedback

Previous		 Go to next page
Next
 View PD F

24 Using the Database Resource Manager

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

< h2>What Is the Database Resource Manager?

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

This section conta ins the following topics:

What Problems Does the Database Resource Man ager Address?

When database resource allocation decisions are left to the operating system, you may encounter the foll owing problems:

  • Excessive overhead

    Excessive overhead results from operating system context switc hing between Oracle Database server processes when the number of server processes is high.

  • Inefficient s cheduling

    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 pro blems by allowing the database more control over how machine resources are allocated.

Specifically, using the Database Resourc e Manager, you can:

  • Guarantee certain users a minimum amount of processing resources regardless of the l oad on the system and the number of users

  • Distribute available processing resources by allocating percen tages 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 oper ation 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 sessio ns 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 defi ned 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 allocati ng resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut d own 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 Datab ase Resource Manager?

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

User sessions grouped together based on resource processing requirements.
Element Description
Resource consumer group
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 resourc e consumer groups and resource plans. The database provides the resource allocation methods that are available, but you determine whi ch method to use.
Resource plan directive Used by administrators to associate resource consumer groups with particular plans and allocate resources among resource consumer groups.< /td>

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 comple x plans are included in the examples presented later ("Putting It All Together: Database Resource Manager Example s "), 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 g roups. You use the DBMS_RESOURCE_MANAGER package to create and maintain these elements of the Database Resource Manager: resource plans, resource consumer groups, and reso urce plan directives. Plan information is stored in tables in the data dictionary. Several views are available for viewing plan data.


See Also:

"Viewing Database Resource Manager Information"

A Single-Level Resource Plan

The first illustration, shown in Figure 24-1, is of a single-level plan, where the plan allocates resources among res ource consumer groups. The Great Bread Company has a plan called GREAT_BREAD that allocates CPU resources among three re source 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< /strong>

Description of admin028.gif follows
Description of the illustration admin028.gif

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 ".

A Multilevel Resource Plan

In addition to containing resource consumer groups, a plan can contain subplans. 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 Mu ltilevel Plan With Subplans

Description of admin027.gif follows
Description of the illustration admin027.gif

In this case, the GREAT_BREAD plan still allocates 20% of CPU resources to the consumer group < code>MARKET. However, now it allocates CPU resources to subplans SALES_TEAM (60%), which in turn divides its shar e equally between the WHOLESALE and RETAIL groups, and DEVELOP_TEAM (20%), which in turn divid es 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 Com pany had a night plan and a day plan. Both the night plan and the day plan contain the sales subplan as a member, but pe rhaps with a different CPU resource allocation in each instance.


Note:

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

< /div>

Resource Consumer Groups

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

< /a>

Resource Plan Directives

How resources are allocate d 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 g roups or subplans. The multiple levels of CPU resource allocation (up to eight levels) provi de a means of prioritizing CPU usage within a plan schema. Level 2 gets resources only after level 1 is unable to use all of its resources. Mul tiple levels not only provide a way of prioritizing, but they provide a way of explicitly specifying how all primary and leftover res ources are to be used.

Active Session Pool with Queuing

You can control the maximum number of concurrently active sessions allowed within a consumer group. This maximum designates the ac tive session pool. When a session cannot be initiated because the pool is full, the session is placed into a queue. When an active se ssion completes, the first session in the queue can then be scheduled for execution. You can also specify a timeout period after whic h 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 opera tion 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 sessi ons 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 t he top call finishes, the session is restored to its original consumer group.

  • Use estimate: specifies wh ether the database is to use its own estimate of how long an operation will execute

The Database Resource Manager sw itches a running session to switch group if the session is active for more than switch time seconds. Active means t hat 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 mor e sessions running than specified by its active session pool. Once the session finishes its operation and becomes idle, it is switche d back to its original group.

If use estimate is set to TRUE, the Database Resource Manager uses a predi cted estimate of how long the operation will take to complete. If the database estimate is longer than the value specified as the swi tch 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 gr oup--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 t reated 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.

Cance ling SQL and Terminating Sessions

You can also specify directives to cancel long-running SQL queries or to terminate l ong-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 li mit, the current DML statement generating the redo is terminated. No other members of the consumer group can perform further data man ipulation until undo space is freed from the pool.

Idle Time Limit

You can specify an amount o f 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 p rivilege with the ADMIN option as part of the DBA (or equivalent) role.

Being an administrator for the Database R esource 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 succeed ing sections of this chapter.

Procedure Description
CREATE_SIMPLE_PLAN Creates a simple resource pla n, 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 pl an and all of its descendents.
CREATE_ CONSUMER_GROUP Creates a resource consumer group.
UPDATE_CONSUMER_GROUP Updates a consumer group.
D ELETE_CONSUMER_GROUP Deletes a consumer group.
CREATE_PLAN_DIRECTIVE Specifies the resource plan directives that allocate resources to resource consumer groups within a plan or among subplans in a multilevel plan schema.
UPDATE_P LAN_DIRECTIVE Updates plan directives
DELETE_PLAN_DIRECTIVE Deletes plan directives
CREATE_PEND ING_AREA Creates a pending area (scratch area) within which changes can be ma de 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 s chema
SET_INITIAL_CONSUMER_GROUP Sets the initial consumer group for a user. This procedure has been deprecated. T he 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 M aps sessions to consumer groups
SET_C ONSUMER_MAPPING_PRI Establishes session attribute mapping priorities

You may, as an administrator with the ADMIN opt ion, 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 proced ures listed in the table below.

Procedure Description
GRANT_SYSTEM_PRIVILEGE Grants ADMI NISTER_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 use r, role, or PUBLIC to switch to a specified resource consumer group.
REVOKE_SWITCH_CONSUMER_GROUP Revok es 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 proce dures in the DBMS_RESOURCE_MANAGER pac kage, but scott cannot use the GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privilege to ot hers.

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

You can revoke this privilege using the < code>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. I t cannot be granted or revoked through the SQL GRANT or REVOKE statements.

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


< /tbody>

See Also:

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

  • DBMS_RESOURCE_MANAGER

  • DBMS_RESOURCE_MANAGER_PRIVS


Creatin g a Simple Resource Plan

You can qui ckly 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 a nd allocate resources to them by executing a single statement. Using this procedure, you are not required to invoke the procedures th at are described in succeeding sections for creating a pending area, creating each consumer group individually, and specifying resour ce plan directives.

You can specify the following parameters for the CREATE_SIMPLE_PLAN procedure:

Parameter Description
SIMPLE_PL AN Name of the plan
CONSUMER_GROUP1 Consumer group name for first group
GROUP1_CPU CPU resource allocated to this group
CONSUMER_GROUP2 Consumer g roup name for second group
GROUP2_CP U 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 t his group
CONSUMER_GROUP7 Consumer group name for seventh group
GROUP7_CPU CPU resource allocated to this group
CONSUMER_G ROUP8 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 EM PHASIS CPU allocation policy and each consumer group uses the ROUND_ROBIN scheduling policy.Each consumer group s pecified in the plan is allocated its CPU percentage at level 2. Also included in the plan are SYS_GROUP (a system-defin ed 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 => 'mygr
oup1', GROUP1_CPU => 80,
   CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20);
END;

Executing the preceding state ments creates the following plan:

Consumer Group Level 1 Level 2 Level 3
< code>SYS_GROUP 100% - -
mygroup1 - 80% -
mygroup2 - 20% -
OTHER_GROUPS - - 100%

Creating Complex Resource Plans

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

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. Th is 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_PEN
DING_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 dictio nary 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 Datab ase 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:

  1. No plan schema can contain any loops.

  2. All plans and resource consumer groups referred to by plan directives must exist.

  3. All plans must have plan directives that poi nt to either plans or resource consumer groups.

  4. All percentages in any given level must not add up to greater than 1 00.

  5. A plan that is currently being used as a top plan by an active instance cannot be deleted.

  6. The following plan directive parameters can appear only in plan directives that refer to resourc e consumer groups (not other resource plans):

    • PARALLEL_DEGREE_LIMIT_P1

    • ACTIVE_SESS_POOL_P1

    • QUEUEING_P1

    • SW ITCH_GROUP

    • SWITCH_TIME

    • SWITCH_ESTIMATE

    • MAX_EST_EXEC_TIME

    • UNDO_POOL

    • MAX_IDLE_TIME

    • MAX_IDLE_BLOCKER_TIME

    • SW ITCH_TIME_IN_CALL

  7. There can be no more than 32 resource consumer groups in any active plan schema. Also, at most, a plan can have 32 children.

  8. Plans and resource consumer groups cannot have the same name.

    9. < li>

    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 the m. 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 t o make your changes active.

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

The submit proc edure 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 s ubmitted (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 als o a procedure for clearing the pending area at any time. This statement causes all of your changes to be cleared from the pending are a:

EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

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

< /a>

Creating Resource Plans

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

Parameter Description
PLAN Name of the plan.
COMMENT Any d escriptive comment.
CPU_MTH Re source allocation method for specifying how much CPU each consumer group or subplan gets. EMPHASIS, the default method, is for mul tilevel plans that use percentages to specify how CPU is distributed among consumer groups. RATIO is for single-level pl ans 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_A BSOLUTE is the default and only method available.
PARALLEL_DEGREE_LIMIT_MTH Resource allocation method for specifyi ng 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.

< /div>

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

Creating a Plan

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

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'great_bread', -
    COMMEN
T => '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 pro cedure, 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 br
ead');

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 => 'grea
t_bread');

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

The DELETE_PLAN_CASCADE procedure deletes the speci fied plan as well as all its descendants (plan directives, subplans, resource consumer group s). 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 sp ecify 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 =&g
t; '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_C
G', 
    COMMENT => 'Bonze service level customers',
    CPU_P1 => 2);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
    (PLAN =&g
t; '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 foll owing parameters:

< font face="Arial, Helvetica, sans-serif">Parameter Description
CONSUMER_GROUP Name of t he consumer group.
COMMENT Any comment.
CPU_MTH The resource allocation method for distribu ting CPU among sessions in the consumer group. The default is ROUND_ROBIN, which uses a round-robin scheduler to ensure that sessi ons are fairly executed. RUN_TO_COMPLETION specifies that sessions with the largest active time are scheduled ahead of o ther sessions.

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

  • DEFAUL T_CONSUMER_GROUP

    This is the initial consumer group for all users/sessions that have not been explicitly assigned an in itial consumer group. DEFAULT_CONSUMER_GROUP< /code> 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 as signed to a user. OTHER_GROUPS must have a resource directive specified in the schema of any active plan. This group app lies 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 execu te 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 yo u 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_GRO UP procedure deletes the specified consumer group. Upon deletion of a consumer group, all users having the deleted group as th eir 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 w ill 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 alloca tion 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 leve l. For RATIO, specifies the weight of CPU usage. Default is NULL for all CPU parameters.< /tr> 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< /code>. 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< /td> For EMPHASIS, specifies CPU percentage at the sixth level. Not applic able 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 eight h level. Not applicable for RATIO. ACTIVE_SESS_POOL_P1 Specifies maximum number of concurrently a ctive 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 g roup 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 i n 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 g roup of an operation prior to beginning its execution. Default is FALSE. MAX_EST_EXEC_TIME Specif ies 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 kil obytes (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 < td align="left" headers="r23c1-t15 r1c2-t15">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 resourc e 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 =&
gt; '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 =&
gt; '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.

< pre xml:space="preserve">EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'great_bread', - GROUP_OR_SUBPLAN => 'd evelop', 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 co nsumer group, then the following rules apply for specific cases:

  1. The parallel degree limit for th e consumer group will be the minimum of all the incoming values.

  2. The active session pool for the consumer g roup will be the sum of all the incoming values and the queue timeout will be the minimum of all incoming timeout v alues.

  3. If there is more than one switch group and more than one switch time, the Database Resource Manager will choo se 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:< /strong>

      • 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.


  4. 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.

  5. The maximum estimated execution time will be the most restrictive of all incoming v alues. Specifically:

    MAX_EST_EXEC_TIME = min (all incoming MAX_EST_EXEC_TIME values)

Managing Resour ce 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 mapp ings that enable the database to automatically assign user sessions to consumer groups depending upon session attributes.

In a ddition to providing procedures to create, update, or delete the elements used by the Database Resource Manager, the DBMS_RESOU RCE_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 s ession to another consumer group.

The DBMS_RESOURCE_MANAGER_PRIVS package, described earlier for granting the Database Resource Manager system privilege, can also b e 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_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

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 Gro ups 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 c onsumer group of running sessions. Both of these procedures can also change the consumer group of any parallel execution server sessi ons associated with the coordinator session. The changes made by these procedures pertain to current sessions only; they are not pers istent. They also do not change the initial consumer groups for users.

Instead of killing a session of a user who is using exc essive 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 i nto 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');
< p>The SID, session serial number, and current resource consumer group for a session are viewable using the V$SESSI ON 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 Pac kage 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 t o switch to a consumer group for which they have the switch privilege. If the caller is another procedure, then this procedure enable s users to switch to a consumer group for which the owner of that procedure has switch privileges.

The parameters for this pro cedure are:

Parameter Description
NEW_CONSUMER_GROUP The consumer group to which the user is switching.
OLD_CONSUMER_GROUP An output parameter. Stores th e 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 in itial 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
DEC
LARE
    old_group varchar2(30);
BEGIN
DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('sales', old_group, FALSE);
DBMS_OUTPUT.PUT_LINE('O
LD 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:

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:

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

Managing the Sw itch Privilege

Using the DBMS_RESOURCE_MANAGER_PRIVS package, yo u 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 swit ch privilege.

The actual switching is done by executing a procedure in the DBMS_SESSION package. A user who has b een 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 .

Gra nting the Switch Privilege

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

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

User scott is al so 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 y ou 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_OPTIO N argument is TRUE, then users granted switch privilege for the consumer group can also grant switch privileges f or that consumer group to others.

Revoking Switch Privileges< /h4>

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 loggin
g in.


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


If you revoke switch privil
eges to a consumer group from PUBLIC, then any users other than those who are explicitly assigned switch privileges eith
er 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 preceden ce in case of conflicts.

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

You use the SET_CONSUMER_GROUP_MAPPING and SET_CONSUMER_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 SET_CONSUMER_GROUP_MAPPING procedure maps a session attribute to a consumer group. The parameters for th is procedure are:

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

The attribute can be one of the following:

n/a
Attribute Type Description
ORACLE_USER Login The standard Oracle Database user name
SERVICE_NAME Login The service name used by the client to establish a login
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_NAME procedure, or the equivalent OCI attribute setting
MODULE_NAME_ACTION Runtime The current module and action being perfor med by the user as set by either of the following procedures, or their equivalent OCI attribute setting:

  • DBMS_APPLICATION_INFO.SET_MODULE_NAME

  • DBMS_APPLICATION_INFO.SET_ACTION

    • < /ul>

    The attribute is specified by the module name, followed by a period (.), followed by the action name.

SERVICE_MODULE Runtime A combination service and module names in this form: < code>service_name.module_name
SERVICE_MODULE_ACTION Runtime A combination of service and module names and action name, in this form: service_name.module_name.action_name< /code>
EXPLICIT An explicit mapping refers to the consumer group explicitly requested by the client, for example by invoking the SWITCH_CURRENT_CONSUMER_GROUP, SWIT CH_CONSUMER_GROUP_FOR_SESS, or SWITCH_CONSUMER_GROUP_FOR_USER procedure. See "Explicit Sessio n Switching"

For each of the session attributes, you spe cify a mapping that consists of a set of pairs (attribute, consumer group) that determines how a session is matched to a con sumer group. For example, the following statement causes user scott to map to the dev_group every time he l ogs in:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING  
     (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'sc
ott', 'dev group');

Creating Attribute Mapping Priorities

In order to decide between confl icting mappings, you can establish a priority ordering of the attributes from most important to least important. The priority of each attribute is set to a unique integer from 1 to 10 using the SET_CONSUMER_MAPPING_PRI procedure. For example, the follow ing statement illustrates this setting of priorities:

DBMS_RESOURCE_MANAGER.SET_MAPPING_PRIORITY(
   EX
PLICIT => 1, CLIENT_MACHINE => 2, MODULE_NAME => 3, ORACLE_USER => 4,
   SERVICE_NAME => 5, CLIENT_OS_USER => 6, CL
IENT_PROGRAM => 7,
   MODULE_NAME_ACTION => 8, SERVICE_MODULE=>9, SERVICE_MODULE_ACTION=>10);

To illustrate h ow mapping priorities work, assume that in addition to the mapping of scott to the dev 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');

In this example, the priority of the database user name is set t o 4 (less important), while the priority of the module name is set to 3 (more important). Now if the session sets its module name to backup, presumably because it is going to perform a backup operation, then the session would be reassigned to the low priority consumer group, because that mapping has a higher priority.

To prevent unauthorized clients from setting t heir 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 ses sion 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.

Explicit Session Switc hing

A session can manually switch its own consumer group, given the privilege to do so, using the procedure DBM S_SESSION.SWITCH_CURRENT_CONSUMER_GROUP. Although there is no mapping for the EXPLICIT group setting, it is consi dered an attribute in the mapping priorities when deciding whether to use this EXPLICIT setting or use the consumer grou p mapping. In other words, if the EXPLICIT setting has the highest priority, then the session always switches consumer g roups when it calls the SWITCH_CURRENT_CONSUMER_GROUP procedure.

On the other hand, if the EXPLICIT setting has the lowest priority, then the session only switches consumer groups if none of the other session attributes match in the mappings. Note that the Database Resource Manager considers a switch to have taken place even if the SWITCH_CURRENT_COSUMER_GRO UP procedure is called to switch the session to the consumer group that it is already in.

Automatic Gr oup 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 va lue (only possible for runtime attributes) then the mapping is reevaluated and the session is switched to the appropriate consumer gr oup.

  • If a runtime session attribute is modified such that the current mapping becomes a different attrib ute 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 consume r 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 Resou rce Manager

You enable the Database Resource Manager by setting the RESOURCE_MANA GER_PLAN initialization parameter. This para meter 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. The following example activates the Database Resource Manager and assigns the top plan as mydb_plan.

RE
SOURCE_MANAGER_PLAN = mydb_plan

You can also activate or deactivate the Database Resource Manager, or change the current top plan, using the ALTER SYSTEM statement. In this example, the top plan is sp ecified 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 Ma nager, issue the following statement:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

When ena bled, the DBMS Scheduler can automatically change the Resource Manager plan at Scheduler window boundaries. In some cases, this may b e unacceptable. For example, if you have an important task to finish, and if you set the Resource Manager plan to give your task prio rity, then you expect that the plan will remain the same until you change it. However, because a Scheduler window could become activa ted 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 wit h "FORCE:". Using the prefix FORCE indicates that the current Resource Manager plan can be changed only whe n 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';

Putting It All Together : Database Resource Manager Examples

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

Multilevel Schema Example

The following statements create a multileve l 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', 
   COMMEN
T => '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 => 'Bug_Online
_group', 
   COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GRO
UP(CONSUMER_GROUP => 'Bug_Batch_group', 
   COMMENT => 'Resource consumer group/method for batch job bug users sessions');
DBMS
_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maintenance_group',
   COMMENT => 'Resource consumer group/metho
d for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_users_group', 
   COM
MENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP =>
; 'Mail_Postman_group',
   COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GR
OUP(CONSUMER_GROUP => 'Mail_Maintenance_group', 
   COMMENT => 'Resource consumer group/method for users sessions for mail db m
aint');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
   GROUP_OR_SUBPLAN => 'Bug_Online_group',
   COMMENT
 => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
   PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MA
NAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', 
   GROUP_OR_SUBPLAN => 'Bug_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_DIREC
TIVE(PLAN => 'bugdb_plan', 
   GROUP_OR_SUBPLAN => 'Bug_Maintenance_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 =&
gt; 'Mail_Postman_group',
   COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 =&
gt; 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'Mail_users_group',
   COMMEN
T => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGE
R.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'Mail_Maintenance_group',
   COMMENT => 'mail maintena
nce users sessions at level 2', CPU_P1 => 0, CPU_P2 => 20,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_P
LAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'OTHER_GROUPS', 
   COMMENT => 'all other users sessions at leve
l 3', CPU_P1 => 0, CPU_P2 => 0,
   CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', 
   G
ROUP_OR_SUBPLAN => 'maildb_plan', 
   COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.C
REATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', 
   GROUP_OR_SUBPLAN => 'bugdb_plan', 
   COMMENT => 'all bug users sessions at le
vel 1', CPU_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
< p>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 the illustration admin057.gif

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.

< /tr>
Group CPU Resource Allocation % Active Session Pool Parameters Automatic Switching Parameters< /font> Maximum Estimated Execut ion Time Undo P ool
oltp Level 1: 80%
Switch to group: batch

Switch time: 3

Use estimate: TR UE

 -- Size: 200K
batch Level 2: 100% Pool size: 5

Timeout: 600

 -- Time: 3600 --
OTHER_GROUPS Level 3: 100% -- -- -- --

The following statements create the p receding 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_MA
NAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp', 
  COMMENT => 'Resource consumer group/method for OLTP jobs');
DBMS_RESOU
RCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch', 
  COMMENT => 'Resource consumer group/method for BATCH jobs');
DB
MS_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_M
ANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', 
  GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 1
00, 
  ACTIVE_SESS_POOL_P1 => 5, QUEUEING_P1 => 600, 
  MAX_EST_EXEC_TIME => 3600);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECT
IVE(PLAN => 'erp_plan', 
  GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P3 => 100);
DBMS_RESOURCE_MANA
GER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

An Oracle-Supplied Plan

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

Resource Consum er Group CPU Resource Allocation
Level 1 Level 2< /th> Level 3
SYS_GROUP 100% 0% 0%
OTHER_GROUPS 0% 100% 0%
LOW_GROU P 0% 0% 1 00%

The database-provided groups in this plan are:

< ul>

  • SYS_GROUP is the initial consumer group for the users SYS and SYSTEM.< /p>

  • OTHER_GROUPS applies collectively to all sessions that belong to a consumer group that is n ot part of the currently active plan schema.

  • LOW_GROUP provides a group having lower priori ty 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 u sed, 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 representat ive 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 act ive 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, t here must be sufficient load (demand for CPU resources) to make CPU resources scarce. If the following rules are followed, the test e nvironment should generate actual (measured) resource allocations that match those specified in the active resource plan.

    1. Create the minimum number of concurrently running processes required to generate sufficient load. This is th e larger of:

      • Four processes for each consumer group

      • 1.5 * (number of proces sors) for each consumer group. If the result is not an integer, round up.

    2. Each and every process must be c apable 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
        m := sqrt(4567);
      END LOOP;
    END LOOP;
  END;
END;
/

    Why Is This Necessary to Produce Expected Results?

    When every group can secure as muc h CPU resources as it demands, the Database Resource Manager first seeks to maximize system throughput, not to enforce allocation per centages. 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.< /p>

    In this case, the measured CPU allocation to each consumer group will be 25%, no matter what the allocations specifi ed 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 para graphs.

    Until the number of concurrently running processes reaches a certain level, typical operating system scheduling algori thms 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 underuti lized 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 loade d system with enough processes waiting, this strategy will work. In large production environments, processor affinity increases perfo rmance significantly, because invalidating the current CPU cache and then loading the new one is quite expensive. Since processes hav e processor affinity on most platforms, more processes than CPUs for each consumer group must be run. Otherwise, full system utilizat ion is not possible.

    Monitoring Results

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

    SQL> SELECT NAME, CONSUMED_CPU_TIME FROM V$RSRC_CONSUMER_G
ROUP;

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

3 r
ows selected.

    Interaction with Opera ting-System Resource Control

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

    Guidelines for Using Operating-System R esource 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:

    1. 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, b oth the operating system and Database Resource Manager try to control resource allocation in a manner that causes unpredictable behav ior 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 Da tabase currently does not support the use of both tools simultaneously. Future releases might allow for their interaction on a limite d scale.

    2. In an Oracle Database enviro nment, the use of an operating-system resource manager, such as Hewlett Packard's Process Resource Manager (PRM) or Sun's Solaris Res ource 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.


        Caut ion:

        Management of individual database processes at different priority levels (for example, using the nice< /code> command on UNIX platforms) is not supported. Severe consequences, including instance crashes, can result. You can expect simil ar undesirable results if operating-system resource control is permitted to manage memory on which an Oracle Database instance is pin ned.

    3. If you chose to use operating-system resource control, make sure you turn off the Database Resource Manager. By default, the Da tabase 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 f ile, 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 Data base. There is no need to restart an instance if the number of CPUs changes.

    The database dynamically detects any change in th e 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

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

    < /tr>
    View Description
    DBA_RSRC_CONSUMER_GROUP_PRIV S

    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_SYSTE M_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_RSR C_GROUP_MAPPINGS Lists all of the various mapping pairs for all of the sessi on attributes
    DBA_RSRC_MAPPING_PRIOR ITY 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 in formation about the current user, and specifically, for the Database Resource Manager, it contains the current user's initial resourc e consumer group.
    V$ACTIVE_SESS_POO L_MTH Displays all available active session pool resource allocation method s.
    V$PARALLEL_DEGREE_LIMIT_MTH Displays all available parallel degree limit resource allocation methods.
    V$QUEUEING Displays all available queuing resource allocation methods.
    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 re source allocation methods for resource plans.
    V$SESSION Lists session information for each current session. Sp ecifically, 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 informat ion for tuning the Database Resource Manager. Some examples of their use follow.



    Viewing Consumer Groups Granted to Users or Roles

    The DBA_RSRC_CONS UMER_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 con sumer groups market or sales, he has the ability to assign (grant) other users to the sales gr oup 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.

    < !-- class="sect2" -->

    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 t
o give system sessions priority                    ACTIVE
BUGDB_PLAN   Resource plan/method for bug users sessions              ACTIV
E
MAILDB_PLAN  Resource plan/method for mail users sessions             ACTIVE
MYDB_PLAN    Resource plan/method for bug and mail use
rs 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 Session s

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

    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 earl ier in "Multilevel Schema Example", as the top level plan. The V$RSRC_PLAN view is queried to di splay the currently active plans.

    SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;

System a
ltered.

SQL> SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;

NAME                            IS_TO
----------------------------------
---
MYDB_PLAN                       TRUE
MAILDB_PLAN                     FALSE
BUGDB_PLAN                      FALSE
    < !-- class="sect2" -->

    See Also:

    Oracle Database Reference for detailed information about the contents of each of these views
    Go to previous page
    Previous    		 Go to next page
    Next
    		 Oracle
    Copyright © 2001, 2003 Oracle Corporation
    All Rights Res erved.
    Go to Documentation Home
    Home    		 Go to Book List
    Book List    		 Go to Table of Contents
    Contents    		 Go to I ndex
    Index    		 Go to Master Index
    Master Index    		 Go to Fee dback page
    Feedback