Database Reference
In-Depth Information
NAME VALUE
---------------------- --------
session pga memory 1723880
session pga memory max 2313704
To avoid the restriction based on the
sid
column shown in the preceding query, you can use the
v$mystat
view.
In fact, for the session querying it, it provides exactly the same information as
v$sesstat
. The only difference is that
only statistics about the current session are shown.
■
In most situations, an analysis starts by breaking up the response time into CPu consumption and wait events. If
a session is always on CPu and, therefore, doesn't experience any wait event, session statistics might be very useful to
understand what the session is doing.
Tip
Metrics
Many of the statistics provided by the dynamic performance views described in the previous sections are cumulated
values. Based on them, the database engine computes a number of metrics (depending on the version, between
200 and 300) that are particularly useful for monitoring purposes. The available metrics are listed in the
v$metricname
view. As an example, from version 11.2 onward, there's a metric that shows the database server's CPU usage
per second (which is based on the OS statistics). The following query shows, for that metric, the content of the
v$metricname
view:
SQL> SELECT metric_id, metric_unit, group_id, group_name
2 FROM v$metricname
3 WHERE metric_name = 'Host CPU Usage Per Sec';
METRIC_ID METRIC_UNIT GROUP_ID GROUP_NAME
--------- ----------------------- -------- -----------------------------
2155 CentiSeconds Per Second 2 System Metrics Long Duration
2155 CentiSeconds Per Second 3 System Metrics Short Duration
As you can see from the output of the previous query, a metric has an ID, a unit of measurement, and is
associated to one or several groups (two in the previous case) that have an ID and a name.
Be aware that metrics are computed based on a number of units of measurement. Some of them, like the
previous one, express the utilization or the number of events per second. Others are computed per transaction,
per request, per call, or are averages of absolute values.
A group to which a metric is associated defines the interval of computation and for how long information about
it is provided. If, as in the previous case, a metric is associated to two groups, it means that the database engine
computes two separate metrics, which have a specific interval and retention. Information about the groups is
provided in the
v$metricgroup
view. The following groups exist in version 12.1 (other versions might have a different
number of groups):
SQL> SELECT *
2 FROM v$metricgroup
3 ORDER BY group_id;
