(转)Oracle动态性能视图学习札记(2)_v$sesstat_v$mystat_v$statname
原博客地址：http://space.itpub.net/10248702/viewspace-669512
1 Overview
##################################################################
1.1 v$sesstata stores session-specific resource usage statistics, beginning at login and ending at logout.
统计会话级的统计数据。虽然是会话级的，但并不是说只有当前会话，是所有会话都有统计。
1.2 The differences between v$sysstat and sesstat:
1) v$sesstat only stores data for each session, whereas v$sysstat stores the accumulated values for all session.
(系统级与会话级)
2) v$sesstat is transitory, and is lost after a session logs out. v$sysstat is cumulative, and is only lost when the instance is shutdown.
3) v$sesstat does not include the name of the statistic, this view must be joined to either v$sysstat or v$statname.
##################################################################
2 示例
##################################################################
2.1 Finding the Top sessions with Highest Logical and Physical I/O Rates Currently Connected to the database.
查找当前Session消耗IO最多的会话。
SQL> select name, statistic#
2 from v$statname
3 where name in( 'session logical reads', 'physical reads');
NAME STATISTIC#
---------------------------- ----------
session logical reads 9
physical reads 42
SQL> SELECT ses.sid
2 ,DECODE(ses.action,NULL,'online','batch') "User"
3 , MAX(DECODE(sta.statistic#,9,sta.value,0))/greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
4 , MAX(DECODE(sta.statistic#,42,sta.value,0))/greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
5 , 60*24*(sysdate-ses.logon_time) "Minutes"
6 FROM V$SESSION ses , V$SESSTAT sta
7 WHERE ses.status = 'ACTIVE'
8 AND sta.sid = ses.sid
9 AND sta.statistic# IN (9,42)
10 GROUP BY ses.sid, ses.action, ses.logon_time
11 ORDER BY
12 SUM( DECODE(sta.statistic#,42,100*sta.value,sta.value) )/ greatest(3600*24*(sysdate-ses.logon_time),1) DESC;
SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
4 online 0 .035992199 5144.63333
5 online .944926428 .011179935 5144.63333
28 online .793103448 0 .966666667
7 online .601633417 3.2396E-06 5144.63333
2 online 0 .000187898 5144.63333
3 online 0 .000187898 5144.63333
6 online .001755875 3.2396E-06 5144.63333
1 online 0 0 5144.65
8 rows selected
2.2：又例如通过v$sesstat和v$statname连接查询某个SID各项信息。
select a.*,b.name
from v$sesstat a,v$statname b
where a.statistic#=b.statistic#
and a.sid=12 order by a.value desc;
##################################################################
3 v$mystat
##################################################################
This view is a subset of v$sesstat returning current session's statistics. When auditing resource usage for sessions through triggers, use v$mystat to capture
the resource usage, because it is much cheaper than scanning the rows in v$sesstat.
v$mystat是v$sesstat的子集，当通过触发器来捕捉数据时，用v$mystat 成本比v$sesstat更低。
##################################################################
4 v$statname
##################################################################
This view displays decoded statistic names for the statistics shown in the v$sesstat and v$sysstat tables.
On some platforms, the name and class columns contain additional operating system-specific statistics.
SQL> desc v$statname;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
STATISTIC# NUMBER Y
NAME VARCHAR2(64) Y
CLASS NUMBER Y
SQL> select count(name) from v$statname;
COUNT(NAME)
-----------
272
总共有272个statname.
具体每个name的含义参考官方文档<<database reference >>的附录, 要经常翻阅此附录．
其中272个name共分成如下8大类别．
CLASS NUMBER A number representing one or more statistics classes. The following
class numbers are additive:
1 - User
2 - Redo
4 - Enqueue
8 - Cache
16 - OS
32 - Real Application Clusters
64 - SQL
128 - Debug