Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Oracle Database Resource Manager Reference

The following sections provide reference information for Oracle Database Resource Manager (the Resource Manager):

Predefined Resource Plans and Consumer Groups

Table 27-4 lists the resource plans and Table 27-5 lists the resource consumer groups that are predefined in each Oracle database. You can verify these by querying the views DBA_RSRC_PLANS and DBA_RSRC_CONSUMER_GROUPS.

The following query displays the CPU allocations in the example plan DSS_PLAN:

SELECT group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, mgmt_p4 
   FROM dba_rsrc_plan_directives WHERE plan = 'DSS_PLAN';
 
GROUP_OR_SUBPLAN                  MGMT_P1    MGMT_P2    MGMT_P3    MGMT_P4
------------------------------ ---------- ---------- ---------- ----------
SYS_GROUP                              75          0          0          0
DSS_CRITICAL_GROUP                      0         75          0          0
DSS_GROUP                               0          0         75          0
ETL_GROUP                               0          0          0         45
BATCH_GROUP                             0          0          0         45
ORA$DIAGNOSTICS                         0          5          0          0
ORA$AUTOTASK_SUB_PLAN                   0          5          0          0
OTHER_GROUPS                            0          0          0         10

Table 27-4 Predefined Resource Plans

Resource Plan Description

DEFAULT_MAINTENANCE_PLAN

Default plan for maintenance windows. See "About Resource Allocations for Automated Maintenance Tasks" for details of this plan. Because maintenance windows are regular Oracle Scheduler windows, you can change the resource plan associated with them, if desired. If you do change a maintenance window resource plan, ensure that you include the subplan ORA$AUTOTASK_SUB_PLAN and the consumer group ORA$DIAGNOSTICS in the new plan.

DEFAULT_PLAN

Basic default plan that prioritizes SYS_GROUP operations and allocates minimal resources for automated maintenance and diagnostics operations.

DSS_PLAN

Example plan for a data warehouse that prioritizes critical DSS queries over non-critical DSS queries and ETL operations.

ETL_CRITICAL_PLAN

Example plan for a data warehouse that prioritizes ETL operations over DSS queries.

INTERNAL_PLAN

For disabling the resource manager. For internal use only.

INTERNAL_QUIESCE

For quiescing the database. This plan cannot be activated directly. To activate, use the QUIESCE command.

MIXED_WORKLOAD_PLAN

Example plan for a mixed workload that prioritizes interactive operations over batch operations. See "An Oracle-Supplied Mixed Workload Plan" for details.

ORA$AUTOTASK_HIGH_SUB_PLAN

Default sub-plan for high-priority, automated maintenance tasks. This sub-plan is referenced by ORA$AUTOTASK_SUB_PLAN and should not be referenced directly. This plan is a sub-plan of DEFAULT_MAINTENANCE_PLAN.

ORA$AUTOTASK_SUB_PLAN

Default sub-plan for automated maintenance tasks. A directive to this sub-plan should be included in every top-level plan to manage the resources consumed by the automated maintenance tasks. This plan is a sub-plan of DEFAULT_MAINTENANCE_PLAN.


Table 27-5 Predefined Resource Consumer Groups

Resource Consumer Group Description

BATCH_GROUP

Consumer group for batch operations. Referenced by the example plan MIXED_WORKLOAD_PLAN.

DEFAULT_CONSUMER_GROUP

Initial consumer group for all sessions started by user accounts other than SYS and SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules. DEFAULT_CONSUMER_GROUP cannot be named in a resource plan directive.

DSS_CRITICAL_GROUP

Consumer group for critical DSS queries. Referenced by the example plans DSS_PLAN and ETL_CRITICAL_PLAN.

DSS_GROUP

Consumer group for non-critical DSS queries. Referenced by the example plans DSS_PLAN and ETL_CRITICAL_PLAN.

ETL_GROUP

Consumer group for ETL jobs. Referenced by the example plans DSS_PLAN and ETL_CRITICAL_PLAN.

INTERACTIVE_GROUP

Consumer group for interactive, OLTP operations. Referenced by the example plan MIXED_WORKLOAD_PLAN.

LOW_GROUP

Consumer group for low-priority sessions.

ORA$DIAGNOSTICS

Consumer group used by database processes that create diagnostic dumps when critical errors occur.

ORA$AUTOTASK_HEALTH_GROUP

Reserved for future use. Included in ORA$AUTOTASK_HIGH_SUB_PLAN.

ORA$AUTOTASK_MEDIUM_GROUP

Consumer group for medium-priority maintenance tasks.

ORA$AUTOTASK_SPACE_GROUP

Consumer group for Automatic Segment Advisor maintenance task. Included in ORA$AUTOTASK_HIGH_SUB_PLAN.

ORA$AUTOTASK_SQL_GROUP

