v$session,v$process,v$mystat,v$statname剖析（还算不错）

v$session,v$process,v$mystat,v$statname剖析

2012-09-27 14:39:52

分类： Oracle

我们经常要想获得跟踪文件的名称，而跟踪文件的名称包含了当前session的id,所以获得了当前session的id，也就可以找到当前跟踪文件的名称。而为了获得当前session的id,一般我们执行下面的SQL：
SQL> select spid from v$session s, v$process p, v$mystat m
        where s.paddr=p.addr and s.sid=m.sid and m.statistic#=0;
SPID
------------
26156
或者：
SQL> select spid from v$session s inner join v$process p on s.paddr=p.addr
         inner join v$mystat m on s.sid=m.sid and m.statistic#=0;
SPID
------------
26156
或者：
SQL> select spid from v$process where addr in (select paddr from v$session where
         sid=(select sid from v$mystat where rownum=1)); 
SPID
------------
26156

1)V$PROCESS
This view contains information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.

Column	Datatype	Description
ADDR	RAW(4 | 8)	Address of process state object
PID	NUMBER	Oracle process identifier
SPID	VARCHAR2(12)	Operating system process identifier
USERNAME	VARCHAR2(15)	Operating system process username. Any two-task user coming across the network has "-T" appended to the username.
SERIAL#	NUMBER	Process serial number
TERMINAL	VARCHAR2(30)	Operating system terminal identifier
PROGRAM	VARCHAR2(48)	Program in progress
TRACEID	VARCHAR2(255)	Trace file identifier
BACKGROUND	VARCHAR2(1)	1 for a background process; NULL for a normal process
LATCHWAIT	VARCHAR2(8)	Address of latch the process is waiting for; NULL if none
LATCHSPIN	VARCHAR2(8)	Address of the latch the process is spinning on; NULL if none
PGA_USED_MEM	NUMBER	PGA memory currently used by the process
PGA_ALLOC_MEM	NUMBER	PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)
PGA_FREEABLE_MEM	NUMBER	Allocated PGA memory which can be freed
PGA_MAX_MEM	NUMBER	Maximum PGA memory ever allocated by the process

2)V$MYSTAT
This view contains statistics on the current session.

Column	Datatype	Description
SID	NUMBER	ID of the current session
STATISTIC#	NUMBER	Number of the statistic
VALUE	NUMBER	Value of the statistic

v$mystat 是当前用户 session 的各种统计信息，sid就是session的id, STATISTIC# 是统计量的编号(用来唯一确定统计量的名称)，value是统计量的值。v$mystat 中的 STATISTIC# 序号对应的含义可以从 v$statname 查询出来，STATISTIC#相当于编号，NAME就是统计量的名称。

3)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.

Column	Datatype	Description
STATISTIC#	NUMBER	Statistic number Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications.
NAME	VARCHAR2(64)	Statistic name
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
STAT_ID	NUMBER	Identifier of the statistic

SQL> column name for a35;
SQL> select * from v$statname where rownum<20;
STATISTIC# NAME                                     CLASS    STAT_ID
---------- ----------------------------------- ---------- ----------
         0 logons cumulative                            1 2666645286
         1 logons current                               1 3080465522
         2 opened cursors cumulative                    1   85052502
         3 opened cursors current                       1 2301954928
         4 user commits                                 1  582481098
         5 user rollbacks                               1 3671147913
         6 user calls                                   1 2882015696
         7 recursive calls                              1 2656001462
         8 recursive cpu usage                          1 4009879262
         9 session logical reads                        1 3143187968
        10 session stored procedure space               1  895486831
        11 CPU used when call started                 128  572264820
        12 CPU used by this session                     1   24469293
        13 DB time                                      1 3649082374
        14 cluster wait time                            1 2432034337
        15 concurrency wait time                        1 3868577743
        16 application wait time                        1 1099569955
        17 user I/O wait time                           1 3332107451
        18 session connect time                         1 1678473874

