诊断Oracle数据库Hanging问题(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>'