dba_hist_system_event
Кумулятивная статистика (с момента старта экземпляра):
SELECT
sn.snap_id,
sn.dbid,
sn.instance_number,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
he.event_name,
he.total_waits,
he.time_waited_micro
FROM
dba_hist_system_event he,
dba_hist_snapshot sn
WHERE
he.snap_id=sn.snap_id;
Средняя статистика за период (snap duration):
SELECT
sn.snap_id,
sn.dbid,
sn.instance_number,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
bse.event_name,
(ase.total_waits - bse.total_waits) total_waits,
(ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits) avg_wait
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
dba_hist_snapshot sn
WHERE
bse.event_name = 'db file sequential read' and
ase.event_name = bse.event_name and
ase.snap_id = bse.snap_id +1 and
ase.instance_number = 1 and
bse.instance_number = ase.instance_number and
ase.snap_id = sn.snap_id and
ase.instance_number = sn.instance_number and
nvl(ase.total_waits - bse.total_waits,1) > 0
ORDER BY ase.snap_id;
Или так (с подзапросом):
SELECT
snap_id,
snap_time,
snap_duration,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class"
FROM
(
SELECT
sn.dbid,
sn.instance_number inst_id,
sn.snap_id snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
bse.event_name event_name,
bse.wait_class,
(ase.total_waits_fg - bse.total_waits_fg) total_waits,
(round(ase.time_waited_micro_fg/1000000, 2) - round(bse.time_waited_micro_fg/1000000, 2)) time_waited
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
dba_hist_snapshot sn
WHERE
ase.snap_id = sn.snap_id and
ase.dbid=sn.dbid and
ase.snap_id = bse.snap_id +1 and
bse.instance_number = ase.instance_number and
ase.instance_number = 1 and
ase.event_name = bse.event_name
)
WHERE
nvl(total_waits,1) > 0
-- event_name = 'db file sequential read' and
-- snap_id between 748 and 849
ORDER BY inst_id, snap_id;
Связываем с TIME MODEL:
SELECT
sn.snap_id,
sn.dbid,
sn.instance_number,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
(atm.value - btm.value) / 1000000 db_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
-- average active session = db time / snap duration
(((atm.value - btm.value) / 1000000) /
(extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)) aas,
bse.event_name,
(ase.total_waits - bse.total_waits) total_waits,
(ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits) avg_wait
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
--- add time model
dba_hist_sys_time_model btm,
dba_hist_sys_time_model atm,
dba_hist_snapshot sn
WHERE
bse.event_name = 'db file sequential read' and
ase.event_name = bse.event_name and
ase.snap_id = bse.snap_id + 1 and
ase.instance_number = 1 and
bse.instance_number = ase.instance_number and
ase.snap_id = sn.snap_id and
ase.instance_number = sn.instance_number and
nvl(ase.total_waits - bse.total_waits,1) > 0 and
--- add time model
atm.snap_id = btm.snap_id + 1 and
atm.dbid = btm.dbid and
atm.instance_number = btm.instance_number and
atm.stat_id = btm.stat_id and
atm.snap_id = sn.snap_id and
btm.stat_name = 'DB time'
ORDER BY ase.snap_id;
Но, существуют и аналитические функции:
SELECT
snap_id,
end_interval_time,
lead(snap_id, 1) over (order by snap_id desc) previous_snap_id,
lag(snap_id, 1) over (order by snap_id desc) next_snap_id
FROM
dba_hist_snapshot
WHERE
end_interval_time > sysdate-1
ORDER BY end_interval_time;
Используем partition by, если хотим разделить группы на подгруппы:
SELECT
snap_id,
end_interval_time,
lead(snap_id, 1) over (partition by startup_time order by snap_id desc) previous_snap_id,
lag(snap_id, 1) over (partition by startup_time order by snap_id desc) next_snap_id,
startup_time
FROM
dba_hist_snapshot
WHERE
end_interval_time > sysdate-1
ORDER BY end_interval_time;
-- Несколько примеров использования аналитических функций:
select snaptime "BeginTime",
event_name "Event",
wtdelta "Waits",
todelta "Timeouts",
twdelta "SecsWaited"
from (
select snap_id,snaptime,event_name,therank,
(waits-lag(waits,1,0)
over (partition by event_name order by snap_id)) wtdelta,
(timeouts-lag(timeouts,1,0)
over (partition by event_name order by snap_id)) todelta,
(time_waited-lag(time_waited,1,0)
over (partition by event_name order by snap_id)) twdelta
from (
select s.snap_id,
to_char(s.begin_interval_time,'DD-MON-RR HH24:MI') snaptime,
event_name, sum(e.total_waits) waits,
sum(e.total_timeouts) timeouts,
sum(e.time_waited_micro)/1000000 time_waited,
(rank() over (order by s.snap_id)) therank
from dba_hist_system_event e,
dba_hist_snapshot s
where s.snap_id = e.snap_id
and s.snap_id between (&&snap_low-1) and &&snap_hi
and s.dbid = e.dbid
and s.instance_number=e.instance_number
and e.event_name like 'logl%'
group by s.snap_id,
to_char(s.begin_interval_time,'DD-MON-RR HH24:MI'),event_name
)
order by snap_id, twdelta desc)
where therank > 1;
Пример использования функции LAG() :
SELECT
dbid,
btime,
round((time_ms_end - time_ms_beg) / nullif(count_end - count_beg,0),1) avg_ms
FROM (
SELECT
sn.dbid,
to_char(sn.begin_interval_time,'dd-mm-yyyy hh24:mi:ss') btime,
total_waits count_end,
time_waited_micro / 1000 time_ms_end,
LAG(se.time_waited_micro / 1000) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) time_ms_beg,
LAG(se.total_waits) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) count_beg
FROM
dba_hist_system_event se,
dba_hist_snapshot sn
WHERE
sn.snap_id=se.snap_id
and se.event_name in ('log file sync' )
and sn.dbid=se.dbid
)
ORDER BY btime;
Связываем с dba_hist_snapshot:
select
snap_id,
snap_time,
snap_duration,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class"
from
(select
sn.dbid,
sn.instance_number inst_id,
sn.snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
se.event_name,
se.wait_class,
se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
from dba_hist_system_event se,
dba_hist_snapshot sn
where se.instance_number=sn.instance_number
and se.snap_id=sn.snap_id
and se.dbid=sn.dbid
)
where
inst_id = 1 and
snap_id > min_snap_id and
nvl(total_waits,1) > 0 and
snap_id between 748 and 849 and
event_name = 'log file sync'
order by inst_id, snap_id;
Связываем с TIME MODEL:
select
snap_id,
snap_time,
snap_duration,
db_time,
aas,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class"
from
(select
sn.dbid,
sn.instance_number inst_id,
sn.snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
(tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 db_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
-- average active session = db time / snap duration
(((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 ) /
(extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)) aas,
se.event_name,
se.wait_class,
se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
from dba_hist_system_event se,
dba_hist_snapshot sn,
dba_hist_sys_time_model tm
where se.instance_number=sn.instance_number and
se.snap_id=sn.snap_id and
se.dbid=sn.dbid and
--- add time model
tm.snap_id = sn.snap_id and
tm.dbid = sn.dbid and
tm.instance_number = sn.instance_number and
tm.stat_name = 'DB time'
)
where
inst_id = 1 and
snap_id > min_snap_id and
nvl(total_waits,1) > 0
-- snap_id between 748 and 849 and
-- event_name = 'log file sync'
order by inst_id, snap_id;
Вывести не более 5-ти топовых ожиданий, на каждый снапшот:
select *
from (
select
inst_id,
snap_id,
snap_time,
snap_duration,
db_time,
aas,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class",
dense_rank() over (partition by inst_id, snap_id order by time_waited desc) - 1 wait_rank
from
(select
sn.dbid,
sn.instance_number inst_id,
sn.snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
(tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 db_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
-- average active session = db time / snap duration
(((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 ) /
(extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)) aas,
se.event_name,
se.wait_class,
se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
from dba_hist_system_event se,
dba_hist_snapshot sn,
dba_hist_sys_time_model tm
where se.instance_number=sn.instance_number and
se.snap_id=sn.snap_id and
se.dbid=sn.dbid and
--- add time model
tm.snap_id = sn.snap_id and
tm.dbid = sn.dbid and
tm.instance_number = sn.instance_number and
tm.stat_name = 'DB time'
)
where
inst_id = 1 and
snap_id > min_snap_id and
nvl(total_waits,1) > 0
)
where wait_rank <= 5
order by inst_id, snap_id;
SELECT
sn.snap_id,
sn.dbid,
sn.instance_number,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
he.event_name,
he.total_waits,
he.time_waited_micro
FROM
dba_hist_system_event he,
dba_hist_snapshot sn
WHERE
he.snap_id=sn.snap_id;
Средняя статистика за период (snap duration):
SELECT
sn.snap_id,
sn.dbid,
sn.instance_number,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
bse.event_name,
(ase.total_waits - bse.total_waits) total_waits,
(ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits) avg_wait
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
dba_hist_snapshot sn
WHERE
bse.event_name = 'db file sequential read' and
ase.event_name = bse.event_name and
ase.snap_id = bse.snap_id +1 and
ase.instance_number = 1 and
bse.instance_number = ase.instance_number and
ase.snap_id = sn.snap_id and
ase.instance_number = sn.instance_number and
nvl(ase.total_waits - bse.total_waits,1) > 0
ORDER BY ase.snap_id;
Или так (с подзапросом):
SELECT
snap_id,
snap_time,
snap_duration,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class"
FROM
(
SELECT
sn.dbid,
sn.instance_number inst_id,
sn.snap_id snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
bse.event_name event_name,
bse.wait_class,
(ase.total_waits_fg - bse.total_waits_fg) total_waits,
(round(ase.time_waited_micro_fg/1000000, 2) - round(bse.time_waited_micro_fg/1000000, 2)) time_waited
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
dba_hist_snapshot sn
WHERE
ase.snap_id = sn.snap_id and
ase.dbid=sn.dbid and
ase.snap_id = bse.snap_id +1 and
bse.instance_number = ase.instance_number and
ase.instance_number = 1 and
ase.event_name = bse.event_name
)
WHERE
nvl(total_waits,1) > 0
-- event_name = 'db file sequential read' and
-- snap_id between 748 and 849
ORDER BY inst_id, snap_id;
Связываем с TIME MODEL:
SELECT
sn.snap_id,
sn.dbid,
sn.instance_number,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
(atm.value - btm.value) / 1000000 db_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
-- average active session = db time / snap duration
(((atm.value - btm.value) / 1000000) /
(extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)) aas,
bse.event_name,
(ase.total_waits - bse.total_waits) total_waits,
(ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits) avg_wait
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
--- add time model
dba_hist_sys_time_model btm,
dba_hist_sys_time_model atm,
dba_hist_snapshot sn
WHERE
bse.event_name = 'db file sequential read' and
ase.event_name = bse.event_name and
ase.snap_id = bse.snap_id + 1 and
ase.instance_number = 1 and
bse.instance_number = ase.instance_number and
ase.snap_id = sn.snap_id and
ase.instance_number = sn.instance_number and
nvl(ase.total_waits - bse.total_waits,1) > 0 and
--- add time model
atm.snap_id = btm.snap_id + 1 and
atm.dbid = btm.dbid and
atm.instance_number = btm.instance_number and
atm.stat_id = btm.stat_id and
atm.snap_id = sn.snap_id and
btm.stat_name = 'DB time'
ORDER BY ase.snap_id;
Но, существуют и аналитические функции:
SELECT
snap_id,
end_interval_time,
lead(snap_id, 1) over (order by snap_id desc) previous_snap_id,
lag(snap_id, 1) over (order by snap_id desc) next_snap_id
FROM
dba_hist_snapshot
WHERE
end_interval_time > sysdate-1
ORDER BY end_interval_time;
Используем partition by, если хотим разделить группы на подгруппы:
SELECT
snap_id,
end_interval_time,
lead(snap_id, 1) over (partition by startup_time order by snap_id desc) previous_snap_id,
lag(snap_id, 1) over (partition by startup_time order by snap_id desc) next_snap_id,
startup_time
FROM
dba_hist_snapshot
WHERE
end_interval_time > sysdate-1
ORDER BY end_interval_time;
-- Несколько примеров использования аналитических функций:
select snaptime "BeginTime",
event_name "Event",
wtdelta "Waits",
todelta "Timeouts",
twdelta "SecsWaited"
from (
select snap_id,snaptime,event_name,therank,
(waits-lag(waits,1,0)
over (partition by event_name order by snap_id)) wtdelta,
(timeouts-lag(timeouts,1,0)
over (partition by event_name order by snap_id)) todelta,
(time_waited-lag(time_waited,1,0)
over (partition by event_name order by snap_id)) twdelta
from (
select s.snap_id,
to_char(s.begin_interval_time,'DD-MON-RR HH24:MI') snaptime,
event_name, sum(e.total_waits) waits,
sum(e.total_timeouts) timeouts,
sum(e.time_waited_micro)/1000000 time_waited,
(rank() over (order by s.snap_id)) therank
from dba_hist_system_event e,
dba_hist_snapshot s
where s.snap_id = e.snap_id
and s.snap_id between (&&snap_low-1) and &&snap_hi
and s.dbid = e.dbid
and s.instance_number=e.instance_number
and e.event_name like 'logl%'
group by s.snap_id,
to_char(s.begin_interval_time,'DD-MON-RR HH24:MI'),event_name
)
order by snap_id, twdelta desc)
where therank > 1;
Пример использования функции LAG() :
SELECT
dbid,
btime,
round((time_ms_end - time_ms_beg) / nullif(count_end - count_beg,0),1) avg_ms
FROM (
SELECT
sn.dbid,
to_char(sn.begin_interval_time,'dd-mm-yyyy hh24:mi:ss') btime,
total_waits count_end,
time_waited_micro / 1000 time_ms_end,
LAG(se.time_waited_micro / 1000) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) time_ms_beg,
LAG(se.total_waits) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) count_beg
FROM
dba_hist_system_event se,
dba_hist_snapshot sn
WHERE
sn.snap_id=se.snap_id
and se.event_name in ('log file sync' )
and sn.dbid=se.dbid
)
ORDER BY btime;
Связываем с dba_hist_snapshot:
select
snap_id,
snap_time,
snap_duration,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class"
from
(select
sn.dbid,
sn.instance_number inst_id,
sn.snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
se.event_name,
se.wait_class,
se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
from dba_hist_system_event se,
dba_hist_snapshot sn
where se.instance_number=sn.instance_number
and se.snap_id=sn.snap_id
and se.dbid=sn.dbid
)
where
inst_id = 1 and
snap_id > min_snap_id and
nvl(total_waits,1) > 0 and
snap_id between 748 and 849 and
event_name = 'log file sync'
order by inst_id, snap_id;
Связываем с TIME MODEL:
select
snap_id,
snap_time,
snap_duration,
db_time,
aas,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class"
from
(select
sn.dbid,
sn.instance_number inst_id,
sn.snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
(tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 db_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
-- average active session = db time / snap duration
(((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 ) /
(extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)) aas,
se.event_name,
se.wait_class,
se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
from dba_hist_system_event se,
dba_hist_snapshot sn,
dba_hist_sys_time_model tm
where se.instance_number=sn.instance_number and
se.snap_id=sn.snap_id and
se.dbid=sn.dbid and
--- add time model
tm.snap_id = sn.snap_id and
tm.dbid = sn.dbid and
tm.instance_number = sn.instance_number and
tm.stat_name = 'DB time'
)
where
inst_id = 1 and
snap_id > min_snap_id and
nvl(total_waits,1) > 0
-- snap_id between 748 and 849 and
-- event_name = 'log file sync'
order by inst_id, snap_id;
Вывести не более 5-ти топовых ожиданий, на каждый снапшот:
select *
from (
select
inst_id,
snap_id,
snap_time,
snap_duration,
db_time,
aas,
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
substr(wait_class, 1, 15) "Wait Class",
dense_rank() over (partition by inst_id, snap_id order by time_waited desc) - 1 wait_rank
from
(select
sn.dbid,
sn.instance_number inst_id,
sn.snap_id,
sn.begin_interval_time begin_snap,
sn.end_interval_time end_snap,
(sn.end_interval_time - sn.begin_interval_time) snap_interval,
to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
(tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 db_time,
extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
-- average active session = db time / snap duration
(((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id)) / 1000000 ) /
(extract (second from sn.end_interval_time - sn.begin_interval_time)
+ (extract (minute from sn.end_interval_time - sn.begin_interval_time)
+ (extract (hour from sn.end_interval_time - sn.begin_interval_time)
+ (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)) aas,
se.event_name,
se.wait_class,
se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
from dba_hist_system_event se,
dba_hist_snapshot sn,
dba_hist_sys_time_model tm
where se.instance_number=sn.instance_number and
se.snap_id=sn.snap_id and
se.dbid=sn.dbid and
--- add time model
tm.snap_id = sn.snap_id and
tm.dbid = sn.dbid and
tm.instance_number = sn.instance_number and
tm.stat_name = 'DB time'
)
where
inst_id = 1 and
snap_id > min_snap_id and
nvl(total_waits,1) > 0
)
where wait_rank <= 5
order by inst_id, snap_id;
Комментариев нет:
Отправить комментарий