Monitoring Oracle Database Resource Manager

Use the following dynamic performance views to help you monitor the results of your Oracle Database Resource Manager settings:

V$RSRC_PLAN
V$RSRC_CONSUMER_GROUP
V$RSRC_SESSION_INFO
V$RSRC_PLAN_HISTORY
V$RSRC_CONS_GROUP_HISTORY

These views provide:

Current status information
History of resource plan activations
Current and historical statistics on resource consumption and CPU waits by both resource consumer group and session

In addition, historical statistics are available through the DBA_HIST_RSRC_PLAN and DBA_HIST_RSRC_CONSUMER_GROUP views, which contain Automatic Workload Repository (AWR) snapshots of the V$RSRC_PLAN_HISTORY and V$RSRC_CONS_GROUP_HISTORY, respectively.

For assistance with tuning, the views V$RSRCMGRMETRIC and V$RSRCMGRMETRIC_HISTORY show how much time was spent waiting for CPU and how much CPU was consumed per minute for every consumer group for the past hour. These metrics can be viewed graphically with Enterprise Manager, on the Resource Manager Statistics page.

V$RSRC_PLAN This view displays the currently active resource plan and its subplans.

SELECT name, is_top_plan FROM v$rsrc_plan;
 
NAME                             IS_TOP_PLAN
-------------------------------- -----------
DEFAULT_PLAN                     TRUE
ORA$AUTOTASK_SUB_PLAN            FALSE
ORA$AUTOTASK_HIGH_SUB_PLAN       FALSE

The plan for which IS_TOP_PLAN is TRUE is the currently active (top) plan, and the other plans are subplans of either the top plan or of other subplans in the list.

V$RSRC_CONSUMER_GROUP

Use the

V$RSRC_CONSUMER_GROUP view to monitor CPU usage and CPU waits. It provides the cumulative amount of CPU time consumed, cumulative amount of time waiting for CPU, and cumulative number of CPU waits by all sessions in each consumer group. It also provides a number of other measures helpful for tuning.

SQL> SELECT name, active_sessions, queue_length,
  consumed_cpu_time, cpu_waits, cpu_wait_time
  FROM v$rsrc_consumer_group;

NAME               ACTIVE_SESSIONS QUEUE_LENGTH CONSUMED_CPU_TIME  CPU_WAITS CPU_WAIT_TIME
------------------ --------------- ------------ ----------------- ---------- -------------
OLTP_ORDER_ENTRY                 1            0             29690        467          6709
OTHER_GROUPS                     0            0           5982366       4089         60425
SYS_GROUP                        1            0           2420704        914         19540
DSS_QUERIES                      4            2           4594660       3004         55700

In the preceding query results, the DSS_ QUERIES consumer group has four sessions in its active session pool and two more sessions queued for activation.

A key measure in this view is CPU_WAIT_TIME. This indicates the total time that sessions in the consumer group waited for CPU because of resource management. Not included in this measure are waits due to latch or enqueue contention, I/O waits, and so on.

V$RSRC_SESSION_INFO Use this view to monitor the status of one or more sessions. The view shows how the session has been affected by the Resource Manager. It provides information such as:

The consumer group that the session currently belongs to.
The consumer group that the session originally belonged to.
The session attribute that was used to map the session to the consumer group.
Session state (RUNNING, WAIT_FOR_CPU, QUEUED, and so on).
Current and cumulative statistics for metrics, such as CPU consumed, wait times, and queued time. Current statistics reflect statistics for the session since it joined its current consumer group. Cumulative statistics reflect statistics for the session in all consumer groups to which it has belonged since it was created.

SQL> SELECT se.sid sess_id, co.name consumer_group, 
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

