-
Notifications
You must be signed in to change notification settings - Fork 61
Expand file tree
/
Copy pathDBA Blocking Sessions.sql
More file actions
93 lines (92 loc) · 3.59 KB
/
DBA Blocking Sessions.sql
File metadata and controls
93 lines (92 loc) · 3.59 KB
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
/*************************************************************************/
/* */
/* (c) 2010-2024 Enginatics GmbH */
/* www.enginatics.com */
/* */
/*************************************************************************/
-- Report Name: DBA Blocking Sessions
-- Description: Chain of currently blocking and blocked database sessions from v$wait_chains
-- Excel Examle Output: https://www.enginatics.com/example/dba-blocking-sessions/
-- Library Link: https://www.enginatics.com/reports/dba-blocking-sessions/
-- Run Report: https://demo.enginatics.com/
select
vwc.instance inst_id,
gs.sid||' - '||gs.serial# sid_serial#,
lpad(' ',2*(vwc.level_-1))||vwc.level_ level_,
xxen_util.user_name(gs.module,gs.action,gs.client_identifier) user_name,
xxen_util.responsibility(gs.module,gs.action) responsibility,
xxen_util.module_type(gs.module,gs.action) module_type,
xxen_util.module_name(gs.module) module_name,
lpad(' ',2*(vwc.level_-1))||gs.status||nvl2(vwc.blocker_sid,null,' - blocking') status,
xxen_util.time(vwc.in_wait_secs) time,
vwc.in_wait_secs seconds,
xxen_util.client_time(gs.prev_exec_start) prev_exec_start,
vwc.wait_event_text wait_event,
(select do.owner||'.'||do.object_name||' ('||do.object_type||')' from dba_objects do where case when vwc.wait_event_text like 'enq: TM%' then vwc.p2 when vwc.wait_event_text like 'enq: TX%' then vwc.row_wait_obj# end=do.object_id) object,
gs.sql_id,
gsa.sql_fulltext sql_text,
case when vwc.wait_event_text like 'enq: TX%' then (
select
'select * from '||do.owner||'.'||do.object_name||' where rowid='''||dbms_rowid.rowid_create(1,do.data_object_id,ddf.relative_fno,vwc.row_wait_block#,vwc.row_wait_row#)||'''' show_blocked_row
from
dba_objects do,
dba_data_files ddf
where
vwc.row_wait_obj#=do.object_id and
vwc.row_wait_file#=ddf.file_id
) end show_blocked_row,
lpad(' ',2*(level-1))||gs.module module,
gs.machine,
gs.username db_user,
gs.osuser,
gs.client_identifier client_id,
gs.action,
gs.program,
case when gs.type<>'BACKGROUND' then 'alter system disconnect session '''||gs.sid||','||gs.serial#||',@'||gs.inst_id||''' immediate;' end disconnect_db_session,
case when gs.type<>'BACKGROUND' then 'kill -9 '||gp.spid end kill_server_process,
vwc.root_blocking_sid
from
(
select
vwc.*,
level level_,
connect_by_root vwc.sid root_blocking_sid
from
v$wait_chains vwc
where
vwc.blocker_sid is not null or vwc.num_waiters>0
connect by nocycle
prior vwc.sid=vwc.blocker_sid and
prior vwc.sess_serial#=vwc.blocker_sess_serial# and
prior vwc.instance=vwc.blocker_instance
start with
vwc.blocker_sid is null
) vwc,
gv$session gs,
gv$process gp,
(
select distinct
gsa.sql_id,
min(gsa.inst_id) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) inst_id,
min(gsa.plan_hash_value) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) plan_hash_value
from
gv$sqlarea gsa
) gsa0,
gv$sqlarea gsa
where
(vwc.blocker_sid is not null or vwc.num_waiters>0) and
vwc.sid=gs.sid(+) and
vwc.sess_serial#=gs.serial#(+) and
vwc.instance=gs.inst_id(+) and
gs.inst_id=gp.inst_id(+) and
gs.paddr=gp.addr(+) and
gs.sql_id=gsa0.sql_id(+) and
gsa0.sql_id=gsa.sql_id(+) and
gsa0.inst_id=gsa.inst_id(+) and
gsa0.plan_hash_value=gsa.plan_hash_value(+)
connect by nocycle
prior vwc.sid=vwc.blocker_sid and
prior vwc.sess_serial#=vwc.blocker_sess_serial# and
prior vwc.instance=vwc.blocker_instance
start with
vwc.blocker_sid is null