Oracle 查询阻塞

来源：互联网发布：怀化学院教务网络编辑：程序博客网时间：2024/06/05 17:02

-- Start

如果你的 SQL 或系统突然 hang 了，很有可能是因为一个 session 阻塞了另一个 session，如何查询是否发生阻塞了呢？看看下面的 SQL吧。

select blocksession.sid        as block_session_sid,blocksession.serial#    as block_session_serial#,blocksession.username   as block_session_username,blocksession.osuser     as block_session_osuser,blocksession.machine    as block_session_machine,blocksession.status     as block_session_status,blockobject.object_name as blocked_table,waitsession.sid         as wait_session_sid,waitsession.serial#     as wait_session_serial#,waitsession.username    as wait_session_username,waitsession.osuser      as wait_session_osuser,waitsession.machine     as wait_session_machine,waitsession.status      as wait_session_statusfrom v$lock          blocklock,v$lock          waitlock,v$session       blocksession,v$session       waitsession,v$locked_object lockedobject,dba_objects     blockobjectwhere blocklock.block    = 1and blocklock.sid != waitlock.sidand blocklock.id1 = waitlock.id1and blocklock.id2 = waitlock.id2and blocklock.sid = blocksession.sidand waitlock.sid  = waitsession.sidand lockedobject.session_id = blocksession.sidand lockedobject.object_id  = blockobject.object_id;

如果上面的语句返回了结果，表明发生了阻塞，这个时候你可以把使用 blocksession 的程序停掉。如果还是不能解决问题，那只能让 DBA 帮你把 blocksession kill 掉，如何 kill 呢？试一试下面的语句吧。

ALTER SYSTEM KILL SESSION '<block_session_sid>,<block_session_serial#>';ALTER SYSTEM KILL SESSION '113,55609';

如果没有发生阻塞，系统就是很慢，该怎么办呢？在"Oracle 查询耗时 SQL"找答案吧。

--更多参见：Oracle SQL 优化精萃

-- 声明：转载请注明出处

-- Last edited on 2015-08-28

-- Created by ShangBo on 2015-08-07

-- End

0 0