Oracle動態效能檢視學習筆記(2)_v$sesstat_v$mystat_v$statname

參考文件<>Chapter 24

##################################################################
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的含義參考官方文件<>的附錄, 要經常翻閱此附錄．

其中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