诊断Oraacle数据库Hanging问题(1)(3)

SPOOL v_views.log;
SELECT *
FROM v$parameter;
SELECT class, value, name
FROM v$sysstat;
SELECT sid, id1, id2, type, lmode, request
FROM v$lock;
SELECT l.latch#, n.name, h.pid, l.gets, l.misses,
l.immediate_gets, l.immediate_misses, l.sleeps
FROM v$latchname n, v$latchholder h, v$latch l
WHERE l.latch# = n.latch#
AND l.addr = h.laddr(+);
SELECT *
FROM v$session_wait
ORDER BY sid;
/* 重复最后一个查询最少三遍，以确定哪个在重复等待*/ 
SPOOL OFF; 

SELECT s.sid, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND ... < p.spid = <os pid> or perhaps
s.sid = <sid from v$session> >
然后通过SID找出相应的SQL语句的具体内容：
SELECT s.sid, s.status, q.sql_text
FROM v$session s, v$sqltext q
WHERE s.sql_hash_value = q.hash_value
AND s.sql_address = q.address
AND s.sid = <sid>
order by q.piece;

column sid format 990 
column seq# format 99990 
column wait_time heading 'WTime' format 99990 
column event format a30 
column p1 format 9999999990 
column p2 format 9999999990 
column p3 format 9990 
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait 
where sid=<SID> 
order by sid; 

SELECT lockwait
FROM v$session
WHERE sid = <sid>;
col Username format A15
col Sid format 9990 heading SID
col Type format A4
col Lmode format 990 heading 'HELD'
col Request format 990 heading 'REQ'
col Id1 format 9999990
col Id2 format 9999990
select SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2 from V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request

SELECT latchwait
FROM v$process
WHERE spid = <pid>;SELECT latchwait
FROM v$process
WHERE spid = <pid>;
column name format a32 heading 'LATCH NAME'
column pid heading 'HOLDER PID'
select c.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+) and a.latch# = c.latch#
and c.name like '&latch_name%' order by a.latch#;

alter system kill session '<sid, serial# from v$session>'