select
*
from
(
select
x.*
&day_avg1
from
(
select
to_char(gs.end_time,'Day') day_of_week,
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','Logical Reads Per Sec','Physical Read Total Bytes Per Sec','Physical Write Total Bytes Per Sec')
) gs
union all
select
to_char(dhs.end_interval_time,'Day') day_of_week,
dhs.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','Logical Reads Per Sec','Physical Read Total Bytes Per Sec','Physical Write Total Bytes 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%",'Logical Reads Per Sec' buff_read,'Physical Read Total Bytes Per Sec' phys_read,'Physical Write Total Bytes Per Sec' phys_write)
)
order by
cast(end_interval_time as date) desc,
instance_number |
select
*
from
(
select
x.*
&day_avg1
from
(
select
to_char(gs.end_time,'Day') day_of_week,
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','Logical Reads Per Sec','Physical Read Total Bytes Per Sec','Physical Write Total Bytes Per Sec')
) gs
union all
select
to_char(dhs.end_interval_time,'Day') day_of_week,
dhs.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','Logical Reads Per Sec','Physical Read Total Bytes Per Sec','Physical Write Total Bytes 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%",'Logical Reads Per Sec' buff_read,'Physical Read Total Bytes Per Sec' phys_read,'Physical Write Total Bytes Per Sec' phys_write)
)
order by
cast(end_interval_time as date) desc,
instance_number |
