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. |