1.查詢會(huì)話等待事件及對(duì)應(yīng)的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;
2.查詢長(zhǎng)時(shí)間運(yùn)行的會(huì)話與對(duì)應(yīng)的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
3.查詢會(huì)話及鎖與對(duì)應(yīng)的sql
SELECT A.OWNER 方案名,
? ? ? A.OBJECT_NAME 表名,
? ? ? B.XIDUSN 回滾段號(hào),
? ? ? B.XIDSLOT 槽號(hào),
? ? ? B.XIDSQN 序列號(hào),
? ? ? 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 機(jī)器名,
? ? ? B.OS_USER_NAME 系統(tǒng)用戶名,
? ? ? B.PROCESS 系統(tǒng)進(jìn)程ID,
? ? ? DECODE(C.STATUS, 'INACTIVE', '不活動(dòng)', 'ACTIVE', '活動(dòng)') 活動(dòng)情況,
? ? ? 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;
4.查詢被阻塞會(huì)話與被阻塞會(huì)話的對(duì)應(yīng)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;
5. 30分鐘內(nèi)執(zhí)行時(shí)間最長(zhǎng)的語(yǔ)句
select ash.USER_ID,u.username,sum(ash.WAIT_TIME) ttl_wait_time,s.SQL_TEXT
from v$active_session_history ash, v$sqlarea s, dba_users u
where ash.SAMPLE_TIME between sysdate - 30/24/60 and sysdate
and ash.SQL_ID = s.SQL_ID
and ash.USER_ID = u.user_id
group by ash.USER_ID, s.SQL_TEXT, u.username
order by ttl_wait_time desc
