sgausage

set lin 450
col db_name for a20
col swap for a15
with sgausage as (
select instance_number inst, snap_id, round(sum(bytes)/(1048576)) sgausage
from dba_hist_sgastat
where snap_id between &&begin_snap_id-1 and &&end_snap_id group by instance_Number, snap_id ) , pgausage as (
select snap_id, instance_number inst, round(value /(1048576)) pgausage
from dba_hist_pgastat
where name ='total PGA inuse' )
select sys_context('userenv','DB_NAME') ||decode(1,1,'-'||a.instance_number) db_name , a.snap_id, to_char(begin_interval_time,'DD-MON-YY HH24:MI') TimeO , to_char(load,'999.00') load, to_char(busytime/(busytime+idle) *100,'99.00') "Cpu%Used" , to_char(usertime/(busytime+idle) *100,'99.00') "%USR", to_char(systime/(busytime+idle) *100,'99.00') "%SYS", to_char(idle/(busytime+idle )*100,'99.00') "%Idle" , to_char(IOtime/(busytime+idle) *100,'99.00') "%WIO",
--busytime "Busy(CpuSec)",
--idle "Idle(CpuSec)" ,
--busytime+idle  "Total(CpuSec)",
--vmpagein+ vmpageout,
sgausage, pgausage, cpus, memory "PhyMem-GB", decode( vmpagein+vmpageout,0,'', 'Y- '||to_char(vmpagein+vmpageout)) swap
--busytime+idle TotCpu_Sec,
--cpus* awrsecs
from (
select dos.instance_number, dos.snap_id, stat_name, begin_interval_time,
--value,
(cast(END_INTERVAL_TIME as date) - cast(BEGIN_INTERVAL_TIME as date)) * 86400 awrsecs, decode(stat_name,'LOAD',value,'NUM_CPUS',value,'PHYSICAL_MEMORY_BYTES',value, value - lag( value) over ( partition by stat_name order by dos.instance_number,dos.snap_id ) ) value
from dba_hist_osstat dos, dba_hist_snapshot dh
where dos.snap_id between &&begin_snap_id - 1 and &&end_snap_id and dos.snap_id = dh.snap_id and dos.instance_number = dh.instance_number ) 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),0), 'VM_IN_BYTES',round(value/1048576), 'VM_OUT_BYTES',round(value/1048576), 'NUM_CPUS', value)) for stat_name in ( 'NUM_CPUS' AS CPUS, '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 )) a ,sgausage, pgausage
where busytime+idle > 0 and a.snap_id > &&begin_snap_id - 1 and a.snap_id = sgausage.snap_id and a.snap_id = pgausage.snap_id and a.instance_number = pgausage.inst and a.instance_Number = sgausage.inst order by a.instance_number , a.snap_id