Reports2017-11-18T12:27:27+00:00

DBA AWR Latch by Time

Description
Categories: DBA, Enginatics
Non idle session wait times by wait class over time.
Each row shows the system-wide wait time per wait class of one AWR snapshot interval to identify unusual wait events that occured at specific times.
Use the Session Type parameter to restrict either to foreground, background or all server processes.

select
x.day_of_week,
x.end_interval_time,
x.instance_number,
&columns
from
(
select
to_char(xxen_util.client_time(dhs.end_interval_time),'Day') day_of_week,
xxen_util.client_time(dhs.end_interval_time) end_interval_time,
dhs.instance_number,
dhl.latch_name,
(dhl.wait_time-nvl(lag(dhl.wait_time) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0))/1000000 seconds
from
dba_hist_snapshot dhs,
dba_hist_latch dhl
where
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhl.dbid and
dhs.instance_number=dhl.instance_number and
dhs.snap_id=dhl.snap_id
)
pivot (
sum(case when seconds>0 then seconds end)
for
latch_name in (
&pivot_columns
)
) x
order by
x.end_interval_time desc,
x.instance_number

Parameter Name SQL text Validation
Date From
dhs.end_interval_time>=fnd_date.displaydt_to_date(:date_from)-1/1440
LOV
Date To
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to)
LOV
Date To
select * from
(
select * from
(
select
'''' ||dhl.latch_name||''' '||substr(translate(lower(dhl.latch_name),' .-:/','_____'),1,29)||',' pivot_text
from
(
select
dhl.wait_time-nvl(lag(dhl.wait_time) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) wait_time_,
dhl.*
from
dba_hist_snapshot dhs,
dba_hist_latch dhl
where
dhs.end_interval_time>=fnd_date.displaydt_to_date(:date_from)-1/1440 and
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to) and
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhl.dbid and
dhs.instance_number=dhl.instance_number and
dhs.snap_id=dhl.snap_id
) dhl
where
dhl.wait_time_>=0
group by
dhl.latch_name
having
sum(dhl.wait_time_)>0
order by
sum(dhl.wait_time_) desc
)
where
rownum<=30
)
union all
select '''dummy'' dummy' pivot_text from dual
Date To
with x as
(
select * from
(
select
substr(translate(lower(dhl.latch_name),' .-:/','_____'),1,29) column_name
from
(
select
dhl.wait_time-nvl(lag(dhl.wait_time) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) wait_time_,
dhl.*
from
dba_hist_snapshot dhs,
dba_hist_latch dhl
where
dhs.end_interval_time>=fnd_date.displaydt_to_date(:date_from)-1/1440 and
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to) and
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhl.dbid and
dhs.instance_number=dhl.instance_number and
dhs.snap_id=dhl.snap_id
) dhl
where
dhl.wait_time_>=0
group by
dhl.latch_name
having
sum(dhl.wait_time_)>0
order by
sum(dhl.wait_time_) desc
)
where
rownum<=30
)
select 'xxen_util.time(x.'||x.column_name||') '||x.column_name||',' from x union all
select 'x.'||x.column_name||',' from x union all
select 'null x' pivot_text from dual

By continuing to use the site, you agree to the use of cookies. Accept