Table Space Management   «Prev  Next»

Oracle Database Resource Manager - Exercise

Course Project: Design a Resource Management Plan

Objective: Given a scenario, design a plan for the Resource Manager.

Exercise Scoring

This exercise is worth a maximum of 10 points.

Background/overview

You will see a scenario similar to the one described in the example in this lesson.
Your job is to write a resource management plan and submit the exercise.

Instructions

Read the scenario below. Design a plan for the Resource Manager that includes these items:
  1. Plan name
  2. Plan directives. Include the name, amount of parallelism, and CPU usage for up to eight levels.
  3. Targeted groups. Include the name of the plan directive assigned to the group of consumers or users at which the plan is aimed. Also describe the type of users assigned to the group.

Scenario

You are the DBA for the House-O-Pets Web store. Three different groups of users retrieve data from your Oracle database as they use the Web store. The first group is window-shoppers who only view general information. Everyone in this category gets automatically logged in with the same username. The second type of user is the potential customer who is loading a shopping cart but has not completed a purchase. All these users also get logged in with a single Oracle username, but they get higher priority for CPU usage than the window-shoppers do. The third type of user is the buyer: someone who has initiated a sale by clicking the Buy button. Each of these users is assigned a unique username and receives top priority for CPU usage. After a user has made a purchase, he or she can log in to the Web site and receive the same top priority status as before. You cannot allow this kind of user to run two sessions at once (no parallelism is allowed).

Hints

Follow the example in the lesson for a good idea of how to organize your plan.

Submitting your exercise

Prepare your answer on your own text editor, then cut and paste the entire answer into the box below.
Click Submit to submit your exercise.
Here is a possible plan:
  1. Plan name: EPETS_PLAN
  2. Plan directive 1: TOP_PRIORITY; unlimited parallel sessions at a time; 60 percent of CPU at level 1.
  3. Plan directive 2: MEDIUM_PRIORITY; unlimited parallel sessions at a time; 30 percent of CPU at level 1, 20 percent of CPU at level 2.
  4. Plan directive 3: LOW_PRIORITY; four parallel sessions at a time; 10 percent of CPU at level 1, 10 percent of CPU at level 2.
  5. Consumer group 1: LOOKER; uses the LOW_PRIORITY plan directive; assigned to the user that view general information on the Web site.
  6. Consumer group 2: SHOPPER; uses the MEDIUM_PRIORITY plan directive; assigned to the username that runs the shopping cart for potential buyers.
  7. Consumer group 3: BUYER; uses the TOP_PRIORITY plan directive; assigned to all users who are purchasing or have purchased in the past.