oracle10g R2 v$session视图详解

引言： 从10g开始，伴随ASH功能的引入，对V$SESSION视图进行了彻底改造。

        从Oracle10gR1开始，Oracle在V$SESSION中增加关于等待事件的字段，实际上也就是把原来V$SESSION_WAIT视图中的所有字段全部整合到了V$SESSION视图中，开始的时候我还以为ASH是依赖联合查询来获取信息的，仔细一看才发现现在V$SESSION已经发生了变化。(如果进一步研究你会发现，实际上V$SESSION的底层查询语句及X$表已经有了变化)

　　这一变化使得我们的查询得以简化，但是也使得V$SESSION_WAIT开始变得多余，此外V$SESSION中还增加了BLOCKING_SESSION等字段，以前我们需要通dba_waiters等视图才能获得的信息，现在也可以直接从V$SESSION中得到了。既然这样，让我们好好研究V$SESSION视图中每个字段的含义。

        首先，请看官方文档上面关于V$SESSION字段的说明：

        v$session：This view has one row for every session connected to the database instance. The sessions include user sessions, as well as background processes like DBWR, LGWR, archiver.

        下面为每个字段的详细说明。

Column	Datatype	Description
SADDR	RAW(4 | 8)	Session address
SID	NUMBER	Session identifier
SERIAL#	NUMBER	Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.
AUDSID	NUMBER	Auditing session ID
PADDR	RAW(4 | 8)	Address of the process that owns the session
USER#	NUMBER	Oracle user identifier
USERNAME	VARCHAR2(30)	Oracle username
COMMAND	NUMBER	Command in progress (last statement parsed); for a list of values, see Table 7-5. These values also appear in the AUDIT_ACTIONS table.
OWNERID	NUMBER	The column contents are invalid if the value is 2147483644. Otherwise, this column contains the identifier of the user who owns the migratable session. For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator.
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
PREV_SQL_ADDR	RAW(4 | 8)	Used with PREV_HASH_VALUE to identify the last SQL statement executed
PREV_HASH_VALUE	NUMBER	Used with SQL_HASH_VALUE to identify the last SQL statement executed
PREV_SQL_ID	VARCHAR2(13)	SQL identifier of the last SQL statement executed
PREV_CHILD_NUMBER	NUMBER	Child number of the last SQL statement executed
MODULE	VARCHAR2(48)	Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
MODULE_HASH	NUMBER	Hash value of the above MODULE
ACTION	VARCHAR2(32)	Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure
ACTION_HASH	NUMBER	Hash value of the above action name
CLIENT_INFO	VARCHAR2(64)	Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
FIXED_TABLE_SEQUENCE	NUMBER	This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.
ROW_WAIT_OBJ#	NUMBER	Object ID for the table containing the row specified in ROW_WAIT_ROW#
ROW_WAIT_FILE#	NUMBER	Identifier for the datafile containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not-1.
ROW_WAIT_BLOCK#	NUMBER	Identifier for the block containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not-1.
ROW_WAIT_ROW#	NUMBER	Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not-1.
LOGON_TIME	DATE	Time of logon
LAST_CALL_ET	NUMBER	If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active. If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.
PDML_ENABLED	VARCHAR2(3)	This column has been replaced by column PDML_STATUS
FAILOVER_TYPE	VARCHAR2(13)	Indicates whether and to what extent transparent application failover (TAF) is enabled for the session: ·         NONE - Failover is disabled for this session ·         SESSION - Client is able to fail over its session following a disconnect ·         SELECT - Client is able to fail over queries in progress as well See Also: ·         Oracle Database Concepts for more information on TAF ·         Oracle Database Net Services Administrator's Guide for information on configuring TAF
FAILOVER_METHOD	VARCHAR2(10)	Indicates the transparent application failover method for the session: ·         NONE - Failover is disabled for this session ·         BASIC - Client itself reconnects following a disconnect ·         PRECONNECT - Backup instance can support all connections from every instance for which it is backed up
FAILED_OVER	VARCHAR2(3)	Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO)
RESOURCE_CONSUMER_GROUP	VARCHAR2(32)	Name of the session's current resource consumer group
PDML_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML.
PDDL_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL.
PQ_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL QUERY enabled mode. If DISABLED, PARALLEL QUERY enabled mode is not supported for the session. IfFORCED, the session has been altered to force PARALLEL QUERY.
CURRENT_QUEUE_DURATION	NUMBER	If queued (1), the current amount of time the session has been queued. If not currently queued, the value is0.
CLIENT_IDENTIFIER	VARCHAR2(64)	Client identifier of the session
BLOCKING_SESSION_STATUS	VARCHAR2(11)	Blocking session status: ·         VALID ·         NO HOLDER ·         GLOBAL ·         NOT IN WAIT ·         UNKNOWN
BLOCKING_INSTANCE	NUMBER	Instance identifier of blocking session
BLOCKING_SESSION	NUMBER	Session identifier of blocking session
SEQ#	NUMBER	Sequence number that uniquely identifies the wait. Incremented for each wait.
EVENT#	NUMBER	Event number
EVENT	VARCHAR2(64)	Resource or event for which the session is waiting See Also: Appendix C, "Oracle Wait Events"
P1TEXT	VARCHAR2(64)	Description of the first additional parameter
P1	NUMBER	First additional parameter
P1RAW	RAW(4)	First additional parameter
P2TEXT	VARCHAR2(64)	Description of the second additional parameter
P2	NUMBER	Second additional parameter
P2RAW	RAW(4)	Second additional parameter
P3TEXT	VARCHAR2(64)	Description of the third additional parameter
P3	NUMBER	Third additional parameter
P3RAW	RAW(4)	Third additional parameter
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	If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. IfWAIT_TIME >0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, andSECONDS_IN_WAIT -WAIT_TIME / 100 is the active seconds since the last wait ended.
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)

