SELECT *
FROM V$SESSION
WHERE USERNAME IS NOT NULL
AND STATUS = 'ACTIVE'
ORDER BY LOGON_TIME, SID;
1.查询会话
SELECT P.SPID,
S.SID,
S.OSUSER,
S.MACHINE,
S.PROGRAM,
S.LOGON_TIME,
Q.MODULE,
S.SERIAL#,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT
FROM V$PROCESS P
INNER JOIN V$SESSION S
ON P.ADDR = S.PADDR
INNER JOIN V$SQLAREA Q
ON S.SQL_ID = Q.SQL_ID
WHERE S.AUDSID = USERENV('SESSIONID'); --查询当前会话
2.查询会话与对应的进程
3.Wait_time和Second_in_wait字段值与state相关:
(1)如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
(2)如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
(3)如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
(4)如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。
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
;
3.查询会话等待与对应的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
;
4.查询会话等待事件与对应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
;
5.查询长时间运行的会话与对应的SQL
SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,
B.ORACLE_USERNAME 锁表用户名,
decode(D.type,
'XR',
'NULL',
'RS',
'SS(Row-S)',
'CF',
'SS(Row-S)',
'TM',
'TABLE LOCK',
'PW',
'TABLE LOCK',
'TO',
'TABLE LOCK',
'TS',
'TABLE LOCK',
'RT',
'ROW LOCK',
'TX',
'ROW LOCK',
'MR',
'S(Share)',
NULL) 锁定方式,
C.MACHINE 用户组,
C.TERMINAL 机器名,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程id,
DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,
C.SERVER,
C.SID,
e.SQL_TEXT,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d,v$sqltext E
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
and C.sid = d.sid
and B.LOCKED_MODE = D.LMODE
and c.SQL_ID=e.sql_id (+)
ORDER BY 1, 2;
6.查询会话及锁与对应的SQL
select t2.username,
t2.sid,
t2.serial#,
t3.object_name,
t2.OSUSER,
t2.MACHINE,
t2.PROGRAM,
t2.LOGON_TIME,
t2.COMMAND,
t2.LOCKWAIT,
t2.SADDR,
t2.PADDR,
t2.TADDR,
t2.SQL_ADDRESS,
t1.LOCKED_MODE
from v$locked_object t1, v$session t2, dba_objects t3,v$sql t4
where t1.session_id = t2.sid
and t1.object_id = t3.object_id
--- and t2.SADDR=t4.ADDRESS(+) and t2.sql_id=t4.sql_id
order by t2.logon_time alter system kill session '139, 182'
139 : sid 182 : sertal#
6.1查询会话及锁与对应SQL
SELECT
s1.username "WAITING USER"
, s1.osuser "OS User" , s1.LOGON_TIME "logon time"
, w.session_id "Sid"
, p1.spid "PID"
, q1.SQL_TEXT "SQLTEXT"
, s2.username "HOLDING User"
, s2.osuser "OS User" , s2.LOGON_TIME "logon time"
, h.session_id "Sid"
, p2.spid "PID"
, q2.SQL_TEXT "SQLTEXT"
FROM
sys.v_$process p1
, sys.v_$process p2
, sys.v_$session s1
, sys.v_$session s2
, dba_locks w
, dba_locks h
, v$sql q1
, v$sql q2
WHERE
h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND w.lock_type (+) = h.lock_type
AND w.lock_id1 (+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
AND s1.SQL_ID=q1.SQL_ID(+)
AND s2.SQL_ID=q2.SQL_ID(+)
order by h.session_id
;
6.2查询阻塞会话与被阻塞会话的对应SQL
