/
latchprof_install.sql
123 lines (104 loc) · 2.77 KB
/
latchprof_install.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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
-- LatchProfX collector v2.0 by Tanel Poder (blog.tanelpoder.com)
-- drop table latchprof_reasons;
create table latchprof_reasons (
indx number not null
, reason_name varchar2(200) not null
, reason_label varchar2(200)
, primary key (indx)
)
organization index
tablespace users
/
insert into latchprof_reasons (indx,reason_name,reason_label)
select indx, ksllwnam, ksllwlbl
from x$ksllw
/
commit;
--drop table latchprof_history;
create table latchprof_history
tablespace users
as
select
systimestamp sample_timestamp
, lh.ksuprpid pid
, lh.ksuprsid sid
, lh.ksuprlat child_address
, lh.ksuprlnm latch_name
, lh.ksuprlmd hold_mode
, lh.ksulawhr where_location
, lh.ksulawhy which_object
, s.ksusesqh sqlhash
, s.ksusesql sqladdr
, s.ksusesph planhash
, s.ksusesch sqlchild
, s.ksusesqi sqlid
from
x$ksuprlat lh
, x$ksuse s
where
lh.ksuprsid = s.indx
and 1=0
/
create or replace package latchprof as
procedure snap_latchholder(p_sleep in number default 1);
end latchprof;
/
show err
create or replace package body latchprof as
procedure snap_latchholder(p_sleep in number default 1) as
begin
while true loop
insert into latchprof_history
select /*+ LEADING(lh) USE_NL(s) LATCHPROF_INSERT */
systimestamp sample_timestamp
, lh.ksuprpid pid
, lh.ksuprsid sid
, lh.ksuprlat child_address
, lh.ksuprlnm latch_name
, lh.ksuprlmd hold_mode
, lh.ksulawhr where_location
, lh.ksulawhy which_object
, s.ksusesqh sqlhash
, s.ksusesql sqladdr
, s.ksusesph planhash
, s.ksusesch sqlchild
, s.ksusesqi sqlid
from
x$ksuprlat lh
, x$ksuse s
where
lh.ksuprsid = s.indx
;
commit;
dbms_lock.sleep(p_sleep);
end loop; -- while true
end snap_latchholder;
end latchprof;
/
show err
-- 9i version
create or replace view latchprof_view as
select
h.sample_timestamp
, h.pid
, h.sid
, h.child_address
, h.latch_name
, h.hold_mode
, h.where_location
, h.which_object
, h.sqlid
, h.sqlchild
, h.planhash
, h.sqlhash
, h.sqladdr
, r.reason_name
, r.reason_label
from
latchprof_history h
, latchprof_reasons r
where
h.where_location = r.indx
/