Несколько последних снапшотов:
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
where end_interval_time between sysdate - interval '5' hour and sysdate
order by end_interval_time desc
Добавим DBTIME:
select * from (
select begin_snap,
end_snap,
timestamp begin_timestamp,
inst,
to_char(a/1000000/60, '99990.9999') dbtime_mins
from
(
select
e.snap_id end_snap,
s.end_interval_time end_time,
lag(e.snap_id) over (order by e.instance_number,e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.instance_number,e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.instance_number,e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time'
)
where end_time between sysdate - interval '10' hour and sysdate
-- order by begin_snap desc
)
Статистика по событию ожидания:
select event, round(sum(tm_delta_time)/1000000/60,1) delta_time_mint
,round(sum(tm_delta_cpu_time)/1000000/60,1) delta_cpu_mint
,round(sum(tm_delta_db_time)/1000000/60,1) delta_db_mint
from dba_hist_active_sess_history
where snap_id between 2716 and 2720 and
instance_number = 1 and event = 'db file sequential read'
group by event;
select * from (
select
WAIT_CLASS ,
EVENT,
count(sample_time) as EST_SECS_IN_WAIT
from v$active_session_history
where sample_time between sysdate - interval '10' hour and sysdate
group by WAIT_CLASS,EVENT
order by count(sample_time) desc
)
where rownum < 11;
Статистика по sql_id из awr:
select sql_id,
plan_hash_value,
sum(execs) execs,
-- sum(etime) etime,
sum(etime)/sum(execs) avg_etime,
sum(cpu_time)/sum(execs) avg_cpu_time,
sum(lio)/sum(execs) avg_lio,
sum(pio)/sum(execs) avg_pio
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
cpu_time_delta/1000000 cpu_time,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
-- and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5;
История плана запроса по awr:
select ss.snap_id,
ss.instance_number node,
begin_interval_time,
sql_id,
plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3;
Статистики по SQL:
SELECT
ss.snap_id, ss.instance_number node, ss.begin_interval_time, s.sql_id, s.plan_hash_value,
NVL(s.executions_delta,0) execs,
(s.elapsed_time_delta/DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
(s.buffer_gets_delta/DECODE(NVL(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio
FROM
dba_hist_sqlstat s, dba_hist_snapshot ss
WHERE
ss.snap_id = s.snap_id AND
ss.instance_number = s.instance_number AND
s.executions_delta > 0 AND
s.sql_id = '271w1sktujfqk'
ORDER BY 1, 2, 3;
TOP по ASH:
select * from (
select
SQL_ID ,
sum(decode(session_state,'ON CPU',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
from v$active_session_history
where SQL_ID is not NULL
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc
)where rownum < 11;
Активность сессий за последний час:
SELECT trunc(sample_time,'MI'),
sql_id,
count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
select
SQL_ID ,
sum(decode(session_state,'WAITING',1,1)) as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1)) desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id
order by trunc(sample_time,'MI') desc;
select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
(select min(sample_time) sample_time
from v$active_session_history ash
) start_time,
(select max(sample_time) sample_time
from v$active_session_history
) end_time,
v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;
select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
(select min(sample_time) sample_time
from v$active_session_history ash
where sample_time between sysdate-1/24 and sysdate) start_time,
(select max(sample_time) sample_time
from v$active_session_history
where sample_time between sysdate-1/24 and sysdate) end_time,
v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;
Кумулятивная статистика (с момента старта экземпляра):
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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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,
to_char((ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits),'99990.9999') avg_wait
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
dba_hist_snapshot sn
WHERE
sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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 sn.end_interval_time between sysdate - interval '1' hour and sysdate and
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
sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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.end_interval_time between sysdate - interval '3' hour and sysdate
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.end_interval_time between sysdate - interval '3' hour and sysdate and
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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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
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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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
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,
to_char(
(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, '99990.9999') db_time,
to_char(
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, '99990.9999') snap_duration,
-- average active session = db time / snap duration
to_char(
(((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)), '99990.9999') 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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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;
Статистика по сессиям:
RDBMS Version: 10g.
Script shows following session statistic values:
PGA Memory, in MB;
CPU, used by session;
Hard Parse, %;
Physical read bytes, in MB;
Physical write bytes, in MB;
Redo size, in MB;
Received from client, in MB;
Sent to client, in MB.
SELECT Logon_time,
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 25 AND v$sesstat.SID = v$session.sid)
AS "PGA Memory, in MB",
(SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 12 AND v$sesstat.SID = v$session.sid)
AS "CPU, used by session",
ROUND ( (SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 339 AND v$sesstat.SID = v$session.sid)
/ (SELECT DECODE (VALUE, 0, 1, VALUE)
FROM v$sesstat
WHERE STATISTIC# = 338 AND v$sesstat.SID = v$session.sid),
2)
AS "Hard Parse, %",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 58 AND v$sesstat.SID = v$session.sid)
AS "Physical read bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 66 AND v$sesstat.SID = v$session.sid)
AS "Physical write bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 139 AND v$sesstat.SID = v$session.sid)
AS "Redo size, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 344 AND v$sesstat.SID = v$session.sid)
AS "Received from client, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 343 AND v$sesstat.SID = v$session.sid)
AS "Sent to client, in MB",
SID,
SERIAL#,
v$session.STATUS,
PROGRAM,
USER#,
USERNAME,
COMMAND,
OWNERID,
OSUSER,
PROCESS,
MACHINE,
OBJECT_NAME
FROM v$session
LEFT OUTER JOIN
DBA_OBJECTS
ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
--AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;
Oracle 11g:
SELECT Logon_time,
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 35 AND v$sesstat.SID = v$session.sid)
AS "PGA Memory, in MB",
(SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 17 AND v$sesstat.SID = v$session.sid)
AS "CPU, used by session",
ROUND ( (SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 584 AND v$sesstat.SID = v$session.sid)
/ (SELECT DECODE (VALUE, 0, 1, VALUE)
FROM v$sesstat
WHERE STATISTIC# = 583 AND v$sesstat.SID = v$session.sid),
2)
AS "Hard Parse, %",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 83 AND v$sesstat.SID = v$session.sid)
AS "Physical read bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 96 AND v$sesstat.SID = v$session.sid)
AS "Physical write bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 185 AND v$sesstat.SID = v$session.sid)
AS "Redo size, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 590 AND v$sesstat.SID = v$session.sid)
AS "Received from client, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 589 AND v$sesstat.SID = v$session.sid)
AS "Sent to client, in MB",
SID,
SERIAL#,
v$session.STATUS,
PROGRAM,
USER#,
USERNAME,
COMMAND,
OWNERID,
OSUSER,
PROCESS,
MACHINE,
OBJECT_NAME
FROM v$session
LEFT OUTER JOIN
DBA_OBJECTS
ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
--AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
where end_interval_time between sysdate - interval '5' hour and sysdate
order by end_interval_time desc
Добавим DBTIME:
select * from (
select begin_snap,
end_snap,
timestamp begin_timestamp,
inst,
to_char(a/1000000/60, '99990.9999') dbtime_mins
from
(
select
e.snap_id end_snap,
s.end_interval_time end_time,
lag(e.snap_id) over (order by e.instance_number,e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.instance_number,e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.instance_number,e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time'
)
where end_time between sysdate - interval '10' hour and sysdate
-- order by begin_snap desc
)
Статистика по событию ожидания:
select event, round(sum(tm_delta_time)/1000000/60,1) delta_time_mint
,round(sum(tm_delta_cpu_time)/1000000/60,1) delta_cpu_mint
,round(sum(tm_delta_db_time)/1000000/60,1) delta_db_mint
from dba_hist_active_sess_history
where snap_id between 2716 and 2720 and
instance_number = 1 and event = 'db file sequential read'
group by event;
select * from (
select
WAIT_CLASS ,
EVENT,
count(sample_time) as EST_SECS_IN_WAIT
from v$active_session_history
where sample_time between sysdate - interval '10' hour and sysdate
group by WAIT_CLASS,EVENT
order by count(sample_time) desc
)
where rownum < 11;
Статистика по sql_id из awr:
select sql_id,
plan_hash_value,
sum(execs) execs,
-- sum(etime) etime,
sum(etime)/sum(execs) avg_etime,
sum(cpu_time)/sum(execs) avg_cpu_time,
sum(lio)/sum(execs) avg_lio,
sum(pio)/sum(execs) avg_pio
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
cpu_time_delta/1000000 cpu_time,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
-- and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5;
История плана запроса по awr:
select ss.snap_id,
ss.instance_number node,
begin_interval_time,
sql_id,
plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3;
Статистики по SQL:
SELECT
ss.snap_id, ss.instance_number node, ss.begin_interval_time, s.sql_id, s.plan_hash_value,
NVL(s.executions_delta,0) execs,
(s.elapsed_time_delta/DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
(s.buffer_gets_delta/DECODE(NVL(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio
FROM
dba_hist_sqlstat s, dba_hist_snapshot ss
WHERE
ss.snap_id = s.snap_id AND
ss.instance_number = s.instance_number AND
s.executions_delta > 0 AND
s.sql_id = '271w1sktujfqk'
ORDER BY 1, 2, 3;
TOP по ASH:
select * from (
select
SQL_ID ,
sum(decode(session_state,'ON CPU',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
from v$active_session_history
where SQL_ID is not NULL
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc
)where rownum < 11;
Активность сессий за последний час:
SELECT trunc(sample_time,'MI'),
sql_id,
count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
select
SQL_ID ,
sum(decode(session_state,'WAITING',1,1)) as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1)) desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id
order by trunc(sample_time,'MI') desc;
select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
(select min(sample_time) sample_time
from v$active_session_history ash
) start_time,
(select max(sample_time) sample_time
from v$active_session_history
) end_time,
v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;
select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
(select min(sample_time) sample_time
from v$active_session_history ash
where sample_time between sysdate-1/24 and sysdate) start_time,
(select max(sample_time) sample_time
from v$active_session_history
where sample_time between sysdate-1/24 and sysdate) end_time,
v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;
Кумулятивная статистика (с момента старта экземпляра):
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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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,
to_char((ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits),'99990.9999') avg_wait
FROM
dba_hist_system_event bse,
dba_hist_system_event ase,
dba_hist_snapshot sn
WHERE
sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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 sn.end_interval_time between sysdate - interval '1' hour and sysdate and
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
sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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.end_interval_time between sysdate - interval '3' hour and sysdate
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.end_interval_time between sysdate - interval '3' hour and sysdate and
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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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
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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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
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,
to_char(
(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, '99990.9999') db_time,
to_char(
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, '99990.9999') snap_duration,
-- average active session = db time / snap duration
to_char(
(((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)), '99990.9999') 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 sn.end_interval_time between sysdate - interval '3' hour and sysdate and
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;
Статистика по сессиям:
RDBMS Version: 10g.
Script shows following session statistic values:
PGA Memory, in MB;
CPU, used by session;
Hard Parse, %;
Physical read bytes, in MB;
Physical write bytes, in MB;
Redo size, in MB;
Received from client, in MB;
Sent to client, in MB.
SELECT Logon_time,
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 25 AND v$sesstat.SID = v$session.sid)
AS "PGA Memory, in MB",
(SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 12 AND v$sesstat.SID = v$session.sid)
AS "CPU, used by session",
ROUND ( (SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 339 AND v$sesstat.SID = v$session.sid)
/ (SELECT DECODE (VALUE, 0, 1, VALUE)
FROM v$sesstat
WHERE STATISTIC# = 338 AND v$sesstat.SID = v$session.sid),
2)
AS "Hard Parse, %",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 58 AND v$sesstat.SID = v$session.sid)
AS "Physical read bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 66 AND v$sesstat.SID = v$session.sid)
AS "Physical write bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 139 AND v$sesstat.SID = v$session.sid)
AS "Redo size, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 344 AND v$sesstat.SID = v$session.sid)
AS "Received from client, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 343 AND v$sesstat.SID = v$session.sid)
AS "Sent to client, in MB",
SID,
SERIAL#,
v$session.STATUS,
PROGRAM,
USER#,
USERNAME,
COMMAND,
OWNERID,
OSUSER,
PROCESS,
MACHINE,
OBJECT_NAME
FROM v$session
LEFT OUTER JOIN
DBA_OBJECTS
ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
--AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;
Oracle 11g:
SELECT Logon_time,
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 35 AND v$sesstat.SID = v$session.sid)
AS "PGA Memory, in MB",
(SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 17 AND v$sesstat.SID = v$session.sid)
AS "CPU, used by session",
ROUND ( (SELECT VALUE
FROM v$sesstat
WHERE STATISTIC# = 584 AND v$sesstat.SID = v$session.sid)
/ (SELECT DECODE (VALUE, 0, 1, VALUE)
FROM v$sesstat
WHERE STATISTIC# = 583 AND v$sesstat.SID = v$session.sid),
2)
AS "Hard Parse, %",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 83 AND v$sesstat.SID = v$session.sid)
AS "Physical read bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 96 AND v$sesstat.SID = v$session.sid)
AS "Physical write bytes, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 185 AND v$sesstat.SID = v$session.sid)
AS "Redo size, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 590 AND v$sesstat.SID = v$session.sid)
AS "Received from client, in MB",
(SELECT ROUND (VALUE / 1024 / 1024, 2)
FROM v$sesstat
WHERE STATISTIC# = 589 AND v$sesstat.SID = v$session.sid)
AS "Sent to client, in MB",
SID,
SERIAL#,
v$session.STATUS,
PROGRAM,
USER#,
USERNAME,
COMMAND,
OWNERID,
OSUSER,
PROCESS,
MACHINE,
OBJECT_NAME
FROM v$session
LEFT OUTER JOIN
DBA_OBJECTS
ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
--AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;
