oracle-script-lib/ash_blockers.sql at master · eratolekov/oracle-script-lib · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- awr_blockers.sql
-- show historic blocking , sql_id and whether mode 4 (ITL) or mode 6 (rowlock)
-- Jared Still
-- still@pythian.com
-- jkstill@gmail.com

@clears

@get_date_range

col lockmode format a20
col event_name format a40 head 'EVENT NAME'
set line 200 trimspool on
set pagesize 60


-- d_date_format set by get_date_range.sql

with waits as (
   select
      sh.inst_id instance_number
      , sh.blocking_inst_id
      , sh.sql_id
      , n.name event_name
		, chr(bitand(sh.p1,-16777216)/16777215)||
		       chr(bitand(sh.p1, 16711680)/65535) LOCKNAME
      , bitand(sh.p1,65535) lockmode
   from gv$active_session_history sh
   join v$event_name n on sh.event_id = n.event_id
   where sh.blocking_inst_id is not null
   --and sh.event_id = ( select event_id from v$event_name where name like 'enq: TX - row lock contention')
   and sh.event_id in ( select event_id from v$event_name where name like 'enq:%')
)
select
   w.instance_number
   , w.event_name
   --, w.blocking_inst_id
   --, w.sql_id
	, w.lockname
   --, decode(w.lockmode, 4,'ITL',6,'ROWLOCK','UNKNOWN') lockmode
	, case w.lockname
	when 'TX' then w.lockmode || '-' || decode(w.lockmode, 0,'None', 1,'No Lock', 2,'Row-S (SS)', 3,'Row-X (SX)',4,'ITL', 5, 'S/Row-X (SRX)', 6,'ROWLOCK','UNKNOWN')
	when 'TM' then w.lockmode || '-' || decode(w.lockmode, 0,'None', 1,'No Lock', 2,'Row-S (SS)', 3,'Row-X (SX)',4,'Share', 5, 'S/Row-X (SRX)', 6,'Exclusive','UNKNOWN')
	else w.lockmode || '-' || decode(w.lockmode, 0,'None', 1,'No Lock', 2,'Row-S (SS)', 3,'Row-X (SX)',4,'Share', 5, 'S/Row-X (SRX)', 6,'Exclusive','UNKNOWN')
	end lockmode
   , count(*) waitcount
from waits w
group by
   w.instance_number
   , w.event_name
   --, w.blocking_inst_id
   --, w.sql_id
	, w.lockname
   , w.lockmode
order by waitcount
/