DBA AWR System Metrics Summary

select
*
from
(
select
x.*
&day_avg1
from
(
select
to_char(xxen_util.client_time(gs.end_time),'Day') day_of_week,
xxen_util.client_time(gs.end_time) end_interval_time,
gs.inst_id instance_number,
gs.metric_name,
gs.unit_factor*gs.value average_,
null maxval_
from
(
select
gs.*,
case
when gs.metric_unit like 'Blocks %' or gs.metric_unit like 'Reads %' or gs.metric_unit like 'Writes %' then vp.value/1000000
when gs.metric_unit like '%Bytes%' then 1/1000000
else 1 end unit_factor
from
(select vp.value from v$parameter vp where vp.name='db_block_size') vp,
(select x.* from (select max(gs.intsize_csec) over (partition by gs.inst_id, gs.metric_id) max_intsize_csec, gs.* from sys.gv_$sysmetric gs) x where x.intsize_csec=x.max_intsize_csec) gs
where
gs.metric_name in (
'Host CPU Utilization (%)',
'Database Wait Time Ratio',
'Average Active Sessions',
'Logical Reads Per Sec',
'Physical Read Total Bytes Per Sec',
'Physical Write Total Bytes Per Sec',
'Redo Generated Per Sec',
'Redo Writes Per Sec',
'User Commits Per Sec',
'Hard Parse Count Per Sec',
'Network Traffic Volume Per Sec'
)
) gs
union all
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,
dhss.instance_number,
dhss.metric_name,
dhss.average_,
dhss.maxval_
from
dba_hist_snapshot dhs,
(
select
dhss.unit_factor*dhss.average average_,
dhss.unit_factor*dhss.maxval maxval_,
dhss.*
from
(
select
case
when dhss.metric_unit like 'Blocks %' or dhss.metric_unit like 'Reads %' or dhss.metric_unit like 'Writes %' then vp.value/1000000
when dhss.metric_unit like '%Bytes%' then 1/1000000
else 1 end unit_factor,
dhss.*
from
v$parameter vp,
dba_hist_sysmetric_summary dhss
where
vp.name='db_block_size' and
dhss.metric_name in (
'Host CPU Utilization (%)',
'Database Wait Time Ratio',
'Average Active Sessions',
'Logical Reads Per Sec',
'Physical Read Total Bytes Per Sec',
'Physical Write Total Bytes Per Sec',
'Redo Generated Per Sec',
'Redo Writes Per Sec',
'User Commits Per Sec',
'Hard Parse Count Per Sec',
'Network Traffic Volume Per Sec'
)
) dhss
) dhss
where
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.snap_id=dhss.snap_id and
dhs.dbid=dhss.dbid and
dhs.instance_number=dhss.instance_number
) x
)
pivot (
sum(average_) avg,
sum(maxval_) max
&day_avg2
for
metric_name in (
'Host CPU Utilization (%)' "CPU%",
'Database Wait Time Ratio' "WAIT%",
'Average Active Sessions' act_sess,
'Logical Reads Per Sec' buff_read,
'Physical Read Total Bytes Per Sec' phys_read,
'Physical Write Total Bytes Per Sec' phys_write,
'Redo Generated Per Sec' redo_rate,
'Redo Writes Per Sec' redo_writes,
'User Commits Per Sec' commits,
'Hard Parse Count Per Sec' hard_parses,
'Network Traffic Volume Per Sec' network_traffic
)
)
order by
cast(end_interval_time as date) desc,
instance_number

select * from ( select x.* &day_avg1 from ( select to_char(xxen_util.client_time(gs.end_time),'Day') day_of_week, xxen_util.client_time(gs.end_time) end_interval_time, gs.inst_id instance_number, gs.metric_name, gs.unit_factor*gs.value average_, null maxval_ from ( select gs.*, case when gs.metric_unit like 'Blocks %' or gs.metric_unit like 'Reads %' or gs.metric_unit like 'Writes %' then vp.value/1000000 when gs.metric_unit like '%Bytes%' then 1/1000000 else 1 end unit_factor from (select vp.value from v$parameter vp where vp.name='db_block_size') vp, (select x.* from (select max(gs.intsize_csec) over (partition by gs.inst_id, gs.metric_id) max_intsize_csec, gs.* from sys.gv_$sysmetric gs) x where x.intsize_csec=x.max_intsize_csec) gs where gs.metric_name in ( 'Host CPU Utilization (%)', 'Database Wait Time Ratio', 'Average Active Sessions', 'Logical Reads Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec', 'Redo Generated Per Sec', 'Redo Writes Per Sec', 'User Commits Per Sec', 'Hard Parse Count Per Sec', 'Network Traffic Volume Per Sec' ) ) gs union all 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, dhss.instance_number, dhss.metric_name, dhss.average_, dhss.maxval_ from dba_hist_snapshot dhs, ( select dhss.unit_factor*dhss.average average_, dhss.unit_factor*dhss.maxval maxval_, dhss.* from ( select case when dhss.metric_unit like 'Blocks %' or dhss.metric_unit like 'Reads %' or dhss.metric_unit like 'Writes %' then vp.value/1000000 when dhss.metric_unit like '%Bytes%' then 1/1000000 else 1 end unit_factor, dhss.* from v$parameter vp, dba_hist_sysmetric_summary dhss where vp.name='db_block_size' and dhss.metric_name in ( 'Host CPU Utilization (%)', 'Database Wait Time Ratio', 'Average Active Sessions', 'Logical Reads Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec', 'Redo Generated Per Sec', 'Redo Writes Per Sec', 'User Commits Per Sec', 'Hard Parse Count Per Sec', 'Network Traffic Volume Per Sec' ) ) dhss ) dhss where 1=1 and dhs.dbid=(select vd.dbid from v$database vd) and dhs.snap_id=dhss.snap_id and dhs.dbid=dhss.dbid and dhs.instance_number=dhss.instance_number ) x ) pivot ( sum(average_) avg, sum(maxval_) max &day_avg2 for metric_name in ( 'Host CPU Utilization (%)' "CPU%", 'Database Wait Time Ratio' "WAIT%", 'Average Active Sessions' act_sess, 'Logical Reads Per Sec' buff_read, 'Physical Read Total Bytes Per Sec' phys_read, 'Physical Write Total Bytes Per Sec' phys_write, 'Redo Generated Per Sec' redo_rate, 'Redo Writes Per Sec' redo_writes, 'User Commits Per Sec' commits, 'Hard Parse Count Per Sec' hard_parses, 'Network Traffic Volume Per Sec' network_traffic ) ) order by cast(end_interval_time as date) desc, instance_number