Consumer group for Automatic SQL Tuning Advisor maintenance task. Included in ORA$AUTOTASK_HIGH_SUB_PLAN.

ORA$AUTOTASK_STATS_GROUP

Consumer group for optimizer statistics gathering maintenance task. Included in ORA$AUTOTASK_HIGH_SUB_PLAN.

ORA$AUTOTASK_URGENT_GROUP

Consumer group for urgent maintenance tasks.

OTHER_GROUPS

Consumer group that applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan, including sessions that belong to DEFAULT_CONSUMER_GROUP. OTHER_GROUPS must have a resource plan directive specified in every plan. It cannot be explicitly assigned to sessions through mapping rules.

SYS_GROUP

Consumer group for system administrators. It is the initial consumer group for all sessions created by user accounts SYS or SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules.


Predefined Consumer Group Mapping Rules

Table 27-6 summarizes the consumer group mapping rules that are predefined in Oracle Database. You can verify these rules by querying the view DBA_RSRC_GROUP_MAPPINGS. You can use the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING procedure to modify or delete any of these mapping rules.

Table 27-6 Predefined Consumer Group Mapping Rules

Attribute Value Mapped Consumer Group Notes

ORACLE_USER

SYS

SYS_GROUP

 

ORACLE_USER

SYSTEM

SYS_GROUP

 

ORACLE_FUNCTION

BACKUP

BATCH_GROUP

The session is running a backup operation with RMAN. The session is automatically switched to BATCH_GROUP when the operation begins.

ORACLE_FUNCTION

COPY

BATCH_GROUP

The session is running a copy operation with RMAN. The session is automatically switched to BATCH_GROUP when the operation begins.

ORACLE_FUNCTION

DATALOAD

ETL_GROUP

The session is performing a data load operation with Data Pump. The session is automatically switched to ETL_GROUP when the operation begins.


Resource Manager Data Dictionary Views

Table 27-7 lists views that are associated with the Resource Manager.

Table 27-7 Resource Manager Data Dictionary Views

View Description

DBA_RSRC_CONSUMER_GROUP_PRIVS

USER_RSRC_CONSUMER_GROUP_PRIVS

DBA view lists all resource consumer groups and the users and roles to which they have been granted. USER view lists all resource consumer groups granted to the user.

DBA_RSRC_CONSUMER_GROUPS

Lists all resource consumer groups that exist in the database.

DBA_RSRC_MANAGER_SYSTEM_PRIVS

USER_RSRC_MANAGER_SYSTEM_PRIVS

DBA view lists all users and roles that have been granted Resource Manager system privileges. USER view lists all the users that are granted system privileges for the DBMS_RESOURCE_MANAGER package.

DBA_RSRC_PLAN_DIRECTIVES

Lists all resource plan directives that exist in the database.

DBA_RSRC_PLANS

Lists all resource plans that exist in the database.

DBA_RSRC_GROUP_MAPPINGS

Lists all of the various mapping pairs for all of the session attributes.

DBA_RSRC_MAPPING_PRIORITY

Lists the current mapping priority of each attribute.

DBA_HIST_RSRC_PLAN

Displays historical information about resource plan activation. This view contains AWR snapshots of V$RSRC_PLAN_HISTORY.

DBA_HIST_RSRC_CONSUMER_GROUP

Displays historical statistical information about consumer groups. This view contains AWR snapshots of V$RSRC_CONS_GROUP_HISTORY.

DBA_USERS

USERS_USERS

DBA view contains information about all users of the database. It contains the initial resource consumer group for each user. USER view contains information about the current user. It contains the current user's initial resource consumer group.

V$ACTIVE_SESS_POOL_MTH

Displays all available active session pool resource allocation methods.

V$PARALLEL_DEGREE_LIMIT_MTH

Displays all available parallel degree limit resource allocation methods.

V$QUEUEING_MTH

Displays all available queuing resource allocation methods.

V$RSRC_CONS_GROUP_HISTORY

For each entry in the view V$RSRC_PLAN_HISTORY, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.

V$RSRC_CONSUMER_GROUP

Displays information about active resource consumer groups. This view can be used for tuning.

V$RSRC_CONSUMER_GROUP_CPU_MTH

Displays all available CPU resource allocation methods for resource consumer groups.

V$RSRCMGRMETRIC

Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past minute.

V$RSRCMGRMETRIC_HISTORY

Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past hour on a minute-by-minute basis. If a new resource plan is enabled, the history is cleared.

V$RSRC_PLAN

Displays the names of all currently active resource plans.

V$RSRC_PLAN_CPU_MTH

Displays all available CPU resource allocation methods for resource plans.

V$RSRC_PLAN_HISTORY

Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.

V$RSRC_SESSION_INFO

Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.

V$SESSION

Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.


See Also:

Oracle Database Reference for detailed information about the contents of each of these views