Oracle Database Resource Manager, Part 3: Conclusion

Synopsis. Oracle Database Resource Management (DRM) provides tools that allow any Oracle DBA to manage a database server's CPU resources effectively for application user groups and during different resource demand periods. This final article builds upon the concepts in the two previous articles, including the construction of more complex resource plans, monitoring DRM utilization, and general DRM maintenance.

The previous article in this series presented some actual examples of how to use the supplied DRM packages to create new resource plans, resource consumer groups and resource directives that bind them together. The concluding article in this series will discuss the creation of more complex DRM plans via DBMS_RESOURCE_MANAGER, how to monitor DRM resource plan utilization, and some remaining tidbits on maintaining DRM plans, directives, and consumer groups.

Creating Complex Resource Plans

The prior article in this series showed how to use two procedures, CREATE_PLAN and CREATE_PLAN_DIRECTIVE, of the DBMS_RESOURCE_MANAGER package to create new plans and plan directives for specific resource allocations. To modify existing plans and directives, I can invoke the UPDATE_PLAN and UPDATE_PLAN_DIRECTIVE procedures, or I can simply drop an existing plan directive and recreate the directive again.

First, I will modify the existing PEAKTIME plan to handle the needs of my user community as set forth in the requirements I defined in the first article of this series. During peak business hours, online transaction processing (OLTP) user sessions must be given precedence over all other types of user sessions. I will also need to insure that decision support (DSS) users have precedence over ad hoc query generation, but when a DSS user session has been actively executing a query for more than 30 minutes, I will make sure that the DSS user is switched to the ADHOC plan instead. Finally, just to make sure that no ADHOC user session runs an abnormally large query, I will limit the maximum estimated execution time for the query to 45 minutes.

I will accomplish all this by updating the plan directives for the PEAKTIME plan directives to new values in Table 1 below:

Table 1. PEAKTIME Resource Plan Directives
Directive	Resource Plan Settings
OLTP	CPU_P1: 75 (75%)
DSS	CPU_P1: 20 (20%) SWITCH_TIME: 1800 (1800 seconds = 30 minutes) SWITCH_GROUP: ADHOC (Switch when SWITCH_TIME is exceeded)
ADHOC	CPU Level 1 allocation: 5% MAX_EST_EXEC_TIME: 2400 (2400 seconds = 45 minutes)

Listing 3.1 shows the code to execute against the database to accomplish this.

Next, I will turn my attention to the OFF-PEAK plan, which is responsible for managing resources during off-peak business hours. I will need to set up plan directives that give any batch processing top priority, followed by DSS user sessions. I will also set up directives that give OLTP and ad hoc query processing relatively equal weights

I will accomplish this by dropping the OFF-PEAK plan and its related directives. Then I will create a new plan, BATCH, and create two plan directives, DSS and OTHER_GROUPS. (Remember, every plan, wherever it is in the plan hierarchy, must have a directive to OTHER_GROUPS as a default.) The new BATCH subplan will allocate 25% of its allocated CPU resources to user sessions in the DSS resource group, and 75% of its resources to any other group.

I will then create a resource plan directive for the BATCH subplan within the OFF-PEAK plan, and I will allocate 80% of all Level 1 CPU resources to that directive. This means that a user session assigned to the DSS resource consumer group under the BATCH subplan would get a maximum of 20% of all CPU resources available (25% of 80% = 20%).

To round out the OFF-PEAK plan, I will allocate a maximum of 15% CPU utilization for any user session assigned to the OLTP resource consumer group, but just 5% to ADHOC user sessions assigned to the ADHOC resource consumer group. Finally, just to keep the ADHOC resource consumer group from doing a lot of unauthorized inserting, updating or deleting, I will limit them to only 4M (4096K) of UNDO pool space.

I will set up the directives with the new values shown in Table 2 below:

Table 2. OFF-PEAK Resource Plan Directives
Plan / Subplan	Group or Subplan	Resource Plan Settings
OFF-PEAK	BATCH	New subplan CPU Level 1 allocation: 80 (80%)
BATCH	OTHER_GROUPS	CPU Level 1 allocation: 0 (0%) CPU Level 2 allocation: 75 (75%
BATCH	DSS	CPU Level 1 allocation: 0 (0%) CPU Level 2 allocation: 25 (25%)
OFF-PEAK	OLTP	CPU Level 1 allocation: 15 (15%)
OFF-PEAK	ADHOC	CPU Level 1 allocation: 5 (5%) Maximum UNDO Pool Usage: 4096 (4096K)
OFF-PEAK	OTHER_GROUPS	CPU Level 1 allocation: 0 (0%) CPU Level 2 allocation: 0 (0%) CPU Level 3 allocation: 100 (0%)

Listing 3.2 shows the code to execute against the database to accomplish this.

Finally, as a busy Oracle DBA, I like to let Oracle handle as much of the workload as possible, so I'll schedule two database jobs via the DBMS_JOB package so that the database can switch itself to off-peak processing mode at 18:00 every night, and switch itself back to peak processing mode at 06:00 every morning. Listing 3.3 shows an example of how to schedule the switchover and switchback of each resource plan via DBMS_JOB.