DBA常用SQL之会话与等待事件
SELECT * FROM V$SESSION WHERE USERNAME IS NOT NULL AND STATUS = 'ACTIVE' ORDER BY LOGON_TIME, SID;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
2.查询会话与对应的进程 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'); --查询当前会话
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
6.查询会话及锁与对应的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;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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' : sid 182 : sertal# 6.1查询会话及锁与对应SQL
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
6.2查询阻塞会话与被阻塞会话的对应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 ;