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.

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles