sqlserver查看查看会话、等待事件

刘泽宇

1. 查看活动会话信息
SELECT *
  FROM V$SESSION
WHERE USERNAME IS NOT NULL
   AND STATUS = 'ACTIVE'
ORDER BY LOGON_TIME, SID;

注：status用来判断会话状态
       Active：正执行sql语句(waiting for/using a resource)sql

       Inactive：等待操做(即等待须要执行的sql语句)code

       killed：被标记为删除进程

2. 查看当前会话执行的sql语句及进程信息
SELECT P.SPID,
       S.SID,
       S.OSUSER,
       S.MACHINE,
       S.PROGRAM,
       S.LOGON_TIME,
       S.SERIAL#,
       Q.MODULE,
       Q.SQL_ID,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT
  FROM V$SESSION S, V$PROCESS P, V$SQLAREA Q
WHERE S.PADDR = P.ADDR(+)
   AND S.SQL_ID = Q.SQL_ID(+)
   AND S.AUDSID = USERENV('SESSIONID');
else:获取当前会话的SID事件

SELECT * FROM V$SESSION  WHERE AUDSID = USERENV('SESSIONID');

SELECT * FROM V$MYSTAT A WHERE ROWNUM=1;

SELECT USERENV('SID') FROM DUAL;

3. 查询会话等待与其对应的sql
SELECT P.PID,
       S.SID,
       S.SERIAL#,
       S.USERNAME,
       Q.SQL_ID,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT,
       W.EVENT,
       W.WAIT_TIME,
       W.STATE,
       CASE
         WHEN W.STATE = 'WAITING' THEN
          W.SECONDS_IN_WAIT
         WHEN W.STATE = 'WAITING KNOWN TIME' THEN
          W.WAIT_TIME
       END AS SEC_IN_WAIT
  FROM V$SESSION S, V$SESSION_WAIT W, V$SQLAREA Q, V$PROCESS P
WHERE S.SID = W.SID
   AND S.SQL_ID = Q.SQL_ID
   AND P.ADDR = S.PADDR
   AND W.EVENT NOT LIKE 'SQL*Net%'
   AND S.USERNAME IS NOT NULL
   AND W.WAIT_TIME >= 0
ORDER BY W.SECONDS_IN_WAIT DESC;
注： 若是state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
         若是state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。it

         若是state值为Wait short time，那么wait_time值和Second_in_wait值都无用。class

         若是state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。sql语句

4. 查询会话等待事件及对应的sql
SELECT P.PID,
       S.SID,
       S.SERIAL#,
       S.USERNAME,
       Q.SQL_ID,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT,
       E.EVENT,
       E.TOTAL_WAITS,
       E.TIME_WAITED,
       E.AVERAGE_WAIT
  FROM V$SESSION S, V$SESSION_EVENT E, V$SQLAREA Q, V$PROCESS P
WHERE S.SID = E.SID
   AND S.SQL_ID = Q.SQL_ID
   AND P.ADDR = S.PADDR
   AND E.EVENT NOT LIKE 'SQL*Net%'
   AND S.STATUS = 'ACTIVE'
   AND S.USERNAME IS NOT NULL;

5. 查询长时间运行的会话与对应的sql
SELECT S.SID,
       OPNAME,
       TRUNC(L.SOFAR / L.TOTALWORK * 100, 2) || '%' AS PCT_WORK,
       L.ELAPSED_SECONDS ELAPSED,
       ROUND(L.ELAPSED_SECONDS * (L.TOTALWORK - L.SOFAR) / L.SOFAR) REMAIN_TIME,
       Q.SQL_TEXT
  FROM V$SESSION_LONGOPS L, V$SQLAREA Q, V$SESSION S
WHERE L.SQL_HASH_VALUE = Q.HASH_VALUE
   AND L.SID = S.SID
   --AND L.SOFAR != L.TOTALWORK
   AND L.ELAPSED_SECONDS>6
ORDER BY L.START_TIME