oracle堵塞(blockingblocked)实例详解_

1、oracle堵塞(blockingblocked)实例详解_ 一、概述： 堵塞是dba常常碰到的情形，尤其是不良的应用程序设计所造成的堵塞将导致数据库性能的严峻下降，直至数据库崩溃。对dba而言，有必要知道如何定位到当前系统有哪些堵塞，究竟谁是堵塞者，谁是被堵塞者。本文对此给出了描述并做了相关演示。 二、演示堵塞： -更新表，注，提示符scottcnmmbo表明用户为scott的session，用户名不同，session不同。 scottcnmmbo update emp set sal=sal*1.1 where empno=7788; 1 row updated. scottcnmmbo

2、my_env spid sid serial# username program - - - - - 11205 1073 4642 robin oracleszdb (tns v1-v3) -另起两个session更新同样的行，这两个session都会处于等待，直到第一个session提交或回滚 leshamicnmmbo update scott.emp set sal=sal+100 where empno=7788; goex_admincnmmbo update scott.emp set sal=sal-50 where empno=7788; -下面在第一个session 查询堵

3、塞状况 scottcnmmbo blocker block_msg block - - pts/5 (1073,4642) is blocking 1067,10438 1 pts/5 (1073,4642) is blocking 1065,4464 1 -上面的结果表明session 1073,4642 堵塞了后面的2个 -即session 1073,4642是堵塞者，后面2个session是被堵塞者 -author : leshami -blog : -下面查询正在堵塞的session id，sql语句以及被堵塞的时间 scottcnmmbo blocking_session_detai

4、l.sql sid=|a.sid|waitclass=|a.wait_class|time=|a.seconds_in_wait|chr(10)|query=|b.sql_text - sid=1067 wait class=application time=5995 query=update scott.emp set sal=sal+100 where empno=7788 sid=1065 wait class=application time=225 query=update scott.emp set sal=sal-50 where empno=7788 -下面的查询堵塞时锁的持有

5、状况 scottcnmmbo request_lock_type username sid ty lmode request id1 id2 - - - - - - - scott 1073 tx exclusive none 524319 27412 leshami 1067 tx none exclusive 524319 27412 goex_admin 1065 tx none exclusive 524319 27412 -可以看到leshami，goex_admin 2个用户都在恳求524319/27412上的exclusive锁，而此时已经被scott加了exclusive锁 -

6、查询堵塞时锁的持有具体信息 scottcnmmbo request_lock_detail sid username osuser terminal object_name ty lock mode req_mode - - - - - - - - 1065 goex_admin robin pts/1 emp tm row excl 1065 goex_admin robin pts/1 trans-524319 tx -waiting- exclusive 1067 leshami robin pts/0 emp tm row excl 1067 leshami robin pts/0 t

7、rans-524319 tx -waiting- exclusive 1073 scott robin pts/5 emp tm row excl 1073 scott robin pts/5 trans-524319 tx exclusive 三、文中涉及到的相关sql脚本完整代码如下： robinszdb:/dba_scripts/custom/sql more my_env.sql select spid, s.sid, s.serial#, p.username, gram from v$process p, v$session s where p.addr = s.padd

8、r and s.sid = (select sid from v$mystat where rownum = 1); robinszdb:/dba_scripts/custom/sql more blocker.sql col block_msg format a50; select c.terminal| (|a.sid|,|c.serial#|) is blocking |b.sid|,|d.serial# block_msg, a.block from v$lock a,v$lock b,v$session c,v$session d where a.id1=b.id1 and a.id

9、2=b.id2 and a.block0 and a.sid b.sid and a.sid=c.sid and b.sid=d.sid; robinszdb:/dba_scripts/custom/sql more blocking_session_detail.sql -to find the query for blocking session -access privileges: select on v$session, v$sqlarea select sid= | a.sid | wait class= | a.wait_class | time= | a.seconds_in_

10、wait | chr (10) | query= | b.sql_text from v$session a, v$sqlarea b where a.blocking_session is not null and a.sql_address = b.address order by a.blocking_session / robinszdb:/dba_scripts/custom/sql more request_lock_type.sql -this script generates a report of users waiting for locks. -access privil

11、eges: select on v$session, v$lock 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, lmode, 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

12、, exclusive, request, 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.

13、id2) ) order by id1, id2, m.request; robinszdb:/dba_scripts/custom/sql more request_lock_detail.sql set linesize 190 col osuser format a15 col username format a20 wrap col object_name format a20 wrap col terminal format a25 wrap col req_mode format a20 select b.sid, c.username, c.osuser, c.terminal, decode(b.id2, 0, a.object_name, trans-|to_char(b.id1) object_name, b.type, decode(b.lmode,0,-waiting-, 1,null, 2,row share, 3,row excl, 4,share, 5,sha row exc, 6,exclusive, other) lock mode, decode(b.request,0, , 1,null, 2