正式环境上经常有SQL执行很慢,看执行计划没有问题,在测试环境上执行很快,可能是程序的业务逻辑写的有问题,唯有查下是否被堵塞。
1、ROW_WAIT_OBJ#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,ROW_WAIT_FILE#的数值只在被阻塞的会话是有效的
2、如果更新多行,则ROW_WAIT_OBJ#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,ROW_WAIT_FILE#对应的是第一个被阻塞的行的信息
select b.sid,
b.serial#,
b.process,
d.ctime "LOCK TIME (sec)",
c.object_name,
decode(a.locked_mode,
1, 'NULL',
2,'ROW SHARED',
3,'ROW EXCLUSIVE',
4,'SHARED',
5,'S/ROW EXCLUSIVE',
6, 'EXCLUSIVE') "LOCK MODE",
b.last_call_et "INACTIVE FOR(sec)",
decode(d.block, 1, 'Yes', 0, 'No') "BLOCKING",
dbms_rowid.rowid_create(1,
c.data_object_id,
b.ROW_WAIT_FILE#,
b.ROW_WAIT_BLOCK#,
b.ROW_WAIT_ROW#) "LOCKed ROWID"
from v$locked_object a, v$session b, dba_objects c, v$lock d
where b.sid = a.session_id
and c.object_id = a.object_id
and d.sid = a.session_id
and d.id1 = a.object_id;
实验:在session1中:update pub_user_bak u set u.cas_code='aaa' where user_id='N00268';
在session2中:update pub_user_bak u set u.cas_code='aaa' where user_id='N00268';
用上述SQL可以查出堵塞的rowid是:AAAYayAAJAAApsUAAF和AAAYayAAJAAAGcbAAA
select * from pub_user_bak where rowid='AAAYayAAJAAApsUAAF';--这个可以查到,就是'N00268'
select * from pub_user_bak where rowid='AAAYayAAJAAAGcbAAA';--这个查不到