诊断Oraacle数据库Hanging问题
2007-05-07 12:07:40 来源:WEB开发网核心提示: 查询v$process视图中的LATCHWAIT设置是多少?如果这个值非空,那么继续查是谁保存了这个latch,诊断Oraacle数据库Hanging问题(8),SELECT latchwaitFROM v$processWHERE spid = <pid>;SELECT la
查询v$process视图中的LATCHWAIT设置是多少?如果这个值非空,那么继续查是谁保存了这个latch。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#;
上述这些保存了锁和latch的会话是否关闭了终端但是没有退出,这可能会导致一个影子进程继续保存那些资源,这样就需要杀掉相应的进程,可以使用如下语句:alter system kill session '<sid, serial# from v$session>'
如果会话没有被挂起而只是运行缓慢,那么需要查看会话的具体信息:
SELECT s.sid, s.value, t.name
FROM v$sesstat s, v$statname t
WHERE s.statistic# = t.statistic#
AND s.sid = <sid>;
如果会话极度的缓慢或者是被挂起了,那么需要查看会话的等待信息:SELECT *
FROM v$session_wait
where sid = <sid>;
如果是个分布式事务,那么需要在各个节点上都运行如下SQL语句:
SELECT * FROM dba_2pc_pending;
SELECT * FROM pending_sessions$;
SELECT * FROM pending_sub_sessions$;
SELECT * FROM dba_2pc_neighbors;
更多精彩
赞助商链接