Oracle Blocking (blockingblocked) example detailed _oracle

First, overview:

Blocking is a common scenario for DBAs, especially the blocking caused by poor application design, which can result in a severe degradation of database performance until the database crashes. For DBAs, it is necessary to know how to navigate to what is blocking the current system, who is the blocking, and who is the blocked. This paper gives a description of this and the related demo.

Second, the demonstration blocking:

--Update the table, note, prompt Scott@cnmmbo indicates that the user is Scott's session, user name is different, session is different.
scott@cnmmbo> Update emp set sal=sal*1.1 where empno=7788;
1 row updated.  scott@cnmmbo> @my_env SPID SID serial# USERNAME program----------------------------------------------- ------------------------------------------------11205 1073 4642 Robin Oracle@szdb (TNS v1-v3)--another two sess
 
Ion update the same row, the two sessions will be waiting until the first session commits or rolls back the leshami@cnmmbo> update scott.emp set sal=sal+100 where empno=7788;
 
goex_admin@cnmmbo> Update scott.emp set sal=sal-50 where empno=7788; --below the first session query blocking situation scott@cnmmbo> @blocker block_msg block------------------------------- -----------------------------PTS/5 (' 1073,4642 ') is blocking 1067,10438 1 pts/5 (' 1073,4642 ') is blocking 1065,4  464 1--The results above indicate that session 1073,4642 blocks the following 2--that is, the session 1073,4642 is a block, and the next 2 sessions are blocked--author:leshami--blog : Http://blog.csdn.net/leshami--The following query is blocking session Id,sql statements andBlocked time scott@cnmmbo> @blocking_session_detail. 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--the following query blocks the hold of the lock scott@cnmmbo> @request_lock_type USERNAME SID TY lmode REQUEST ID1 ID2--------------------------------------------------------------- ---------------------SCOTT 1073 tx Exclusive None 524319 27412 leshami 1067 TX Non E Exclusive 524319 27412 goex_admin 1065 TX None Exclusive 524319 27412--you can see Leshami,goex_ad
 
    MIN 2 users are on request 524319/27412 of the exclusive lock, and at this time has been added by Scott exclusive lock-query blocking the holding details of the lock scott@cnmmbo> @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  Trans-524319 TX--waiting--Exclusive 1073 SCOTT Robin PTS/5 EMP TM Row excl 1073 

 SCOTT Robin PTS/5 Trans-524319 TX Exclusive

Three, the relevant SQL script involved in the complete code is as follows:

robin@szdb:~/dba_scripts/custom/sql> more My_env.sql SELECT spid, s.sid, s.serial#, P.username, P.program from V$proc

ESS p, v$session s WHERE p.addr = s.paddr and S.sid = (SELECT sid from V$mystat WHERE rownum = 1); 
robin@szdb:~/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.id2=b.id2 and a 

. block>0 and A.sid <>b.sid and A.sid=c.sid and B.sid=d.sid; robin@szdb:~/dba_scripts/custom/sql> more Blocking_session_detail.sql--to find the query for blocking session--ACCE SS Privileges:select on V$session, V$sqlarea SELECT ' sid= ' | | A.sid | | ' Wait class= ' | | A.wait_class | | ' Time= ' | | a.seconds_in_wait | | CHR (10) | | ' Query= ' | | B.sql_text from V$session A, V$sqlarea b WHERE a.blocking_Session isn't NULL and a.sql_address = b.address ORDER by a.blocking_session/robin@szdb:~/dba_scripts/custom/sql> Mo
Re request_lock_type.sql--this script generates a of the users waiting for locks.
            --access privileges: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 exc
             L. ', 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/ro W excl. ', 6, ' Exclusive ', request, LTrim (To_char (m.request, ' 990 ')) request, M.ID1, M.I D2 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)) OR 
DER by Id1, Id2, m.request; robin@szdb:~/dba_scripts/custom/sql> more Request_lock_detail.sql set linesize 190 col osuser format A15 col username F Ormat A20 wrap Col object_name format A20 wrap Col terminal format A25 wrap Col req_mode format A20 select B.sid, C.userna ME, 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, ' Row Share ', 3, ' Row excl ', 4, ' Share ', 5, ' Sha Row exc ', 6, ' Exclusive ', ' other ') "Req_mode" from Dba_objects A, V$lock B , V$session C where a.object_id (+) = B.ID1 and B.sid = C.sid and c.username is isn't null order by B.SID, B.id2;