Получение значений метрик из AWR

select
os.value,
hss.begin_interval_time
from
DBA_HIST_OSSTAT os,
dba_hist_snapshot hss
where
os.snap_id = hss.snap_id
and os.stat_name = ‘LOAD’ — AVG_BUSY_TIME, AVG_USER_TIME, AVG_SYS_TIME, AVG_IOWAIT_TIME
order by
begin_interval_time

select
begin_time,
value
from DBA_HIST_SYSMETRIC_HISTORY
where
metric_name = ‘User Transaction Per Sec’
order by
begin_time

— User Transaction Per Sec
— Physical Reads Per Sec
— Physical Writes Per Sec
— Logons Per Sec
— User Calls Per Sec
— Current Open Cursors Count
— SQL Service Response Time
— Executions Per Sec

select
hss.begin_interval_time,
(tm_cpu_cur.value – tm_cpu_prev.value)/1000000/(cast(hss.end_interval_time as date) – cast(hss.begin_interval_time as date) )/24/60/60 db_cpu_per_sec,
(tm_time_cur.value – tm_time_prev.value)/1000000/(cast(hss.end_interval_time as date) – cast(hss.begin_interval_time as date) )/24/60/60 db_time_per_sec
from
DBA_HIST_SYS_TIME_MODEL tm_cpu_cur,
DBA_HIST_SYS_TIME_MODEL tm_time_cur,
DBA_HIST_SYS_TIME_MODEL tm_cpu_prev,
DBA_HIST_SYS_TIME_MODEL tm_time_prev,
dba_hist_snapshot hss
where
tm_cpu_cur.snap_id = hss.snap_id
and tm_time_cur.snap_id = hss.snap_id
and tm_cpu_prev.snap_id = hss.snap_id – 1
and tm_time_prev.snap_id = hss.snap_id – 1
and tm_cpu_cur.stat_name = ‘DB CPU’ — ‘DB time’, ‘PL/SQL compilation elapsed time’, ‘sql execute elapsed time’
and tm_time_cur.stat_name = ‘DB time’
and tm_cpu_prev.stat_name = ‘DB CPU’ — ‘DB time’, ‘PL/SQL compilation elapsed time’, ‘sql execute elapsed time’
and tm_time_prev.stat_name = ‘DB time’
order by
hss.begin_interval_time