| Oracle® Database Administrator's Guide 11g Release 2 (11.2) Part Number E25494-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Database Administrator's Guide 11g Release 2 (11.2) Part Number E25494-02 |
|
|
PDF · Mobi · ePub |
Use the following dynamic performance views to help you monitor the results of your Oracle Database Resource Manager settings:
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.
This view also contains other information, including the following:
The INSTANCE_CAGING column shows whether instance caging is enabled.
The CPU_MANAGED column shows whether CPU is being managed.
The PARALLEL_EXECUTION_MANAGED column shows whether parallel statement queuing is enabled.
See Also:
Oracle Database ReferenceV$RSRC_CONSUMER_GROUP Use the V$RSRC_CONSUMER_GROUP view to monitor resources consumed, including CPU, I/O, and parallel servers. It can also be used to monitor statistics related to CPU resource management, runaway query management, parallel statement queuing, and so on. All of the statistics are cumulative from the time when the plan was activated.
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.
See Also:
Oracle Database ReferenceV$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.
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.
You can join this view with the V$SESSION view for more information about a session.
See Also:
Oracle Database ReferenceV$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.
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.
See Also:
Oracle Database ReferenceV$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. Therefore, you can determine the plan that was active for each row of consumer group statistics.
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.
See Also:
Oracle Database Performance Tuning Guide for information about the AWR.
V$RSRCMGRMETRIC This view enables you to track CPU metrics in milliseconds, in terms of number of sessions, or in terms of utilization for the past one minute. It provides real-time metrics for each consumer group and is very useful in scenarios where you are running workloads and want to continuously monitor CPU resource utilization.
Use this view to compare the maximum possible CPU utilization and average CPU utilization percentage for consumer groups with other consumer group settings such as CPU time used, time waiting for CPU, average number of sessions that are consuming CPU, and number of sessions that are waiting for CPU allocation. For example, you can view the amount of CPU resources a consumer group used and how long it waited for resource allocation. Or, you can view how many sessions from each consumer group are executed against the total number of active sessions.
To track CPU consumption in terms of CPU utilization, use the CPU_UTILIZATION_LIMIT and AVG_CPU_UTILIZATION columns. AVG_CPU_UTILIZATION lists the average percentage of the server's CPU that is consumed by a consumer group. CPU_UTILIZATION_LIMIT represents the maximum percentage of the server's CPU that a consumer group can use. This limit is set using the MAX_UTILIZATION_LIMIT directive attribute.
SELECT consumer_group_name, cpu_utilization_limit, avg_cpu_utilization FROM v$rsrcmgrmetric;
Use the CPU_CONSUMED_TIME and CPU_TIME_WAIT columns to track CPU consumption and throttling in milliseconds. The column NUM_CPUS represents the number of CPUs that Resource Manager is managing.
SELECT consumer_group_name, cpu_consumed_time, cpu_wait_time, num_cpus FROM v$rsrcmgrmetric;
To track the CPU consumption and throttling in terms of number of sessions, use the RUNNING_SESSIONS_LIMIT, AVG_RUNNING_SESSIONS, and AVG_WAITING_SESSIONS columns. RUNNING_SESSIONS_LIMIT lists the maximum number of sessions, from a particular consumer group, that can be running at any time. This limit is defined by the MAX_UTILIZATION_LIMIT directive attribute that you set either for the consumer group or for a subplan that contains the consumer group. For each consumer group, AVG_RUNNING_SESSIONS lists the average number of sessions that are consuming CPU and AVG_WAITING_SESSIONS lists the average number of sessions that are waiting for CPU.
SELECT sequence#, consumer_group_name, running_sessions_limit, avg_running_sessions, avg_waiting_sessions FROM v$rsrcmgrmetric;
See Also:
Oracle Database ReferenceThe columns in the V$RSRCMGRMETRIC_HISTORY are the same view as V$RSRCMGRMETRIC. The only difference between these views is that V$RSRCMGRMETRIC contains metrics for the past one minute only, whereas V$RSRCMGRMETRIC_HISTORY contains metrics for the last 60 minutes.
See Also:
Oracle Database Reference
|
 Copyright © 2001, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|