Notes

Outline

Managing a Mixed Workload

Objectives After completing this lesson, you should be able to do the following: List the features of Database Resource Manager Limit the use of resources using Database Resource Manager

Overview Manage a mixed workload Control processing resources used

Database Resource Management Concepts

Resource Consumer Groups Users can be members of multiple consumer groups. A default group is assigned to a user at login. Only one group is active at a time for a session. Either the user or DBA can switch the consumer group during a session. Groups are created with the database.

Resource Plan Directives

Database Resource Management Example

Steps in Database Resource Management 1. Assign the resource manager system privileges to the administrator. 2. Create resource objects with the package DBMS_RESOURCE_MANAGER: Resource consumer groups Resource plans Resource plan directives 3. Assign users to groups with the package DBMS_RESOURCE_MANAGER_PRIVS. 4. Specify the plan to be used by the instance.

Assigning the Resource Manager Privilege 1. Assign the resource manager system privileges to the administrator.

Creating Database Resource Manager Objects 2. Create resource objects with the package DBMS_RESOURCE_MANAGER. 2.1. Create a pending area. 2.2. Create resource consumer groups.

Creating Database Resource Manager Objects 2.3 Create resource plans.

Creating Database Resource Manager Objects 2.5. Validate the pending area. 2.6. Commit the pending area.

Assigning Users to Consumer Groups 3. Assign users to groups. Set the initial consumer group for users

Setting the Resource Plan for an Instance 4. Specify the plan to be used by the instance. Specify the RESOURCE_MANAGER_PLAN initialization parameter. Change the resource plan without shutting down and restarting the instance.

Changing a Consumer Group Within a Session The user or the application can switch the current consumer group.

Changing Consumer Groups for Sessions Can be set by DBA for a session Can be set by DBA for all sessions for a user

Database Resource Manager Information DBA_RSRC_PLANS Resource plans and status DBA_RSRC_PLAN_DIRECTIVES Resource plan directives and status DBA_RSRC_CONSUMER_GROUPS Consumer groups and status DBA_RSRC_CONSUMER_GROUP_PRIVS Users granted consumer groups DBA_USERS Column: INITIAL_RSRC_CONSUMER_GROUP

Current Database Resource Manager Settings V$SESSION: Contains the  RESOURCE_CONSUMER_GROUP column that shows the current group for a session V$RSRC_PLAN: A view that show the active resource plan V$RSRC_CONSUMER_GROUP: A view that contains statistics by consumer group

Summary In this lesson, you should have learned how to control the use of CPUs and the degree of parallelism using Database Resource Manager.