cpuusage

col db_host for a20
set lin 450
with cpuusage as (
select a.inst_id, cpuusage , loadavg,cpucnt
from (
select inst_id, round(value,2) cpuusage
from gv$sysmetric
where metric_name ='Host CPU Utilization (%)' and group_id = 3 ) a, (
select inst_id, round(value,2) loadavg
from gv$osstat
where stat_name ='LOAD') b, (
select inst_id, value cpucnt
from gv$osstat
where stat_name ='NUM_CPUS') c
where a.inst_id = b.inst_Id and a.inst_id = c.inst_id ), redostat as (
SELECT inst_id, COUNT(*) logsw, round(sum(blocks * block_size )/(1048576*1000),0) RedoGb
FROM gV$archived_log a
where completion_time > SYSDATE - interval '60' minute and thread#=inst_id and dest_id = 1 group by inst_id ), iotime as (
select
---sys_context('userenv','DB_NAME') ||decode(         0,1,'-'||inst_id)  db_name,
inst_id , max(decode(name,'db file sequential read',avgms)) sequential, max(decode(name,'log file sync',avgms)) logfilesync, max(decode(name,'log file parallel write',avgms)) logprllWrt, max(decode(name,'db file parallel write',avgms)) dbprllWrt, max(decode(name,'log file sequential read',avgms)) logseqread, max(decode(name,'direct path read',avgms)) drread, max(decode(name,'direct path write',avgms)) drwrite, max(decode(name,'direct path read',avgms)) drreadtmp, max(decode(name,'direct path write',avgms)) drwritetmp, max(decode(name,'db file scattered read',avgms)) scattered
from (
select m.inst_id, n.name , round(m.time_waited,3) time_waited, m.wait_count, round(10*m.time_waited/nullif(m.wait_count,0),2) avgms
from gv$eventmetric m, gv$event_name n
where m.event_id=n.event_id and n.name in ( 'db file sequential read', 'db file scattered read', 'direct path read', 'direct path read temp', 'direct path write', 'direct path write temp', 'log file sync', 'log file sequential read', 'log file parallel write', 'db file parallel write' ) and m.wait_count > 0 and m.inst_id = n.inst_id order by 1,4 desc ) group by inst_id )
select sys_context('userenv','DB_NAME') ||decode( 0,1,'-'||a.inst_id)||'-'||sys_context('userenv','SERVER_HOST') db_host , sequential,logfilesync,logprllWrt,logseqread,dbprllWrt, drread,drwrite,drreadtmp,drwritetmp,
--scattered,
cpuusage,loadavg,logsw,redogb , CASE when sequential > 25 then '*high seq time*' when ( logfilesync > 25 or logprllWrt > 25 or logseqread > 25 ) and ( redogb > 2) then '*High LogSync Time*' when ( drread > 25 or drwrite > 25 or drreadtmp > 25 or drwritetmp > 25 ) then '*High Direct IO *' when ( (loadavg/cpucnt) > 1 ) then '*High Load*' when ( logsw > 20 ) then '*Too Many LogSwitches' ELSE ' ' end Comments
from iotime a, cpuusage b, redostat c
where a.inst_id = b.inst_id (+) and a.inst_id = c.inst_id (+) order by a.inst_id