-
Notifications
You must be signed in to change notification settings - Fork 39
/
locks.sql
93 lines (62 loc) · 2.57 KB
/
locks.sql
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
-- locks.sql locks and enqueue blocks for 11g
-- Luca Jan 2012
set feedback off
col oracle_username for a15
col owner for a15
col object_name for a15
col inst_sid_s# for a13
col username for a14
col obj_lck for a18
col blk_info for a14
col f_blk_info for a14
col event for a30
col s_wt for 9999
col chain_signature for a65
col Wsecs for 999
/*
prompt DML locks from current instance (dba_dml_locks)
select session_id sid, owner,name,mode_held,mode_requested from dba_dml_locks;
prompt
prompt sessions with lockwait from gv$session
select inst_id||' '||sid||','||serial# inst_sid_s#, username, row_wait_obj#||','||row_wait_block#||','||row_wait_row# obj_lck,
blocking_session_Status||' '||blocking_instance||','||blocking_session blk_info,
final_blocking_session_Status||' '||final_blocking_instance||','||final_blocking_session f_blk_info,
event, seconds_in_wait s_wt
from gv$session
where lockwait is not null
order by inst_id;
prompt
prompt waitchains (all events)
select instance||' '||sid||','||sess_serial# inst_sid_s#, chain_signature,num_waiters wrs#,in_wait_secs Wsecs,row_wait_obj#||','||row_wait_block# obj_lck,
blocker_is_valid||' '||blocker_instance||','||blocker_sid blk_info
from v$wait_chains
where in_wait='TRUE' and blocker_is_valid='TRUE'
order by instance,chain_signature;
*/
prompt
prompt final blockers from gv$session_blockers
select * from GV$SESSION_BLOCKERS;
prompt
prompt final blockers from gv$session (all events)
select final_blocking_instance f_blk_inst, final_blocking_session f_blk_sess, event, sql_id, row_wait_obj#||','||row_wait_block# obj_lck, count(*) num_blocked, round(max(wait_time_micro)/1000000,2) max_wait_sec
from gv$session
where final_blocking_session_Status='VALID'
group by final_blocking_instance, final_blocking_session, event, sql_id, row_wait_obj#||','||row_wait_block#
order by 1;
/*
prompt global blocked locks
select * from GV$GLOBAL_BLOCKED_LOCKS ;
prompt TX locks
select * from gv$transaction_enqueue;
prompt waiting sessions
select inst_id,sid,process,username,row_wait_obj#,LOCKWAIT,blocking_instance blk_inst, blocking_session blk_sid
from gv$session
where lockwait is not null;
prompt blocking sessions
select sid,username,row_wait_obj#,row_wait_block#,row_wait_row#,blocking_session from gv$session a where row_wait_obj#<>0 and blocking_Session is not null order by 2,1;
prompt blockers from gv$Lock
select inst_id,sid,type,ctime LOCK_TIME,id1,id2
from gv$lock
where block =1;
*/
set feedback on