Skip Headers

Oracle9i Database Administrator's Guide
Release 2 (9.2)

Part Number A96521-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

27
Using the Database Resource Manager

Oracle provides database resource management capability through its Database Resource Manager. This chapter introduces you to its use.

The following topics are discussed:

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.

This section contains the following topics:

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:

How Does the Database Resource Manager Address These Problems?

Oracle's 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:

What are the Elements of the Database Resource Manager?

The elements of Oracle's 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. Oracle 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. You use the DBMS_RESOURCE_MANAGER package to create and maintain these 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.

See Also:

Oracle9i Database Concepts for more information about the Database Resource Manager

"Viewing Database Resource Manager Information"

A Single-Level Resource Plan

The first illustration, shown in Figure 27-1, is of a single-level plan, where the plan allocates resources among resource consumer groups. 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 27-1 A Simple Resource Management Plan

Text description of admin028.gif follows
Text description of the illustration admin028.gif


Oracle 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

But a plan cannot only contain resource consumer groups, it can also contain other plans, called subplans. Maybe the Great Bread Company chooses to divide their CPU resource as shown in Figure 27-2.

Figure 27-2 A Multilevel Plan With Subplans

Text description of admin027.gif follows
Text description of the illustration admin027.gif


In this case, the great_bread plan still allocates CPU resources to the consumer group market, but now it allocates CPU resources to subplans sales_team and develop_team, who in turn allocate resources to consumer groups. Figure 27-2 illustrates a plan schema, which contains a top plan (great_bread) and all of its descendents.

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 subplan as a member, but perhaps with a different CPU resource allocation in each instance.


Note:

As explained later, the above plans 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. The multiple levels of CPU resource allocation (up to eight levels) provide 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. 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.

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:

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 Oracle's predicted estimate is longer than the value specified as the switch time, then Oracle 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.

Execution Time Limit

You can specify a maximum execution time allowed for an operation. If Oracle 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 it's 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.

See Also:

Oracle9i Database Concepts for additional conceptual information about the Database Resource Manager.

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 allows you to 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's comment information.

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's comment information.

DELETE_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_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 to a plan schema.

SET_INITIAL_CONSUMER_GROUP

Sets the initial consumer group for a user.

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.

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:

Oracle9i Supplied 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. Each consumer group specified in the plan is allocated its CPU percentage at level 2. Also included in the plan are SYS_GROUP (an Oracle defined groups 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 above 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

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, what is really happening here is that 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:

  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 point to either plans or resource consumer groups.
  4. All percentages in any given level must not add up to greater than 100.
  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 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
  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. All leaves of a top plan must be resource consumer groups; at the lowest level in a plan schema the plan directives must refer to consumer groups.
  8. Plans and resource consumer groups cannot have the same name.
  9. 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 above 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 following parameters:

Parameter Description

PLAN

Name of the plan.

COMMENT

Any comment. This field is optional.

The following parameters are not required to be specified. The defaults are appropriate and the only values allowed at this time.

CPU_MTH

CPU resource allocation method. EMPHASIS is the default and the only CPU method allowed at the resource plan level.

ACTIVE_SESS_POOL_MTH

Active session pool resource allocation method. Controls maximum concurrent users. 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 sessions will execute. FIFO_TIMEOUT is the default and only method available.

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

See Also:

Oracle9i Database Concepts contains detailed descriptions of the resource allocation methods

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.

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 CPU resource allocation method for consumer groups. The default is ROUND-ROBIN. This is the only method currently available for resource consumer groups.

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

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

Specifies CPU percentage at the first level. Default is NULL for all CPU parameters.

CPU_P2

Specifies CPU percentage at the second level.

CPU_P3

Specifies CPU percentage at the third level.

CPU_P4

Specifies CPU percentage at the fourth level.

CPU_P5

Specifies CPU percentage at the fifth level.

CPU_P6

Specifies CPU percentage at the sixth level.

CPU_P7

Specifies CPU percentage at the seventh level.

CPU_P8

Specifies CPU percentage at the eighth level.

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 the execution queue (waiting for execution) will timeout. 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 is met. Default is NULL.

SWITCH_TIME

Specifies time (in seconds) that a session can execute before it is switched to another consumer group. Default in UNLIMITED.

SWITCH_ESTIMATE

If TRUE, tells Oracle 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. 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.

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 27-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:

  1. The parallel degree limit for the consumer group will be the minimum of all the incoming values.
  2. 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.
  3. 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


      Note:

      If both plan directives specify the same switch time, but different switch group's, then the choice as to which group to switch to will be statically, yet arbitrarily, decided by the Database Resource Manager.


  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 values. Specifically:
    • max_estimated_exec_time = min (all incoming max_estimated_exec_time values)

Managing Resource Consumer Groups

Before you enable the Database Resource Manager, you must assign resource consumer groups to users. In addition to providing procedures to create, update, or delete the elements used by the Database Resource Manager, the DBMS_RESOURCE_MANAGER package contains the procedure to assign 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.

You do not use a pending area for any of the procedures discussed below.

Assigning an Initial Resource Consumer Group

The initial consumer group of a user is the consumer group to which any session created by that user initially belongs. The user's initial consumer group is automatically set to DEFAULT_CONSUMER_GROUP when the user is created.

A user (or PUBLIC) must be granted permission to switch to a specific consumer group before that consumer group can become the user's initial consumer group. This permission is called the switch privilege, and is explained in "Managing the Switch Privilege". The switch privilege to an initial consumer group cannot come from a role granted to that user.

The following statements illustrate setting a user's initial consumer group.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('scott', 'sales',-
    TRUE);
EXEC DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('scott', 'sales');

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's 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'); 

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 a role's 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.

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.

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:

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 a session's consumer group assignment at session startup, or as particular modules are called.


See Also:

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

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. 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 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 = '';

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

The following statements create a multilevel schema as illustrated in Figure 27-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 => 'Bug_Online_group', 
   COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(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/method for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_users_group', 
   COMMENT => '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_GROUP(CONSUMER_GROUP => 'Mail_Maintenance_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 => 'Bug_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 => '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_DIRECTIVE(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 => 'Mail_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 => 'Mail_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_Maintenance_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 27-3 Multilevel Schema

Text description of admin057.gif follows
Text 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.

Group CPU Resource Allocation % Active Session Pool Parameters Automatic Switching Parameters Max 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%

The following statements create the above 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 provides one default resource manager plan, SYSTEM_PLAN, which gives priority to system sessions. SYSTEM_PLAN is defined as follows:

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 Oracle provided groups in this plan are:

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

You can use this simple Oracle provided 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.

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.

  1. 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.
  2. 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
            m := sqrt(4567);
          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:

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) above. 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 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.

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

List all resource plans that exist in the database.

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$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 resource allocation methods for resource plans.

V$SESSION

Lists session information for each current session. Specifically, lists the name of each current session's resource consumer group.

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:

Oracle9i 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 * FROM V$RSRC_PLAN;

NAME
--------------------------------
MYDB_PLAN
MAILDB_PLAN
BUGDB_PLAN

Go to previous page Go to next page
Oracle
Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback