Database Reference
In-Depth Information
GROUP_ID NAME INTERVAL_SIZE MAX_INTERVAL
---------- -------------------------------- ------------- ------------
0 Event Metrics 6000 1
1 Event Class Metrics 6000 60
2 System Metrics Long Duration 6000 60
3 System Metrics Short Duration 1500 12
4 Session Metrics Long Duration 6000 60
5 Session Metrics Short Duration 1500 1
6 Service Metrics 6000 60
7 File Metrics Long Duration 60000 6
9 Tablespace Metrics Long Duration 6000 0
10 Service Metrics (Short) 500 24
11 I/O Stats by Function Metrics 6000 60
12 Resource Manager Stats 6000 60
13 WCR metrics 6000 60
14 WLM PC Metrics 500 24
The
interval_size
column shows the interval at which the metrics associated to a group are computed in
hundredths of a second. For example, the metrics associated to the
System Metrics Long Duration
group are
computed every 60 seconds. The
max_interval
column shows the maximum number of values that are provided.
For example, up to 60 values are provided for the metrics associated to the
System Metrics Long Duration
group.
As a result, for this group, information for the last hour should be available.
The values of the metrics themselves are provided through a number of views. In fact, there are views that are
specific for some metric groups. For example, for the metric groups 2 and 3, the
v$sysmetric
and
v$sysmetric_history
views show the current value and the history, respectively. Anyway, for simplicity, for most metrics you can also use
the
v$metric
and
v$metric_history
views. To illustrate, the following query shows the current metrics for the
Host
CPU Usage Per Sec
metric (notice that the first metric is computed over 60 seconds and the second metric over
15 seconds):
SQL> SELECT begin_time, end_time, value, metric_unit
2 FROM v$metric
3 WHERE metric_name = 'Host CPU Usage Per Sec';
BEGIN_TIME END_TIME VALUE METRIC_UNIT
------------------- ------------------- ---------- -----------------------
2014-04-28 01:56:00 2014-04-28 01:57:00 168.137173 CentiSeconds Per Second
2014-04-28 01:56:45 2014-04-28 01:57:00 159.786951 CentiSeconds Per Second
Current Sessions Status
Through the
v$session
view, you can not only know what sessions currently exist, but also what they're presently
doing. Because this dynamic performance view has many columns (for example, there are 82 in version 10.2.0.5 and
101 in version 12.1.0.1), they're not fully covered here (refer to the
Oracle Database Reference
manual for additional
information). The most important information you can extract from the
v$session
view and which column it's found
in, is the following:
•
The identification of the session (
sid
,
serial#
,
saddr
and
audsid
), whether it's a
BACKGROUND
or
USER
session (
type
), and when it was initialized (
logon_time
).
The identification of the user that opened the session (
•
username
and
user#
), the current
