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 Pendin Area for Creating Plan Schemas
The first thing you must do to create or modify plan schemas is to create a pending area. This is a scratch area allowing you to stage your changes and to validate them before they are made active.
Creating a Pending Area
To create a pending area, you use the following statement:EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
In effect, you are making the pending area active and "loading" all existing, or active, plan schemas into the pending area so that they can be updated or new plans added. Active plan schemas are those schemas already stored in the data dictionary for use by the Database Resource Manager. If you attempt to update a plan or add a new plan without first activating (creating) the pending area, you will receive an error message notifying you that the pending area is not active. Views are available for inspecting all active resource plan schemas as well as the pending ones.
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:
You will receive an error message if any of the preceding rules are violated. You can then make changes to fix any problems and call the validate procedure again. It is possible to create "orphan" consumer groups that have no plan directives referring to them. This allows the creation of consumer groups that will not currently be used, but may be part of some plan to be implemented in the future.
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.
Clearing the Pending Area
There is also a procedure for clearing the pending area at any time. This statement causes all of your changes to be cleared from the pending area:EXEC DBMS _RESOURCE _MANAGER.CLEAR _PENDING _AREA;
You must call the CREATE _PENDING _AREA procedure before you can again attempt to make changes.
Creating Resource Plans
When you create a resource plan, you can specify the parameters shown in the following table. The first parameter is required; the remainder are optional .
Oracle Database provides one resource plan, SYSTEM _PLAN, that contains a simple structure that may be adequate for some environments. 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.
Deleting a Plan
The DELETE _PLAN procedure deletes the specified plan as well as all the plan directives associated with it. The following statement deletes the great_bread plan and its directives.EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'great_bread');
The resource consumer groups themselves are not deleted, but they are no longer associated with the great_bread plan. The DELETE_PLAN_CASCADE procedure deletes the specified plan as well as all its descendants (plan directives, subplans, resource consumer groups). If DELETE_PLAN_CASCADE encounters an error, it will roll back, leaving the plan schema unchanged.
Using the Ratio Policy
The RATIO policy is a single -level CPU allocation method. Instead of percentages, you specify numbers corresponding to the ratio of CPU you want to give to the consumer group. For example, given three consumer groups GOLD_CG, SILVER_ CG, and BRONZE_CG, assume that we specify the following plan directives:
The ratio of CPU allocation would be 10:5:2:1 for the GOLD_CG, SILVER_CG, BRONZE_CG, and OTHER_GROUPS consumer groups, respectively. If sessions exists only in the GOLD_CG and SILVER_CG consumer groups, then the ratio of CPU allocation would be 10:5 between the two groups.
Creating Resource Consumer Groups
When you create a resource consumer group, you can specify the following parameters:
There are two special consumer groups that are always present in the data dictionary, and they cannot be modified or deleted. These are:
This is the initial consumer group for all users/sessions that have not been explicitly assigned an initial consumer group. DEFAULT_CONSUMER_GROUP has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this consumer group.
This consumer group cannot be explicitly assigned to a user. OTHER _GROUPS must have a resource directive specified in the schema of any active plan. This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema, including DEFAULT _CONSUMER _GROUP.
Additionally, two other groups, SYS_GROUP and LOW_GROUP, are provided as part of the Oracle -supplied SYSTEM_PLAN .
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.
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
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.
To complete the plan, execute the following statements: 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.
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:
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.