반응형
|
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
|
-- #####################################################
-- ASH Lock History
--
-- Active Session History Lock History 확인
-- #####################################################
select /*+ opt_param('_complex_view_merging','true') */ to_char(max(blocked.sample_time),'YYYY-MM-DD HH24:MI:SS') as sample_time
, holder.instance_number as holder_inst_num
, holder.machine as holder_machine
, holder.program as holder_program
, holder.session_id as holder_sid
, holder_user.username as holder_username
, substr(holder_sql.sql_text,1,50) as holder_sql_text
, ' ------------------> ' as is_blocking
, blocked.instance_number as blocked_inst_num
, blocked.machine as blocked_machine
, blocked.program as blocked_program
, blocked.session_id as blocked_sid
, blocked_user.username as blocked_username
, blocked.session_state as blocked_session_state
, blocked.event as blocked_event
, blocked.blocking_session as blocked_blocking_session
, blocked.sql_id as blocked_sql_id
-- , blocked.sql_child_number as blocked_sql_child_number
, sys_obj.name as blocked_table_name
, substr(blocked_sql.sql_text,1,50) as blocked_sql_text
from dba_hist_snapshot sn
inner join
dba_hist_active_sess_history holder
on holder.dbid = sn.dbid
and holder.snap_id = sn.snap_id
and holder.instance_number = sn.instance_number
inner join
dba_hist_active_sess_history blocked
on holder.session_id = blocked.blocking_session
and holder.session_serial# = blocked.blocking_session_serial#
inner join
sys.obj$ sys_obj
on sys_obj.obj# = blocked.current_obj#
inner join
dba_users holder_user
on holder.user_id = holder_user.user_id
inner join
dba_users blocked_user
on blocked.user_id = blocked_user.user_id
left outer join
v$sql blocked_sql
on blocked_sql.sql_id = blocked.sql_id
and blocked_sql.child_number = blocked.sql_child_number
left outer join
v$sql holder_sql
on holder_sql.sql_id = holder.sql_id
and holder_sql.child_number = holder.sql_child_number
where blocked.instance_number = 2
and blocked.sample_time >= TO_DATE('2022-10-25 09:40:00','YYYY-MM-DD HH24:MI:SS')
and blocked.sample_time <= TO_DATE('2022-10-25 09:50:00','YYYY-MM-DD HH24:MI:SS')
AND sn.begin_interval_time >= TO_DATE('2022-10-25 09:40:00','YYYY-MM-DD HH24:MI:SS')
AND sn.begin_interval_time <= TO_DATE('2022-10-25 09:50:00','YYYY-MM-DD HH24:MI:SS')
and blocked.event = 'enq: TX - row lock contention'
group by holder.instance_number
, holder.machine
, holder.program
, holder.session_id
, holder_user.username
, ' ------------------> '
, blocked.instance_number
, blocked.machine
, blocked.program
, blocked.session_id
, blocked_user.username
, blocked.session_state
, blocked.event
, blocked.blocking_session
, blocked.sql_id
, blocked.sql_child_number
, sys_obj.name
, holder_sql.sql_text
, blocked_sql.sql_text
order by sample_time
|
cs |
반응형
댓글