1、SADDR  --Session address Session地址

2、SID --Session identifier Session ID

3、SERIAL#
      官方解释：Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.
      sid 会重用，但是同一个SID被重用时，serial#会增加，不会重复。
      比如说你在10:00时发现有一个SID 为10 ,serial#为100的session 不正常，想杀掉他，要是直接用kill sid 10 ，而同时这个session 主动退出，新session近来 而又正好用了 10这个SID （这时新session的serial#不会=100，只会比100高）,就会发生误杀的情况。所以Oracle要求我们在杀session时，必须同时指定sid和serial#.
从另外一个角度上说，sid 在同一个instance的当前session中是一个unique key, 而sid ,serial#则是在整个instance生命期内的所有session中是unique key。（不考虑serial#超过最大值，重用的情况）

3、AUDSID --Auditing session ID

      from asktom
      the audsid column is populated via a sequence and for normal
       sessions is the same as "userenv('sessionid')" -- but for some background sessions -- it is not set
       (it comes back as "0" making the view not work.
        So, I'd just ignore "audsid" for now and use SID.

4、paddr ，这个我们要和saddr、taddr一起讲一下

      saddr(session address):表示当前记录的内存地址;
      paddr(process address):该session对应的进程地址,关联v$process的addr字段,可以通过这个字段查处当前session对应操作系统的那个进程的id;
      taddr(transation address):当前有活动事务的地址,关联v$transaction表的addr,通过这个字段关联查出当前session正在使用的回滚段的情况,以及当前事务的大小等信息

5、USER#、USERNAME 表示登录的用户名编号和用户名，比如说sytem、test等，如果没有说明是后台进程产生的session。

      比如说：QMNC、MMON 、MMNL 等后台经常，这是时候我们可以看看v$session 的PROGRAM字段。

     

SQL> select PROGRAM from v$session where username is null;

PROGRAM
------------------------------------------------
oracle@TEST-8-40 (q001)
oracle@TEST-8-40 (q000)
oracle@TEST-8-40 (QMNC)
oracle@TEST-8-40 (MMNL)
oracle@TEST-8-40 (MMON)
oracle@TEST-8-40 (CJQ0)
oracle@TEST-8-40 (RECO)
oracle@TEST-8-40 (SMON)
oracle@TEST-8-40 (CKPT)
oracle@TEST-8-40 (LGWR)
oracle@TEST-8-40 (DBW0)

PROGRAM
------------------------------------------------
oracle@TEST-8-40 (MMAN)
oracle@TEST-8-40 (PSP0)
oracle@TEST-8-40 (PMON)

14 rows selected.

6、COMMAND  Command in progress (last statement parsed); for a list of values, seeTable 7-5. These values also appear in the AUDIT_ACTIONS table.

     

COMMAND Column of V$SESSION and Corresponding Commands

Number	Command	Number	Command
1	CREATE TABLE	2	INSERT
3	SELECT	4	CREATE CLUSTER
5	ALTER CLUSTER	6	UPDATE
7	DELETE	8	DROP CLUSTER
9	CREATE INDEX	10	DROP INDEX
11	ALTER INDEX	12	DROP TABLE
13	CREATE SEQUENCE	14	ALTER SEQUENCE
15	ALTER TABLE	16	DROP SEQUENCE
17	GRANT OBJECT	18	REVOKE OBJECT
19	CREATE SYNONYM	20	DROP SYNONYM
21	CREATE VIEW	22	DROP VIEW
23	VALIDATE INDEX	24	CREATE PROCEDURE
25	ALTER PROCEDURE	26	LOCK
27	NO-OP	28	RENAME
29	COMMENT	30	AUDIT OBJECT
31	NOAUDIT OBJECT	32	CREATE DATABASE LINK
33	DROP DATABASE LINK	34	CREATE DATABASE
35	ALTER DATABASE	36	CREATE ROLLBACK SEG
37	ALTER ROLLBACK SEG	38	DROP ROLLBACK SEG
39	CREATE TABLESPACE	40	ALTER TABLESPACE
41	DROP TABLESPACE	42	ALTER SESSION
43	ALTER USER	44	COMMIT
45	ROLLBACK	46	SAVEPOINT
47	PL/SQL EXECUTE	48	SET TRANSACTION
49	ALTER SYSTEM	50	EXPLAIN
51	CREATE USER	52	CREATE ROLE
53	DROP USER	54	DROP ROLE
55	SET ROLE	56	CREATE SCHEMA
57	CREATE CONTROL FILE	59	CREATE TRIGGER
60	ALTER TRIGGER	61	DROP TRIGGER
62	ANALYZE TABLE	63	ANALYZE INDEX
64	ANALYZE CLUSTER	65	CREATE PROFILE
66	DROP PROFILE	67	ALTER PROFILE
68	DROP PROCEDURE	70	ALTER RESOURCE COST
71	CREATE MATERIALIZED VIEW LOG	72	ALTER MATERIALIZED VIEW LOG
73	DROP MATERIALIZED VIEW LOG	74	CREATE MATERIALIZED VIEW
75	ALTER MATERIALIZED VIEW	76	DROP MATERIALIZED VIEW
77	CREATE TYPE	78	DROP TYPE
79	ALTER ROLE	80	ALTER TYPE
81	CREATE TYPE BODY	82	ALTER TYPE BODY
83	DROP TYPE BODY	84	DROP LIBRARY
85	TRUNCATE TABLE	86	TRUNCATE CLUSTER
91	CREATE FUNCTION	92	ALTER FUNCTION
93	DROP FUNCTION	94	CREATE PACKAGE
95	ALTER PACKAGE	96	DROP PACKAGE
97	CREATE PACKAGE BODY	98	ALTER PACKAGE BODY
99	DROP PACKAGE BODY	100	LOGON
101	LOGOFF	102	LOGOFF BY CLEANUP
103	SESSION REC	104	SYSTEM AUDIT
105	SYSTEM NOAUDIT	106	AUDIT DEFAULT
107	NOAUDIT DEFAULT	108	SYSTEM GRANT
109	SYSTEM REVOKE	110	CREATE PUBLIC SYNONYM
111	DROP PUBLIC SYNONYM	112	CREATE PUBLIC DATABASE LINK
113	DROP PUBLIC DATABASE LINK	114	GRANT ROLE
115	REVOKE ROLE	116	EXECUTE PROCEDURE
117	USER COMMENT	118	ENABLE TRIGGER
119	DISABLE TRIGGER	120	ENABLE ALL TRIGGERS
121	DISABLE ALL TRIGGERS	122	NETWORK ERROR
123	EXECUTE TYPE	157	CREATE DIRECTORY
158	DROP DIRECTORY	159	CREATE LIBRARY
160	CREATE JAVA	161	ALTER JAVA
162	DROP JAVA	163	CREATE OPERATOR
164	CREATE INDEXTYPE	165	DROP INDEXTYPE
167	DROP OPERATOR	168	ASSOCIATE STATISTICS
169	DISASSOCIATE STATISTICS	170	CALL METHOD
171	CREATE SUMMARY	172	ALTER SUMMARY
173	DROP SUMMARY	174	CREATE DIMENSION
175	ALTER DIMENSION	176	DROP DIMENSION
177	CREATE CONTEXT	178	DROP CONTEXT
179	ALTER OUTLINE	180	CREATE OUTLINE
181	DROP OUTLINE	182	UPDATE INDEXES
183	ALTER OPERATOR

 

7、OWNERID

       如果值为2147483644，则此列的内容无效。否则此列包含拥有可移植会话的用户标符。对于利用并行从服务器的操作，将这个值解释为一个48 字节的值。其低位两字节表示会话号，而高位字节表示查询协调程序的实例ID

8、LOCKWAIT Address of lock waiting for; null if none

      等待锁的地址；如果没有，为NULL

9、STATUS

        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

         文档里面已经说的比较清楚了

10、SERVER 

         服务器类型：DEDICATED（独有）、SHARED（共享）、PSEUDO、NONE

         服务器类型在安装时候已经选择了，我们一般选择DEDICATED模式，非共享。

11、SCHEMA#   模式用户标识符

12、SCHEMANANME  模式用户名、OSUSER   操作系统客户机用户

13、PROCESS 操作系统客户机进程ID

       关于v$process与v$session中process的理解

       说明
       v$session有个process字段，V$PROCESS有个SPID字段，这两个字段是不是一个意思呢？是不是都代表会话的操作系统进程呢？
       官方文档上的解释：
       SPID      VARCHAR2(12)  Operating system process identifier
       PROCESS   VARCHAR2(9)   Operating system client process ID

      本文以数据库服务器安装在linux上为例进行说明。
      V$PROCESS中的SPID表示的是操作系统的进程，v$session中的process表示客户端进程ID，即客户端进程在客户端机器上的进程ID号。一个表示客户端进程在客户端机器上的进程号，一个表示服务器进程在服务器上的进程号。
      连接服务器的会话，发起会话的客户端进程可能是unix进程，也可能是windows进程。
      -------------------
      windows客户端进程
     -------------------
     例如，使用windows进程连接unix上的数据库，对应会话sid=35，对应客户端windows的988:5412

14、MACHINE 操作系统机器名、TERMINAL  操作系统终端名

       

          可以根据主terminal查询客户端的ip

          select utl_inaddr.get_host_address(terminal) from v$session where username is not null;

15、PROGRAM  操作系统程序名

        通过本机连接的session，一般都有program。如果是通过服务器连接的session，一般都没有program。

16、TYPE 会话类型

          一般有两个类型：background（后台进程）、user（用户）

17、SQL_ADDRESS 

       Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
       当前正在执行的SQL语句的SQL_HASH_VALUE值

18、SQL_HASH_VALUE 

        Used with SQL_ADDRESS to identify the SQL statement that is currently being executed
        当前正在执行的SQL语句的SQL_ADDRESS值
        SQL_HASH_VALUE，SQL_ADDRESS：这两列用于鉴别默认被session执行的SQL语句。如果为null或0，那就说明这个session没有执行任何SQL语句。PREV_HASH_VALUE和PREV_ADDRESS两列用来鉴别被session执行的上一条语句。

19、SQL_ID

         SQL identifier of the SQL statement that is currently being executed
         正在执行的SQL语句的标识符

20、SQL_CHILD_NUMBER

         Child number of the SQL statement that is currently being executed

21、PREV_SQL_ADDR

         Used with PREV_HASH_VALUE to identify the last SQL statement executed

22、PREV_HASH_VALUE

       

          Used with SQL_HASH_VALUE to identify the last SQL statement executed

23、MODULE、ACTION

        

         Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure

        

session 1:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        10

SQL> create or replace procedure proc1
  2  as
  3  begin
  4    dbms_application_info.set_action('PROC1');
  5    for i in 1..50 loop
  6      dbms_lock.sleep(1);
  7    end loop;
  8    dbms_application_info.set_action(NULL);
  9  end;
 10  /

Procedure created.

SQL> create or replace procedure proc2
  2  as
  3  begin
  4    dbms_application_info.set_action('PROC2');
  5    for i in 1..30 loop
  6      dbms_lock.sleep(2);
  7    end loop;
  8    dbms_application_info.set_action(NULL);
  9  end;
 10  /

Procedure created.

SQL> create or replace procedure proc_main
  2  as
  3  begin
  4    dbms_application_info.set_module('PROC_MAIN','MAIN');
  5    dbms_lock.sleep(20);
  6    proc1;
  7    proc2;
  8    dbms_application_info.set_module('PROC_MAIN',NULL);
  9  end;
 10  /

Procedure created.

SQL> exec proc_main;


session 2:
SQL> select sid,serial#,module,action from v$session where sid=10;

       SID    SERIAL# MODULE     ACTION
---------- ---------- ---------- ----------
        10         26 PROC_MAIN  MAIN
......       
SQL> select sid,serial#,module,action from v$session where sid=10;

       SID    SERIAL# MODULE     ACTION
---------- ---------- ---------- ----------
        10         26 PROC_MAIN  PROC1
......
SQL> select sid,serial#,module,action from v$session where sid=10;

       SID    SERIAL# MODULE     ACTION
---------- ---------- ---------- ----------
        10         26 PROC_MAIN  PROC2
......
SQL> select sid,serial#,module,action from v$session where sid=10;

       SID    SERIAL# MODULE     ACTION
---------- ---------- ---------- ----------
        10         26 PROC_MAIN_HASH_VALUE，SQL_ADDRESS 

如果是psql登录的也可以看到登录工具：MODULE 为登录工具、或者sqlplus工具，action是sql窗口你正在做的操作

24、MODULE_HASH、ACTION_HASH hash值

25、CLIENT_INFO：还记得SET COMMAND ID命令吗？该命令设置的值就会在V$SESSION.CLIENT_INFO中体现。

         让我举个例子说给你们看看的，这个可以找了好长时间的啊。

1）首先，让我们来设置SET_CLIENT_INFO，这里我们通过一个触发器

SQL> CREATE OR REPLACE TRIGGER trigger_name AFTER LOGON ON DATABASE
  2  DECLARE
  3  ipinfo VARCHAR2(30);
  4  BEGIN
  5  SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO ipinfo FROM DUAL;
  6  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(ipinfo);
  7  END;
  8  /
 
Trigger created

2）这个触发器的意思能够看懂吧，通过这个触发器，让我一登陆就是知道了

[oracle@IM-8-201 ~]$ sqlplus test/test
SQL> select CLIENT_INFO from v$session  where username='TEST';
 
CLIENT_INFO
----------------------------------------------------------------
192.168.1.100
 

26、FIXED_TABLE_SEQUENCE: 

          当session完成一个user call后就会增加的一个数值，也就是说，如果session inactive，它就不会增加。因此可以根据此字段的值变化来监控某个时间点以来的session的性能情况。例如，一个小时以前，某个session的FIXED_TABLE_SEQUENCE是10000，而现在是20000，则表明一个小时内其user call比较频繁，可以重点关注此session的performance statistics。

SQL> conn sys/sys as sysdba
Connected.
SQL>  SELECT FIXED_TABLE_SEQUENCE FROM V$SESSION a where a.username='SYS';

FIXED_TABLE_SEQUENCE
--------------------
                3023

SQL>  SELECT FIXED_TABLE_SEQUENCE FROM V$SESSION a where a.username='SYS';

FIXED_TABLE_SEQUENCE
--------------------
                3025

SQL>  SELECT FIXED_TABLE_SEQUENCE FROM V$SESSION a where a.username='SYS';

FIXED_TABLE_SEQUENCE
--------------------
                3027

27、ROW_WAIT_OBJ#

        被锁定行所在table的object_id。和dba_objects中的object_id关联可以得到被锁定的table name。

28、ROW_WAIT_FILE#

被锁定行所在的datafile id。和v$datafile中的file#关联可以得到datafile name。

29、ROW_WAIT_ROW#

          session当前正在等待的被锁定的行。

 
30、LOGON_TIME: session logon time

31、LAST_CALL_ET

        If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
        If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

32、PDML_ENABLED This column has been replaced by column PDML_STATUS

         既然这么说，就要先看PDML_STATUS

33、PDML_STATUS

         If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML.

         PARALLEL ：数据并行技术，具体等会儿给链接

34、PDDL_STATUS

         If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL.

         这个是针对DDL的

35、PQ_STATUS

         If ENABLED, the session is in a PARALLEL QUERY enabled mode. If DISABLED, PARALLEL QUERY enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL QUERY.

         这个是针对select的，简单吧

36、CURRENT_QUEUE_DURATION
         If queued (1), the current amount of time the session has been queued. If not currently queued, the value is 0.
        if 1则session已经在队列中，if 0 则还未形成排队

37、CLIENT_IDENTIFIER
         Client identifier of the session

38、BLOCKING_SESSION_STATUS
         Blocking session status：
*VALID 状态valid为正在等待
*NO HOLDER 
*GLOBAL
*NOT IN WAIT
*UNKNOWN

发生阻塞时候可以查询

到了10G,多了blocking_session 和 blocking_session_status字段，可以知道是否被阻塞。如果blocking_session_status字段是VALID，那一个有效的SID会出现在blocking_session 字段。
SQL>  select sid, sql_id,  PREV_SQL_ID , BLOCKING_SESSION_STATUS , BLOCKING_SESSION ,WAIT_CLASS_ID,SECONDS_IN_WAIT   ,WAIT_CLASS   from v$session  where
  2   username='KONG';


       SID SQL_ID        PREV_SQL_ID   BLOCKING_SESSION_STATUS           BLOCKING_SESSION WAIT_CLASS_ID SECONDS_IN_WAIT WAIT_CLASS
---------- ------------- ------------- --------------------------------- ---------------- ------------- --------------- ----------------------------------------------------------------
       290 0hcsvq77pq2a8 dyk4dprp70d74 VALID                                          300    4217450380             118 Application
       300               0hcsvq77pq2a8 NO HOLDER                                             2723168908             124 Idle
可以看到290 的进程被300的进程阻塞。


在以前如果想要查询某个session执行了那个sql语句，那需要用两个字段来查询： HASH_VALUE和ADDRESS。现在只有一个SQL_ID就可以了。
SQL> select  sql_text from v$sqltext st, v$session s
  2  where (st.sql_id = s.sql_id ) and s.sid=300;


SQL_TEXT
----------------------------------------------------------------
select count(*) from t2

　　BLOCKING_INSTANCE                NUMBER          模块化的实例标识符
　　BLOCKING_SESSION                 NUMBER          模块化的session标识符
　　SEQ#                             NUMBER          不唯一的标示每个等待的序列号
　　EVENT#                           NUMBER          事件数量
　　EVENT                            VARCHAR2(64)    oracle的session正在等待的数据或者事件
　　P1TEXT                           VARCHAR2(64)    首个附加参数的描述
　　P1                               NUMBER          首个附加参数
　　P1RAW                            RAW(4)          首个附加参数和前一个区别我还不是很懂
　　P2TEXT                           VARCHAR2(64)    第二个附加参数的描述
　　P2                               NUMBER          第二个附加参数
　　P2RAW                            RAW(4)          第二个附加参数
　　P3TEXT                           VARCHAR2(64)    第三个附加参数的描述
　　P3                               NUMBER          第三个附加参数
　　P3RAW                            RAW(4)          第三个附加参数
　　WAIT_CLASS_ID                    NUMBER          标记等待事件种类
　　WAIT_CLASS#                      NUMBER          等待事件的种类
　　WAIT_CLASS                       VARCHAR2(64)    等待事件的名称
　　WAIT_TIME                        NUMBER          非0代表上一次session上次等待时间，0代表session当前正在等待
　　SECONDS_IN_WAIT                  NUMBER


oracle文档的资料：
　　If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the
　　current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the
　　seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_
　　TIME / 100 is the active seconds since the last wait ended.
　STATE                VARCHAR2(19)
　　oracle资料文档：
　　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)        session的服务名称
　　SQL_TRACE                      VARCHAR2(8)         标示sql是否能被跟踪
　　SQL_TRACE_WAITS                VARCHAR2(5)         标记是否等待事件被跟踪
　　SQL_TRACE_BINDS                VARCHAR2(5)         标记是否绑定跟踪可用与否