Managing Resource Plans and Consumer Groups


The primary goal of the Database Resource Manager is to give the Oracle Server more control over resource management decisions. In this way, Oracle can circumvent any problems that may otherwise arise from inefficient operating system management.

Your database may encounter many problems when the database resource allocation decisions are allowed to be managed only at the operating system level as follows:

  • Excessive overhead can result from operating systems that constantly switch between Oracle processes, particularly when the number of server processes is high.

  • Inefficient scheduling can occur when the operating system unschedules services when it is inefficient to the database to do so.

  • Inefficient resource allocation can occur when the operating system strives to distribute resources equally among all active processes and is therefore unable to intelligently prioritize between different tasks.

  • Database-specific resources are often beyond the capability of the operating system to manage. This is particularly true for parallel execution servers and active sessions.

To the rescue, Oracle Database Resource Manager.

Oracle Database Resource Manager

The Oracle Database Resource Manager helps overcome the inefficiencies of the operating system to deal with Oracle-specific issues pertaining to resource allocation. The Database Resource Manager allows the database to maintain more control over the way in which the machine resources are allocated.

Through the Database Resource Manager you can guarantee that a minimum amount of processing resources is available to the database regardless of the load on the system or the number of concurrent users. Resource Manager can allow for the distribution of available processing resources by allocating a percentage of CPU time to different sets of users and applications and limit the degree of parallelism of any given operation performed by a group of users. Through this mechanism, you can create an active session pool that consists of a specific number of user session connections, and sessions beyond that maximum number get queued.

The Database Resource Manager has several components created through the use of the DBMS_RESOURCE_MANAGER package. Use of the DBMS_RESOURCE_MANAGER requires both ADMINISTER_RESOURCE_MANAGER privileges and the following of the next several sections. These sections provide details about these components and explain how they can be used.

Pending Area

Before you can modify an old resource plan or create a new plan, you have to first activate, or create, a pending area using the Database Resource Manager package. All the resource plans that you create will be stored in the data dictionary. The pending area that you create with the following command allows you to work with the resource plans in a staging area before they are implemented. The following command creates the pending area:

 EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA; 

This command clears the pending area:

 EXECUTE DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA; 

In this preproduction pending area, you can validate changes before their implementation.

Resource Consumer Groups

After you create the pending area, you are ready to create consumer groups to which you will allocate your users. Users can be created in one group and later changed to another group as the need arises.

Resource consumer groups are user sessions grouped together based on resource and processing requirements. A resource plan can have many consumer groups associated with it.

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

  • CONSUMER_GROUP or the name of the group that you are creating.

  • COMMENTS include any comments that you want to include concerning your new group.

  • CPU_MTH is the CPU allocation method to be used for the consumer group. The default (and only available as yet) method for resource allocation is ROUND-ROBIN.

There are two Oracle provided consumer groups. These two groups cannot be changed or deleted. The groups are as follows:

  • DEFAULT_CONSUMER_GROUP is the default initial group for users and sessions that have not been assigned to any other initial group.

  • OTHER_GROUPS isn't really a complete group because you can't assign users to it. Whenever a resource plan is active, the OTHER_GROUPS group is the catchall term for all sessions that don't belong to the active resource plans. You must assign a resource directive for this group in any active resource plan.

A group can be created with the following command:

 EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP;; 

Resource Plans

Now you need to create a resource plan. Resource plans are associated with a resource plan directive, each of which specifies which resources are allocated to a consumer group.

Resource plans contain directives that specify how resources are allocated to a resource consumer group. These plans specify the resource consumer groups that belong to the plan and contain directives for how resources can be allocated among the groups.

Figure 16.1 shows a simple single level resource allocation plan that might be set up for use by the Accounting Department's members. Notice the distribution of CPU allocation.

Figure 16.1. Example of a simple resource plan.


You can specify parameters for the resource plan just as you can for the resource group. These parameters are as follows:

  • PLAN is the plan name.

  • COMMENT is any descriptive comments relevant to the plan.

  • ACTIVE_SESS_POOL_MTH is the method used for managing the resources for the active session pool. The maximum number of active sessions allowed within a consumer group 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 is scheduled for execution.

  • CPU_MTH is the method for allocating the CPU among the groups (again, this as yet can take on only the value of ROUND_ROBIN).

  • PARALLEL_DEGREE_LIMIT_MTH specifies the resource allocation method to be used that will effect a specified degree of parallelism in operations within the given resource group.

  • QUEUEING_MTH specifies the queuing resource allocation method.

Now that we have discussed many of the procedures that can be called through the DBMS_RESOURCE_MANAGER let's look at creating a plan. The following code can be used to create a resource plan:

 EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN (plan_name); 

Automatic Consumer Group Switching

Automatic consumer group switching allows you to control resources by specifying criteria, and if the criteria isn't met this causes the automatic switching of sessions to another consumer group. The criteria used to determine switching include the use of the following parameters:

  • SWITCH GROUP is the group to be switched to.

  • SWITCH TIME is the switch time in seconds.

  • SWITCH ESTIMATE is the estimate of how long the operation will take to complete. This is used to decide whether to switch an operation even before it starts.

Resource Allocation Methods

Resource allocation methods are the methods and policies used by the Database Resource Manager when allocating a particular resource. These methods are used by resource consumer groups and resource plans.

Resource Plan Directives

Resource plan directives are used by administrators to associate resource consumer groups with resource plans and to allocate resources among the resource consumer groups.

[View full width]

EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( PLAN=> 'myplan', GROUP_OR_SUBPLAN => 'mygroup' EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'myplan', GROUP_OR_SUBPLAN => 'mygroup2', CPU_P1 = 50); EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN (=> 'myplan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', CPU_P1 = 100);

The set of directives created in the preceding code assigns 50% of the available CPU to the myplan plan and its group mygroup and 50% to mygroup2.

Make sure that you include a resource directive for the OTHER GROUPS group. If OTHER_GROUPS is not defined, Oracle won't let you use your plan for any groups.


Assigning Users to Consumer Groups

You can now assign users to the consumer groups. You will normally have several users in each group.

Before you can assign users to their groups, you need to grant the users privileges to set initial groups and switch groups among other things. This can be accomplished quickly and elegantly by granting the privileges to PUBLIC.

You need to execute the following package procedures to be able to assign and reassign users to different consumer groups:

  • DBMS_RESOURCE_MANAGER.GRANT_SWITCH_CONSUMER_GROUP

  • DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP

  • DBMS_RESOURCE_MANAGER.GRANT_SWITCH_CONSUMER_GROUPS

Enabling Database Resource Manager

Just because you have created all the plans, plan directives, and groups doesn't mean that you can automatically use them. For Oracle to be able to enforce the resource plans, you need to explicitly activate the Resource Manager, either by specifying the initialization parameter RESOURCE_MANAGER_PLAN in the initialization parameter file or by using the ALTER SYSTEM command as follows:

 ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=myplan; 

Deactivating the plan can be done as follows:

 ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=myotherplan; 



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net