SESS_ID CONSUMER_GROUP     STATE     CPU_TIME CPU_WAIT_TIME QUEUED_TIME
------- ------------------ -------- --------- ------------- -----------
    113 OLTP_ORDER_ENTRY   WAITING     137947         28846           0
    135 OTHER_GROUPS       IDLE        785669         11126           0
    124 OTHER_GROUPS       WAITING      50401         14326           0
    114 SYS_GROUP          RUNNING        495             0           0
    102 SYS_GROUP          IDLE         88054            80           0
    147 DSS_QUERIES        WAITING     460910        512154           0

CPU_WAIT_TIME in this view has the same meaning as in the V$RSRC_CONSUMER_GROUP view, but applied to an individual session.

V$RSRC_PLAN_HISTORY

This view shows when resource plans were enabled or disabled on the instance. Each resource plan activation or deactivation is assigned a sequence number. For each entry in the view, the

V$RSRC_CONS_GROUP_HISTORY view has a corresponding entry for each consumer group in the plan that shows the cumulative statistics for the consumer group. The two views are joined by the SEQUENCE# column in each.

SQL> SELECT sequence# seq, name plan_name,
to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
FROM v$rsrc_plan_history;

 SEQ PLAN_NAME                  START_TIME      END_TIME        WINDOW_NAME
---- -------------------------- --------------- --------------- ----------------
   1                            29-MAY-07 23:05 29-MAY-07 23:05
   2 DEFAULT_MAINTENANCE_PLAN   29-MAY-07 23:05 30-MAY-07 02:05 TUESDAY_WINDOW
   3                            30-MAY-07 02:05 30-MAY-07 22:05
   4 DEFAULT_MAINTENANCE_PLAN   30-MAY-07 22:05 31-MAY-07 02:05 WEDNESDAY_WINDOW
   5                            31-MAY-07 02:05 31-MAY-07 22:05
   6 DEFAULT_MAINTENANCE_PLAN   31-MAY-07 22:05                 THURSDAY_WINDOW

A null value under PLAN_NAME indicates that no plan was active.

AWR snapshots of this view are stored in the DBA_HIST_RSRC_PLAN view.

V$RSRC_CONS_GROUP_HISTORY

This view helps you understand how resources were shared among the consumer groups over time. The

sequence# column corresponds to the column of the same name in the V$RSRC_PLAN_HISTORY view. This enables you to determine the plan that was active for each row of consumer group statistics.

SQL> select sequence# seq, name, cpu_wait_time, cpu_waits,
consumed_cpu_time from V$RSRC_CONS_GROUP_HISTORY;
 
 SEQ NAME                      CPU_WAIT_TIME  CPU_WAITS CONSUMED_CPU_TIME
---- ------------------------- ------------- ---------- -----------------
   2 SYS_GROUP                         18133        691          33364431
   2 OTHER_GROUPS                      51252        825         181058333
   2 ORA$AUTOTASK_MEDIUM_GROUP            21          5           4019709
   2 ORA$AUTOTASK_URGENT_GROUP            35          1            198760
   2 ORA$AUTOTASK_STATS_GROUP              0          0                 0
   2 ORA$AUTOTASK_SPACE_GROUP              0          0                 0
   2 ORA$AUTOTASK_SQL_GROUP                0          0                 0
   2 ORA$AUTOTASK_HEALTH_GROUP             0          0                 0
   2 ORA$DIAGNOSTICS                       0          0           1072678
   4 SYS_GROUP                         40344         85          42519265
   4 OTHER_GROUPS                     123295       1040         371481422
   4 ORA$AUTOTASK_MEDIUM_GROUP             1          4           7433002
   4 ORA$AUTOTASK_URGENT_GROUP         22959        158          19964703
   4 ORA$AUTOTASK_STATS_GROUP              0          0                 0
      .
      .
   6 ORA$DIAGNOSTICS                       0          0                 0

AWR snapshots of this view are stored in the DBA_HIST_RSRC_CONSUMER_GROUP view. Use DBA_HIST_RSRC_CONSUMER_GROUP with DBA_HIST_RSRC_PLAN to determine the plan that was active for each historical set of consumer group statistics.