19 rows selected.
从上面的查询可以看出STATISTIC# =0 的含义是自实例启动以来用户登录数据库次数的累计值。其实在查询
select spid from v$session s inner join v$process p on s.paddr=p.addr
inner join v$mystat m on s.sid=m.sid and m.statistic#=0; 中m.statistic#=1,2,3都可以，
只不过是为了得到唯一的 m.sid 而言。
另外：
通过连接查询v$mystat和v$statname就可以得到当前session的各种信息：
SQL> select m.*,n.name,n.class from v$mystat m inner join v$statname n on
        m.statistic#=n.statistic# where rownum<20;
       SID STATISTIC#      VALUE NAME                                     CLASS
---------- ---------- ---------- ----------------------------------- ----------
       147          0          1 logons cumulative                            1
       147          1          1 logons current                               1
       147          2        353 opened cursors cumulative                    1
       147          3         10 opened cursors current                       1
       147          4          0 user commits                                 1
       147          5          0 user rollbacks                               1
       147          6        389 user calls                                   1
       147          7       7155 recursive calls                              1
       147          8        152 recursive cpu usage                          1
       147          9       3818 session logical reads                        1
       147         10          0 session stored procedure space               1
       147         11        196 CPU used when call started                 128
       147         12        197 CPU used by this session                     1
       147         13        463 DB time                                      1
       147         14          0 cluster wait time                            1
       147         15          0 concurrency wait time                        1
       147         16          0 application wait time                        1
       147         17         71 user I/O wait time                           1
       147         18          0 session connect time                         1

19 rows selected.

4)V$SESSION
SQL> select count(column_name) from dba_tab_columns where table_name='V_$SESSION';
COUNT(COLUMN_NAME)
------------------
                76
我们看到v$session视图有76列，十分的复杂。我们稍微看一下比较重要的列。

This view lists session information for each current session.

Column	Datatype	Description
SADDR	RAW(4 | 8)	Session address
SID	NUMBER	Session identifier
PADDR	RAW(4 | 8)	Address of the process that owns the session
USER#	NUMBER	Oracle user identifier
USERNAME	VARCHAR2(30)	Oracle username
TADDR	VARCHAR2(8)	Address of transaction state object
LOCKWAIT	VARCHAR2(8)	Address of lock waiting for; null if none ......
STATUS	VARCHAR2(8)	Status of the session: ACTIVE - Session currently executing SQL INACTIVE KILLED - Session marked to be killed CACHED - Session temporarily cached for use by Oracle*XA SNIPED - Session inactive, waiting on the client
SERVER	VARCHAR2(9)	Server type (DEDICATED| SHARED| PSEUDO| NONE)
SCHEMA#	NUMBER	Schema user identifier
SCHEMANAME	VARCHAR2(30)	Schema user name
OSUSER	VARCHAR2(30)	Operating system client user name
PROCESS	VARCHAR2(12)	Operating system client process ID
MACHINE	VARCHAR2(64)	Operating system machine name
TERMINAL	VARCHAR2(30)	Operating system terminal name
PROGRAM	VARCHAR2(48)	Operating system program name
TYPE	VARCHAR2(10)	Session type
SQL_ADDRESS	RAW(4 | 8)	Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
SQL_HASH_VALUE	NUMBER	Used with SQL_ADDRESS to identify the SQL statement that is currently being executed
SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement that is currently being executed
SQL_CHILD_NUMBER	NUMBER	Child number of the SQL statement that is currently being executed ......
WAIT_CLASS_ID	NUMBER	Identifier of the wait class
WAIT_CLASS#	NUMBER	Number of the wait class
WAIT_CLASS	VARCHAR2(64)	Name of the wait class
WAIT_TIME	NUMBER	A nonzero value is the session's last wait time. A zero value means the session is currently waiting.
SECONDS_IN_WAIT	NUMBER
STATE	VARCHAR2(19)	Wait state: 0 - WAITING (the session is currently waiting) -2 - WAITED UNKNOWN TIME (duration of last wait is unknown) -1 - WAITED SHORT TIME (last wait <1/100th of a second) >0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
SERVICE_NAME	VARCHAR2(64)	Service name of the session
SQL_TRACE	VARCHAR2(8)	Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)
SQL_TRACE_WAITS	VARCHAR2(5)	Indicates whether wait tracing is enabled (TRUE) or not (FALSE)
SQL_TRACE_BINDS	VARCHAR2(5)	Indicates whether bind tracing is enabled (TRUE) or not (FALSE)