Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 18, 2004

Oracle Database Resource Manager, Part 3: Conclusion

By Jim Czuprynski

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


Resource Plan Settings


CPU_P1: 75 (75%)


CPU_P1: 20 (20%)

SWITCH_TIME: 1800 (1800 seconds = 30 minutes)

SWITCH_GROUP: ADHOC (Switch when SWITCH_TIME is exceeded)


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



New subplan

CPU Level 1 allocation: 80 (80%)



CPU Level 1 allocation: 0 (0%)

CPU Level 2 allocation: 75 (75%



CPU Level 1 allocation: 0 (0%)

CPU Level 2 allocation: 25 (25%)



CPU Level 1 allocation: 15 (15%)



CPU Level 1 allocation: 5 (5%)

Maximum UNDO Pool Usage: 4096 (4096K)



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.

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM