86 lines
3.2 KiB
MySQL
86 lines
3.2 KiB
MySQL
@@header
|
|
|
|
/*
|
|
|
|
Author : Vishal Gupta
|
|
Purpose : Displays blocking locks in database
|
|
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 27-Feb-12 Vishal Gupta Intial version
|
|
* 18-Apr-12 Vishal Gupta Replace with gv$session with materialize hinted
|
|
* sub-query factoring (CTE - common table extression)
|
|
*
|
|
*/
|
|
|
|
COLUMN INST_ID HEADING "I#" FORMAT 99
|
|
COLUMN SID FORMAT a20
|
|
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 blocking_instance HEADING "Bl'ing|Inst"
|
|
COLUMN blocking_session HEADING "Bl'ing|SID"
|
|
COLUMN blocking_session_status HEADING "Bl'ing|Session|Status"
|
|
COLUMN final_blocking_instance HEADING "Final|Bl'ing|Inst"
|
|
COLUMN final_blocking_session HEADING "Final|Bl'ing|SID"
|
|
COLUMN final_blocking_session_status HEADING "Final|Bl'ing|Session|Status"
|
|
COLUMN program HEADING "Program" FORMAT a30 TRUNC
|
|
COLUMN event HEADING "Event" FORMAT a30 TRUNC
|
|
|
|
|
|
-- Materialize hint is needed to speed up the joins
|
|
-- between dynamic performance view gv$session
|
|
|
|
-- Need to use Subquery for gv$session, otherwise session is disconnect with ORA-07445 [qksopCheckPropOpt]
|
|
WITH sess as
|
|
( select /*+ materialize */ s1.* from gv$session s1
|
|
)
|
|
, locks as
|
|
(select /*+ materialize */ * from gv$lock where lmode = 0
|
|
-- AND ( l.lmode = 0 OR (l.lmode > 0 AND l.block > 0))
|
|
)
|
|
SELECT s.inst_id
|
|
, DECODE(level
|
|
,1 , s.sid || ' (Blocker)'
|
|
, ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || s.sid
|
|
)
|
|
-- || NVL2(s2.blocking_session, ' (Blocker)', ' ')
|
|
sid
|
|
, s.status
|
|
-- , l.lmode
|
|
-- , l.request
|
|
-- , l.block
|
|
-- , s.lock_type
|
|
, lt.name lock_name
|
|
, s.username
|
|
, s.osuser
|
|
, s.sql_id
|
|
, s.program
|
|
, s.event
|
|
-- , s.blocking_instance
|
|
-- , s.blocking_session
|
|
-- , s.blocking_session_status
|
|
-- , s.final_blocking_instance
|
|
-- , s.final_blocking_session
|
|
-- , s.final_blocking_session_status
|
|
FROM sess s
|
|
LEFT OUTER JOIN sess s2 ON s2.blocking_instance = s.inst_id AND s2.blocking_session = s.sid
|
|
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 s2.sid IS NOT NULL
|
|
)
|
|
CONNECT BY PRIOR s.inst_id = s.blocking_instance
|
|
AND PRIOR s.sid = s.blocking_session
|
|
START WITH s.blocking_session IS NULL
|
|
;
|
|
|
|
@@footer
|