|
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:
To the rescue, Oracle Database Resource Manager. Oracle Database Resource ManagerThe 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 AreaBefore 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 GroupsAfter 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:
There are two Oracle provided consumer groups. These two groups cannot be changed or deleted. The groups are as follows:
A group can be created with the following command: EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP;; Resource PlansNow 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:
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 SwitchingAutomatic 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:
Resource Allocation MethodsResource 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 DirectivesResource plan directives are used by administrators to associate resource consumer groups with resource plans and to allocate resources among the resource consumer groups.
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.
Assigning Users to Consumer GroupsYou 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:
Enabling Database Resource ManagerJust 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; |
|