alter session set container=cdb$root;
select * from (
WITH
cpuwl AS (
SELECT /*+ MATERIALIZE NO_MERGE */
instance_number,
dbid,
snap_id,
SUM(CASE WHEN stat_name = 'RSRC_MGR_CPU_WAIT_TIME' THEN value ELSE 0 END) rsrcmgr,
SUM(CASE WHEN stat_name = 'LOAD' THEN value ELSE 0 END) loadavg,
SUM(CASE WHEN stat_name = 'NUM_CPUS' THEN value ELSE 0 END) cpu
FROM dba_hist_osstat
WHERE stat_name IN
('RSRC_MGR_CPU_WAIT_TIME','LOAD','NUM_CPUS')
-- and snap_id in (251,252,253,254)
GROUP BY
instance_number,
dbid,
snap_id
)
select a.dbid, a.instance_number, TO_CHAR(a.begin_interval_time,'MM/DD/YY HH24:MI:SS') tm,
to_char(a.begin_interval_time, 'yyyy-mm-dd') as logdate,
to_char(a.begin_interval_time, 'mm') as logmonth,
to_char(a.begin_interval_time, 'hh24') as loghour,
lag(b.snap_id) over(partition by b.instance_number,b.dbid order by b.snap_id) as snap_id,
b.cpu AS cpu,
round(b.loadavg,2) AS loadavg,
round((( b.rsrcmgr-lag(b.rsrcmgr) over (partition by b.instance_number,b.dbid order by b.snap_id) ) / 100) / (((CAST(a.end_interval_time AS DATE) - CAST(a.begin_interval_time AS DATE)) * 86400)*b.cpu)*100,2) as rsrcmgrpct
from dba_hist_snapshot a, cpuwl b
where a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.snap_id = b.snap_id
)
unpivot (value for metric_name in (cpu as 'NUM_CPUS', loadavg as 'LOAD', rsrcmgrpct as 'RSRC_MGR_CPU_WAIT_TIME_PCT'))
union all
select a.dbid, a.instance_number, TO_CHAR(a.begin_interval_time,'MM/DD/YY HH24:MI:SS') tm,
to_char(a.begin_interval_time, 'yyyy-mm-dd') as logdate,
to_char(a.begin_interval_time, 'mm') as logmonth,
to_char(a.begin_interval_time, 'hh24') as loghour,
lag(b.snap_id) over(partition by b.metric_name,b.instance_number,b.dbid order by b.snap_id) as snap_id,
b.metric_name,
case when b.metric_name = 'Host CPU Utilization (%)' then
case when b.average < 50 then (b.average*1.7)
else (85+(b.average-50)*0.3)
end
when b.metric_name = 'Average Active Sessions' then b.average
when b.metric_name = 'Current OS Load' then b.average
end as value
from dba_hist_snapshot a, DBA_HIST_SYSMETRIC_SUMMARY b
where a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.snap_id = b.snap_id
and b.metric_name in ('Host CPU Utilization (%)','Average Active Sessions','Current OS Load')
--and b.metric_name in ('Host CPU Utilization (%)','Current OS Load')
--where metric_name in ('Host CPU Utilization (%)','Current OS Load','CPU Usage Per Sec','Host CPU Usage Per Sec','Background CPU Usage Per Sec','Average Active Sessions')
-- and a.snap_id in (251,252,253,254)
order by 7,8 asc
/
alter session set container=cdb$root;
--Def v_secs=60 -- bucket size
select
to_char(to_date( trunc((id*60)/ (24*60*60)) || ' ' || mod((id*60), 24*60*60) , 'J SSSSS' )-(1/1440), 'MON DD YYYY HH24:MI') start_time,
trunc((id*60)/ (24*60*60)) start_time,
mod((id*60), 24*60*60) x,
round(CPU/60,2) ASH_CPU,
round(total/60,2) ASH_AAS
from (
select
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60) id,
sum(decode(session_state,'ON CPU',1,0)) CPU,
sum(decode(session_state,'ON CPU',0,1)) Wait,
count(*) total
from
v$active_session_history ash
where SAMPLE_TIME > sysdate - 60/(24*60)
group by
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60)
);
select DBTT.begin_time , DBAAS.DBAAS, ASH_CPU, DBT_CPU , ASH_AAS, DBT_AAS from
(
select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
round(value,2) DBAAS
from v$sysmetric_history
where metric_name='Average Active Sessions'
and INTSIZE_CSEC > 2000
order by 1
) DBAAS,
(
select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
round(value/100,2) DBT_AAS
from v$sysmetric_history
where metric_name='Database Time Per Sec'
and INTSIZE_CSEC > 2000
order by 1
) DBTT,
(
select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
round(value/100,2) DBT_CPU
from v$sysmetric_history
where metric_name='CPU Usage Per Sec'
) DBTC,
(
select
to_char(to_date(
trunc((id*60)/ (24*60*60)) || ' ' || -- Julian days
mod((id*60), 24*60*60) -- seconds in the day
, 'J SSSSS' )-(1/1440), 'MON DD YYYY HH24:MI') start_time,
round(CPU/60,2) ASH_CPU,
round(total/60,2) ASH_AAS
from (
select
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60) id,
sum(decode(session_state,'ON CPU',1,0)) CPU,
sum(decode(session_state,'ON CPU',0,1)) Wait,
-- decode(session_state,'ON CPU','ON CPU','WAIT') event,
count(*) total
from
v$active_session_history ash
where SAMPLE_TIME > sysdate - 60/(24*60)
group by
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60)
)
) AAS
where
DBTT.begin_time=DBTC.begin_time
and DBTT.begin_time=DBAAS.begin_time
and DBTT.begin_time=AAS.start_time (+)
order by DBTT.begin_time ;
alter session set container=cdb$root;
--Def v_secs=60 -- bucket size
select
to_char(to_date( trunc((id*60)/ (24*60*60)) || ' ' || mod((id*60), 24*60*60) , 'J SSSSS' )-(1/1440), 'MON DD YYYY HH24:MI') start_time,
trunc((id*60)/ (24*60*60)) start_time,
mod((id*60), 24*60*60) x,
round(CPU/60,2) ASH_CPU,
round(total/60,2) ASH_AAS
from (
select
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60) id,
sum(decode(session_state,'ON CPU',1,0)) CPU,
sum(decode(session_state,'ON CPU',0,1)) Wait,
count(*) total
from
v$active_session_history ash
where SAMPLE_TIME > sysdate - 60/(24*60)
group by
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60)
);
select DBTT.begin_time , DBAAS.DBAAS, ASH_CPU, DBT_CPU , ASH_AAS, DBT_AAS from
(
select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
round(value,2) DBAAS
from v$sysmetric_history
where metric_name='Average Active Sessions'
and INTSIZE_CSEC > 2000
order by 1
) DBAAS,
(
select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
round(value/100,2) DBT_AAS
from v$sysmetric_history
where metric_name='Database Time Per Sec'
and INTSIZE_CSEC > 2000
order by 1
) DBTT,
(
select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
round(value/100,2) DBT_CPU
from v$sysmetric_history
where metric_name='CPU Usage Per Sec'
) DBTC,
(
select
to_char(to_date(
trunc((id*60)/ (24*60*60)) || ' ' || -- Julian days
mod((id*60), 24*60*60) -- seconds in the day
, 'J SSSSS' ), 'MON DD YYYY HH24:MI') start_time,
round(CPU/60,2) ASH_CPU,
round(total/60,2) ASH_AAS
from (
select
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60) id,
sum(decode(session_state,'ON CPU',1,0)) CPU,
sum(decode(session_state,'ON CPU',0,1)) Wait,
-- decode(session_state,'ON CPU','ON CPU','WAIT') event,
count(*) total
from
v$active_session_history ash
where SAMPLE_TIME > sysdate - 60/(24*60)
group by
trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/60)
)
) AAS
where
DBTT.begin_time=DBTC.begin_time
and DBTT.begin_time=DBAAS.begin_time
and DBTT.begin_time=AAS.start_time (+)
order by DBTT.begin_time ;
