(转)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

?