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
Predefined Consumer Group Mapping Rules
Resource Manager Data Dictionary Views

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.

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`.
`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`	Default consumer group for all sessions that do not have an explicit initial consumer group, are not mapped to a consumer group with session-to–consumer group mapping rules, or are mapped to a consumer group that is not in the currently active resource plan. `OTHER_GROUPS` must have a resource plan directive specified in every plan. It cannot be assigned explicitly 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$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$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_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.