119 lines
4.0 KiB
MySQL
119 lines
4.0 KiB
MySQL
@@header
|
|
|
|
/*
|
|
|
|
Author : Vishal Gupta
|
|
Purpose : Displays blocking locks in database
|
|
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ --------------------------------------------------------
|
|
* 19-Oct-12 Vishal Gupta Added ClientProcess in output and changed column sizing
|
|
* 18-Apr-12 Vishal Gupta Replace with gv$session with materialize hinted
|
|
* sub-query factoring (CTE - common table extression)
|
|
* 27-Feb-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
PROMPT
|
|
PROMPT *********************************
|
|
PROMPT * B L O C K I N G L O C K S
|
|
PROMPT *********************************
|
|
PROMPT
|
|
|
|
COLUMN INST_ID HEADING "I#" FORMAT 99
|
|
COLUMN SID FORMAT a40
|
|
COLUMN username HEADING "UserName" FORMAT a20 TRUNC
|
|
COLUMN lock_type HEADING "Lock|type" FORMAT a4 TRUNC
|
|
COLUMN lock_Name HEADING "Lock Name" FORMAT a22 TRUNC
|
|
COLUMN osuser HEADING "OSUser" FORMAT a20 TRUNC
|
|
COLUMN machine HEADING "Client|Machine" FORMAT a20 TRUNC
|
|
COLUMN process HEADING "Client|Process" FORMAT a10
|
|
COLUMN program HEADING "Program" FORMAT a30 TRUNC
|
|
COLUMN event HEADING "Event" FORMAT a30 TRUNC
|
|
|
|
|
|
--
|
|
----
|
|
---- Need to use Subquery for gv$session, otherwise session is disconnected with
|
|
---- ORA-07445 [qksopCheckPropOpt] when joining dynamic performance views in heirarichal query.
|
|
----
|
|
--WITH sess as
|
|
-- ( select /*+ RULE NO_MERGE */ s1.* from gv$session s1
|
|
-- )
|
|
--, locks as
|
|
-- (select /*+ RULE NO_MERGE */ *
|
|
-- from gv$lock where lmode = 0
|
|
-- --AND ( lmode = 0 OR (lmode > 0 AND block > 0))
|
|
-- )
|
|
--SELECT /*+ RULE */
|
|
-- s.inst_id
|
|
-- , DECODE(level
|
|
-- ,1 , s.sid || ' (Blocker)'
|
|
-- , ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || s.sid
|
|
-- )
|
|
-- sid
|
|
-- , s.status
|
|
-- , lt.name lock_name
|
|
-- , s.username
|
|
-- , s.osuser
|
|
-- , s.sql_id
|
|
-- , s.process
|
|
-- , s.program
|
|
-- , s.event
|
|
--FROM sess s
|
|
-- LEFT OUTER JOIN locks l ON l.inst_id = s.inst_id AND l.sid = s.sid
|
|
-- LEFT OUTER JOIN v$lock_type lt ON l.type = lt.type
|
|
--WHERE ( s.blocking_session IS NOT NULL
|
|
-- OR exists (select 1 from sess s2 where s2.blocking_instance = s.inst_id and s2.blocking_session = s.sid)
|
|
-- )
|
|
--CONNECT BY PRIOR s.inst_id = s.blocking_instance
|
|
-- AND PRIOR s.sid = s.blocking_session
|
|
--START WITH s.blocking_session IS NULL
|
|
--;
|
|
|
|
SELECT l.inst_id, DECODE(l.request,0,'Holder ','---> Waiter ')|| l.sid sid,l.id1, l.id2, l.lmode, l.request, lt.name, l.CTIME, l.BLOCK
|
|
, s.username
|
|
FROM gV$LOCK l
|
|
, gv$session s
|
|
, gv$lock_type lt
|
|
WHERE l.inst_id = s.inst_id
|
|
AND l.sid = s.sid
|
|
AND l.addr = s.
|
|
AND l.type = lt.type
|
|
AND (l.id1, l.id2, l.type) IN (SELECT l2.id1, l2.id2, l2.type FROM gV$LOCK l2 WHERE l2.request>0)
|
|
ORDER BY l.id1, l.request;
|
|
|
|
|
|
SELECT s.inst_id
|
|
, DECODE(level
|
|
,1 , s.sid || ' (Blocker)'
|
|
, ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || s.sid
|
|
)
|
|
, s.blocking_session
|
|
, s.blocking_instance
|
|
, s.final_blocking_session
|
|
, s.final_blocking_instance
|
|
, s.status
|
|
-- , lt.name lock_name
|
|
, s.username
|
|
, s.osuser
|
|
, s.sql_id
|
|
, s.process
|
|
, s.program
|
|
, s.event
|
|
FROM gv$session s
|
|
, gv$lock l
|
|
, gv$lock_type lt
|
|
WHERE s.inst_id = l.inst_id
|
|
AND s.sid = l.sid
|
|
AND l.type = lt.type
|
|
START WITH s.blocking_session is null
|
|
CONNECT BY NOCYCLE
|
|
PRIOR s.inst_id = s.blocking_instance
|
|
AND s.sid = s.blocking_session
|
|
;
|
|
|
|
@@footer
|