Table Space Management   «Prev  Next»

Lesson 6 Database Resource Manager
Objective Describe the Database Resource Manager.

Database Resource Manager

The Database Resource Manager is a new Oracle feature. The primary purpose of this feature is to allow the DBA to control the operating system resources and internal database resources used by database sessions. Currently, the Database Resource Manager is limited to controlling one system resource (CPU usage) and one internal resource (parallelism of sessions):
  1. CPU usage is controlled by dividing the CPU into a maximum of eight levels. Level 1 gets first priority, so that level 2 only gets resources if level 1 has leftover resources, and so on.
  2. Parallelism of sessions is controlled by specifying the number of concurrent (parallel) sessions that one group of users (called a consumer group ) can run. You can specify a number from zero (single-threaded sessions) to ABSOLUTE (an infinity of concurrent sessions).

Oracle plans to continue developing this feature so that it will ultimately be able to manage other operating system resources, such as I/O traffic, memory usage, and so on. The settings you create for the Resource Manager are stored in the database by running the DBMS_RESOURCE_MANAGER package. The following MouseOver shows the primary components you must understand to use the Database Resource Manager and the DBMS_RESOURCE_MANAGER package.

Oracle Plan
  1. The plan is the top of a hierarchy of plan directives and consumer groups. It is also possible to create subplans. Only one plan can be in effect at one time, but you can define multiple plans in one database and switch between the plans.
  2. Each plan directive contains resource limitation settings. The settings define CPU levels and usage within levels and session parallelism.
  3. A database can have many consumer groups. Each one has a name and is intended to be a group of users with common resource consumption needs. You assign a plan directive to each consumer group, thus limiting the group's resources.
  4. A user can be assigned to one consumer group at a time. When the user is not explicitly assigned to a consumer group, the user is assigned to a default consumer group.
  5. A user's sessions belong to the user, and therefore all the restrictions of the user's consumer group apply to all the user's sessions.

Example of a Resource Management Plan

Database Resource Manager components
Database Resource Manager components


Location 1 The plan is the top of a hierarchy of plan directives and consumer groups. It is also possible to create subplans. Only one plan can be in effect at one time, but you can define multiple plans in one database and switch between the plans.
Location 2 Each plan directive contains resource limitation settings. The settings define CPU levels and usage within levels and session parallelism.
Location 3 A database can have many consumer groups. Each one has a name and is intended to be a group of users with common resource consumption needs. You assign a plan directive to each consumer group, thus limiting the group's resources.
Location 4 A user can be assigned to one consumer group at a time. When the user is not explicitly assigned to a consumer group, the user is assigned to a default consumer group.
Location 5 A user's sessions belong to the user, and therefore all the restrictions of the user's consumer group apply to all the user's sessions.

Set up a Plan Example

Here is an example of how you might set up a plan. Let us say you run an e-commerce Web site that is delivered from your database. Your users are running through a middle tier that logs in as the same user every time. This user needs an infinite number of concurrent sessions. In addition to this user, you have a group of managers who run queries to monitor customer activity. f the current Web activity is low, you wantthe managers to be able to pick up some of the available CPU usage.
The Tooltip below shows what your plan might look like.
Plan Name
  1. Plan name
  2. Plan directives
  3. Consumer groups

Resource Management Plan - Example
As you can imagine, it is possible to create a myriad of possible combinations between plan directives, targeted groups to which the plan is being marketed, and users. Just keep in mind that a simpler plan may be more effectively managed than a complex one.

DB Resource Manager - Exercise

Click the Exercise link below to practice designing your own plan.
DB Resource Manager - Exercise
In the next lesson you will learn how to create a plan in the database.