Query perf2

 

set lin 450
col DB_HOST for a30
col CPUS for a5
col CONNDET for a10
col SWAPUSAGE for a10
col ActiveSess for a20
col CONNDET for a20
WITH PGAUSAGE as (
select inst_id, round(value/(1048576)) pgausage
from gv$pgastat
where name ='total PGA inuse' ), SGAUSAGE AS (
select inst_id, round(sum(bytes)/(1048576)) sgausage
from gv$sgastat group by inst_id ), SESSUSAGE AS (
select a.inst_id, ActiveSess, TotalSess, allowedSess
from (
select /*+ rule */inst_id, count(*) ActiveSess
from gv$session
where status ='ACTIVE' group by inst_id ) a ,
(select /*+ rule */ inst_id, count(*) TotalSess
from gv$session group by inst_id ) b , (
select inst_id, value allowedSess
from gv$parameter
where name ='processes' ) c
where a.inst_id = b.inst_id and a.inst_id = c.inst_Id ), PARAM AS (
select inst_id, name, value cpu_count
from gv$parameter
where name ='cpu_count' ), CPUUSAGE AS (
--- Intel CPU with hyperthreading - look at Exadata Monitoring PDF -2020
select inst_id, CASE WHEN value < 50 THEN value * 1.7 ELSE VALUE *0.3 END cpuusage
from gv$sysmetric
where metric_name ='Host CPU Utilization (%)' and group_id = 3 )
SELECT instance_name ||'-'|| substr(host_name,1,17) db_host,
--'p1krn4d2' env,
--sys_context('userenv','DB_NAME') ||decode(         0,1,'-'||b.inst_id)||'-'||sys_context('userenv','server_host')  db_host,
load,
---round(busytime/(busytime+idle) * 100,2) "%Cpu Busy" ,
round(cpuusage,2) "Cpu%", to_char(activesess,'999999') ActiveSess, lpad(TotalSess,5,' ')||'/'||lpad(AllowedSess,5,' ') ConnDet, pgausage, sgausage, case when cpus !=cpu_count then cpu_count||'/'||cpus ||'*' else cpus ||'' end cpus , memory "PhyMem-MB", decode(vmpagein+vmpageout, 0,'N','Y-'|| to_char(vmpagein+vmpageout)) SWapUsage, CASE when ( cpuusage > 60 ) then '*High Cpu Usage*' when ( (load/core) > 1 ) then '*High Load*' ELSE ' ' end Comments
from (
select dos.inst_id, stat_name, decode(stat_name, 'LOAD',value, 'NUM_CPUS',value, 'NUM_CPU_CORES',value, 'PHYSICAL_MEMORY_BYTES',value, 'VM_IN_BYTES', value, 'VM_OUT_BYTES', value , value) value
from gv$osstat dos ) a pivot ( sum( decode(stat_name, 'BUSY_TIME',round(value/100,3), 'USER_TIME',round(value/100,3), 'SYS_TIME',round(value/100,3) , 'IDLE_TIME',round(value/100,3), 'LOAD',round(value,2), 'IOWAIT_TIME',round(value/100,3), 'PHYSICAL_MEMORY_BYTES' , round(value/(1048576*1024),0), 'VM_IN_BYTES',round(value/1048576), 'VM_OUT_BYTES',round(value/1048576), 'NUM_CPUS', value, 'NUM_CPU_CORES', value)) for stat_name in ( 'NUM_CPUS' AS CPUS, 'NUM_CPU_CORES' AS CORE, 'BUSY_TIME' as busyTime, 'USER_TIME' as userTime, 'SYS_TIME' as systime, 'IDLE_TIME' as Idle , 'LOAD' as load , 'PHYSICAL_MEMORY_BYTES' as Memory, 'IOWAIT_TIME' as IOtime, 'VM_IN_BYTES' as VMPAGEIN, 'VM_OUT_BYTES' as VMPAGEOUT ) )b , pgausage,sgausage ,cpuusage , SESSUSAGE, gv$instance i, param p
where pgausage.inst_id = b.inst_id and sgausage.inst_id = b.inst_id and cpuusage.inst_id = b.inst_id and i.inst_id = b.inst_id and sessusage.inst_id = b.inst_id and sessusage.inst_id = p.inst_id
--and   SESSUSAGE.inst_id = b.inst_id
order by b.inst_id