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.

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欄位。

6、COMMAND  Command in progress (last statement parsed); for a list of values, see . 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

        

如果是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，這裡我們透過一個觸發器

 

 

 

2）這個觸發器的意思能夠看懂吧，透過這個觸發器，讓我一登陸就是知道了

 

 

26、FIXED_TABLE_SEQUENCE: 

 

          當session完成一個user call後就會增加的一個數值，也就是說，如果session inactive，它就不會增加。因此可以根據此欄位的值變化來監控某個時間點以來的session的效能情況。例如，一個小時以前，某個session的FIXED_TABLE_SEQUENCE是10000，而現在是20000，則表明一個小時內其user call比較頻繁，可以重點關注此session的performance statistics。

 

 

 

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)         標記是否繫結跟蹤可用與否

 

------------------------------------------------------------------------------------------------------------------------

v$session

 

SADDR : session address

 

SID : session identifier，常用於連線其它列。

 

SERIAL# : sid 會重用，但是同一個sid被重用時，serial#會增加，不會重複。

 

AUDSID : audit session id。可以透過audsid查詢當前session的sid。select sid from v$session where audsid=userenv('sessionid');

 

PADDR : process address，關聯v$process的addr欄位，可以透過這個欄位查處當前session對應作業系統的那個程式的id。

 

USER# : session's user id。等於dba_users中的user_id。Oracle內部程式的user#為0。

 

USERNAME : session's username。等於dba_users中的username。Oracle內部程式的username為空。

 

COMMAND : session正在執行的SQL Id。1代表create table，3代表select。

 

TADDR : 當前的transaction address。可以用來關聯v$transaction的addr欄位。

 

LOCKWAIT : 可以透過這個欄位查詢出當前正在等待的鎖的相關資訊。sid & lockwait與v$lock中的sid & kaddr相對應。

 

STATUS : 用來判斷session狀態。Active：正執行SQL語句。Inactive：等待操作。Killed：被標註為刪除。

 

SERVER : server type (dedicated or shared)

 

SCHEMA# : schema user id。Oracle內部程式的schema#為0。

 

SCHEMANAME : schema username。Oracle內部程式的schemaname為sys。

 

OSUSER : 客戶端作業系統使用者名稱。

 

PROCESS : 客戶端process id。

 

MACHINE : 客戶端machine name。

 

TERMINAL : 客戶端執行的terminal name。

 

PROGRAM : 客戶端應用程式。比如ORACLE.EXE (PMON)或者sqlplus.exe

 

TYPE : session type (background or user)

 

SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER : session正在執行的sql statement，和v$sql中的address, hash_value, sql_id, child_number相對應。

 

PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER : 上一次執行的sql statement。

 

MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO : 應用透過DBMS_APPLICATION_INFO設定的一些資訊。

 

FIXED_TABLE_SEQUENCE : 當session完成一個user call後就會增加的一個數值，也就是說，如果session inactive，它就不會增加。因此可以根據此欄位的值變化來監控某個時間點以來的session的效能情況。例如，一個小時以前，某個session的FIXED_TABLE_SEQUENCE是10000，而現在是20000，則表明一個小時內其user call比較頻繁，可以重點關注此session的performance statistics。

 

ROW_WAIT_OBJ# : 被鎖定行所在table的object_id。和dba_objects中的object_id關聯可以得到被鎖定的table name。

 

ROW_WAIT_FILE# : 被鎖定行所在的datafile id。和v$datafile中的file#關聯可以得到datafile name。

 

ROW_WAIT_BLOCK# : Identifier for the block containing the row specified in ROW_WAIT_ROW#

 

ROW_WAIT_ROW# : session當前正在等待的被鎖定的行。

 

LOGON_TIME : session logon time

 

v$process

 

ADDR : process address。可以和v$session的paddr欄位關聯。

 

PID : Oracle程式identifier。

 

SPID : 作業系統程式identifier。

 

USERNAME : 作業系統程式的使用者名稱。並非Oracle使用者名稱。

 

SERIAL#:: process serial number。

 

TERMINAL : 作業系統terminal identifier(e.g., computer name)。

 

PROGRAM : 程式正在執行的程式(e.g., ORACLE.EXE (ARC0))，和v$session中的program類似。

 

BACKGROUND : 1代表oracle background process，null代表normal process。

 

 

檢視當前使用者的sid和serial#:

select sid, serial#, status from v$session where audsid=userenv('sessionid');

 

檢視當前使用者的spid:

select spid from v$process p, v$session s where s.audsid=userenv('sessionid') and s.paddr=p.addr;

select spid from v$process p join v$session s on p.addr=s.paddr and s.audsid=userenv('sessionid');

 

檢視當前使用者的trace file路徑:

select p.value || '/' || t.instance || '_ora_' || ltrim(to_char(p.spid,'fm99999')) || '.trc'

    from v$process p, v$session s, v$parameter p, v$thread t

    where p.addr = s.paddr and s.audsid = userenv('sessionid') and p.name = 'user_dump_dest';

 

已知spid，檢視當前正在執行或最近一次執行的語句:

select /*+ ordered */ sql_text from v$sqltext sql

    where (sql.hash_value, sql.address) in (

        select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address)

        from v$session s where s.paddr = (select addr from v$process p where p.spid = to_number('&pid')))

    order by piece asc;

 

檢視鎖和等待:

col user_name format a10

col owner format a10

col object_name format a15

col sid format 999999

col serial# format 999999

col spid format a6

select /*+ rule */ lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username user_name,

    o.owner, o.object_name, o.object_type, s.sid, s.serial#, p.spid

    from v$locked_object l, dba_objects o, v$session s, v$process p

    where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr

    order by o.object_id, xidusn desc;