11.6. Putting the Pieces Together
Now that you've learned about all of the various elements of DRM individually, it's time to put them all together.
For most companies, database requirements differ depending on the time of day. For example, during normal daytime business hours, online transaction processing (OLTP) may be mission-critical, along with a small amount of report processing. After hours, however, bulk data loads and online analytical processing (OLAP) reports may take priority.
In this section, you'll learn how to develop complex multi-level resource plans to accommodate these types of business requirements. You'll see how all of the elements are created and associated to the plan. You'll also learn how to enable the finalized plan.
11.6.1. Creating the Elements
In this section, you will be provided step-by-step instructions for the creation of the plans shown in Figures 11.2 and 11.3. First, the pending area will be created. Next, the consumer resource groups will be created. After that, the resource plans will be created. And, finally, the resource plan directives will be created to tie them all together.
Once all of the elements are in place, they will be validated and activated. Finally, you'll learn to enable the resource plans, as well as how to switch the enabled resource plan.
Figure 11.2. OFF_HOURS_PLAN high-level design
Figure 11.3. DAY_PLAN high-level design
11.6.1.1. Creating the Pending Area
Before creating any new DRM elements, a pending area must be established to hold your new plans. The pending area is a development area where you can work on DRM elements without affecting the active DRM plan. It can be created as shown here:
SQL> exec dbms_resource_manager.create_pending_area(); PL/SQL procedure successfully completed.
Once the pending area is in place, new elements will reside there until the plan is enabled.
11.6.1.2. Creating the Consumer Resource Groups
Next, the consumer resource groups can be created. In this step, all consumer resource groups required by both resource plans will be created.
These groups can be created as follows:
SQL> begin
dbms_resource_manager.create_consumer_group(
'OLTP_GROUP','Incoming orders'),
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_consumer_group(
'DAY_REPORTS_GROUP','DAYTIME REPORTS'),
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_consumer_group(
'NIGHTLY_PROCESSING_GROUP','BULK LOADS, ETL, ETC.'),
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_consumer_group(
'OLAP_REPORTS_GROUP','OFF HOURS REPORTS'),
end;
SQL>/
PL/SQL procedure successfully completed.NOTE
Because the SYS_GROUP and the OTHER_GROUPS consumer groups are created automatically at Oracle installation time, there is no need to create them.
11.6.1.3. Creating the Resource Plans
Now that all the necessary consumer groups are in place, the next step is to create the resource plans. Three distinct plans are required. Both the DAY_PLAN and OLTP_PLAN plans use the default EMPHASIS CPU allocation method, whereas the OFF_HOURS_PLAN plan utilizes the RATIO method. Remember that the CPU resource allocation method (CPU_MTH) sets the type of allocation used only if there is a resource plan directive which specifies CPU allocation. These plans can be created as shown here:
SQL> begin
dbms_resource_manager.create_plan(
PLAN => 'DAY_PLAN',
COMMENT => 'GOVERNS NORMAL WORKING HOURS '),
end;
SQL> /
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_plan(
PLAN => 'OLTP_PLAN',
COMMENT => 'ORDER ENTRY SUB-PLAN'),
end;
SQL> /
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_plan(
PLAN => 'OFF_HOURS_PLAN',
COMMENT => 'GOVERNS NON-WORKING HOURS',
CPU_MTH => 'RATIO'),
end;
SQL> /
PL/SQL procedure successfully completed.Because the default CPU allocation method is EMPHASIS, the CPU_MTH parameter was left out when creating the first two plans. For the OFF_HOURS_PLAN plan, the CPU_MTH parameter was explicitly set.
11.6.1.4. Creating the Resource Plan Directives
Next, the resource plan directives need to be created. This will be done in three steps. First, the directives for the OFF_HOURS_PLAN plan will be created. Next, the directives for the OLTP_PLAN plan will be created. Finally, the directives for the DAY_PLAN plan will be created.
11.6.1.4.1. Creating the OFF_HOURS_PLAN Plan Directives
The OFF_HOURS_PLAN plan is a single-level plan using the RATIO method for CPU allocation. The plan directives can be created as follows:
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 10);
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'NIGHTLY_PROCESSING_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY JOBS',
CPU_P1 => 5);
end;
SQL>/
PL/SQL procedure successfully completed.SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OLAP_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY REPORTS',
CPU_P1 => 2);
end;
SQL>/
PL/SQL procedure successfully completed.The CPU allocation ratio for the OFF_HOURS_PLAN plan will be 10:5:2:1.
11.6.1.4.2. Creating the OLTP_PLAN Plan Directives
Next, the plan directives for the OLTP_PLAN plan can be created:
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_GROUP',
COMMENT => 'CPU ALLOCATION FOR OLTP USERS',
CPU_P1 => 90);
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'DAY_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR DAYTIME REPORTING',
CPU_P1 => 10);
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P2 => 100);
end;
SQL>/
PL/SQL procedure successfully completed.As you can see, the directives for the OLTP_PLAN plan allocate 90 percent of level 1 CPU resources to the OLTP_GROUP group, and the other 10 percent to the DAY_REPORTS_GROUP group. 100 percent of level 2 CPU resources are allocated to the OTHER_GROUPS group.
11.6.1.4.3. Creating the DAY_PLAN Plan Directives
Now, the directives for the DAY_PLAN plan can be created:
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 100);
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_PLAN',
COMMENT => 'CPU ALLOCATION FOR OLTP_PLAN SUB-PLAN',
CPU_P2 => 100);
end;
SQL>/
PL/SQL procedure successfully completed.
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P3 => 100);end; SQL>/ PL/SQL procedure successfully completed.
You may have noticed that both the DAY_PLAN and OLTP_PLAN plans allocate resources to the OTHER_GROUPS group. Remember that every resource plan must have an allocation to the OTHER_GROUPS group. In fact, any consumer group can be assigned to more than one plan, as long as no loops are created as a result.
11.6.1.5. Validating the Pending Area
Now that all of the necessary elements have been created and defined, the contents of the pending area must be validated. Validation checks for any rule violations that may exist when the elements are grouped under their respective plans.
Validation will be done for all elements in the pending area, as shown here:
SQL> exec dbms_resource_manager.validate_pending_area; PL/SQL procedure successfully completed.
When validating the pending area, no news is good news. As long as the procedure completes and no error messages are returned, the pending area has passed inspection.
|
11.6.1.6. Submitting the Pending Area
The final step is to activate the plans by submitting the pending area. This step moves the DRM elements to the data dictionary. Once the elements are in the data dictionary, they are considered active and eligible to be enabled (resource plans) or referenced by enabled plans (resource consumer groups and resource plan directives). Remember that this step does not actually enable a plan, it only makes it eligible to be enabled.
Also, when submitting a pending area, Oracle automatically performs the same validation that was done in the previous step. Therefore, the validation step above is technically unnecessary. However, it is still a good idea from a debugging standpoint, especially when designing very complex plans.
The pending area can be submitted as shown:
SQL> exec dbms_resource_manager.submit_pending_area; PL/SQL procedure successfully completed.
Again, the absence of error messages signifies successful submission of the pending area to the data dictionary. The plans are now active (in other words, residing in the data dictionary) and can be enabled at any time.
11.6.1.7. Enabling the Resource Plans
When a resource plan is enabled, it governs all resource allocation for the Oracle instance. Only one resource plan may be enabled at any given time, and the enabled plan can be switched at any time.
There are two methods in which resource plans can be enabled:
Initialization parameter (at instance startup time)
ALTER SYSTEM statement
11.6.1.7.1. Initialization Parameter Method
In the init.ora file, the RESOURCE_MANAGER_PLAN initialization variable can be set to the name of any active plan. For example, the following code can be added to the init.ora file:
RESOURCE_MANAGER_PLAN = DAY_PLAN;
When the instance is restarted, DAY_PLAN will be the enabled plan for the instance.
11.6.1.8. ALTER SYSTEM Statement Method
Resource plans can also be enabled dynamically by using the ALTER SYSTEM statement, as shown here:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAY_PLAN' [SCOPE = BOTH];
This dynamically enables the DAY_PLAN plan for the instance. There is no need to shut down the instance in this case. The optional SCOPE clause can be used in an SPFILE environment to change the setting both in memory and in the SPFILE (to make the change persist through a shutdown).
11.6.1.9. Switching the Enabled Resource Plan
The top-level plans that were created in this section are designed to govern specific times of the day. The DAY_PLAN plan is to be used during normal business hours, while the OFF_HOURS plan is to be used on nights and weekends.
The enabled plan can be changed at any time by using the ALTER SYSTEM command, as you saw earlier, but it would be very inconvenient to have to always make this change manually. Instead, Oracle's scheduler can be used to schedule the switch so that it is executed automatically based on a specific schedule.
One caveat of scheduling resource plan switches, however, is that you may encounter a situation where you don't want the plans to change.
For instance, if your nightly data loads are larger than normal and might exceed the cutoff time, you may want to delay the switch until the loads have finished. This will ensure that the loads have all the resources necessary to complete.
Rather than having to alter the job in the scheduler, you can simply execute the following statement:
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:OFF_HOURS_PLAN'; System altered.
By adding the prefix FORCE: to the name of the plan, Oracle will restrict the active plan from being changed by the scheduler. The scheduler will still attempt to make the change, but it will fail.
When the nightly loads are finished, the restriction can be lifted by reissuing the identical ALTER SYSTEM statement without the FORCE: prefix, as shown here:
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'OFF_HOURS_PLAN'; System altered.
With the restriction lifted, the resource plan can now be changed manually (or by the scheduler).