Database Reference
In-Depth Information
System and Session Statistics
In addition to time model statistics and wait events, the database engine also records hundreds (more than 850 in
version 12.1) of additional statistics providing information like the number of times a specific operation has been
performed or the amount of data processed by a specific functionality. These statistics are externalized at the system
level and for all connected sessions through the
v$sysstat
and
v$sesstat
views, respectively. In addition, in a 12.1
multitenant environment, the
v$con_sysstat
view shows statistics at the container level.
There are two key columns in the
v$sysstat
view:
name
identifies the statistic (refer to the
Oracle Database Reference
manual for a short
description of most of them), and
•
value
provides the figure associated to the statistic itself. In most cases, the value is cumulated
since the database instance startup, but this isn't the case for all statistics.
•
To show you examples of the kind of information you can retrieve through a dynamic performance view like
v$sysstat
, let's have a look at two queries. The first one retrieves statistics based on counters that are constantly
incremented. In this case, those counters represent the number of logons, commits, and in-memory sorts since the
database instance startup:
SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name IN ('logons cumulative', 'user commits', 'sorts (memory)');
NAME VALUE
----------------- --------
logons cumulative 1422
user commits 1298103
sorts (memory) 770169
The second query retrieves statistics showing the amount of data processed through disk I/O operations:
SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name LIKE 'physical % total bytes';
NAME VALUE
-------------------------- -----------
physical read total bytes 9.1924E+10
physical write total bytes 4.2358E+10
The structure of the
v$con_sysstat
view is exactly the same as that of
v$sysstat
. However, the
v$sesstat
view is
a bit different. Although it does provide a column (
sid
) that's used to identify the session that the statistics belong to,
it doesn't provide the
name
column. To get the name of the statistic, it's necessary to join
v$sesstat
with another view
that provides a list of all available statistics:
v$statname
. The following query illustrates how to use these two views
for retrieving information about the PGA memory utilization for the current session (two values are returned—the
amount that is currently allocated and the maximum amount allocated since the session was initialized):
SQL> SELECT sn.name, ss.value
2 FROM v$statname sn, v$sesstat ss
3 WHERE sn.statistic# = ss.statistic#
