124 lines
2.8 KiB
MySQL
124 lines
2.8 KiB
MySQL
|
-- 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
|
||
|
|
/
|
||
|
|
|