osload

col db_host for a20
col TOPRD for a20
col TOPWRT for a8
set lin 450
WITH osload as (
select a.inst, a.snap_id, 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%", cpus, round(load) osload, host_name, memory "PhyMem-GB"
from (
select dos.instance_number inst, dos.snap_id, stat_name, di.INSTANCE_NAME, di.host_name , 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_DATABASE_INSTANCE di
where dos.snap_id between &&begin_snap_id and &&end_snap_id and dos.INSTANCE_NUMBER = di.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
where busytime+idle > 0 and a.snap_id > &&begin_snap_id - 1 ), totio as (
select snap_id, inst, round((readMb - lag( ReadMB) over (partition by inst order by inst, snap_id))/(1024),0) TotRead , round((writeMb - lag( writeMb) over (partition by inst order by inst, snap_id))/(1024),0) TotWrt , round((readMb - lag( ReadMB) over (partition by inst order by inst, snap_id))/(1024),0) + round((writeMb - lag( writeMb) over (partition by inst order by inst, snap_id))/(1024),0) TotIO
from (
select snap_id, instance_number inst, sum(large_read_megabytes+small_read_megabytes ) ReadMB, sum(large_write_megabytes+small_write_megabytes ) WriteMB
from dba_hist_IOSTAT_FUNCTION
where snaP_id between &&begin_snap_id - 1 and &&end_snap_id group by snap_id, instance_number ) ), TOPIO as (
select inst, snap_id, decode(round(dirread,0),0,0,round(dirread,2)) dirread, round(write,2) write,name, rank () over ( partition by inst, snap_id order by dirread desc ) as rdrnk, rank () over ( partition by inst, snap_id order by Write desc ) as wtrnk
from (
select snap_id, inst, name, (readMb - lag( ReadMB) over (partition by name order by inst, snap_id))/(1024) DirRead , (writeMb - lag( writeMb) over (partition by name order by inst, snap_id))/(1024) Write
from (
select snap_id, instance_number inst, function_name name, sum(large_read_megabytes+small_read_megabytes ) ReadMB, sum(large_write_megabytes+small_write_megabytes ) WriteMB , wait_time
from dba_hist_IOSTAT_FUNCTIOn a
where snaP_id between &&begin_snap_id and &&end_snap_id group by snap_id, instance_number , function_name, wait_time order by 4 ) ) ), TOPWRT as (
select inst, snap_id, name||'-'||round(write,0) topwrt
from topio
where wtrnk=1 and write >0 ) , TOPRD as (
select inst, snap_id, name||'-'||round(dirread,0) toprd
from topio
where rdrnk=1 and dirread >0 ), rmanlgwr as (
select snap_id, inst, max(decode(function_id,0,IO)) RMAN, max(decode(function_id,2,IO)) "LGWR"
from (
select snap_id, instance_number inst,function_id, round( (sum(large_write_megabytes+small_write_megabytes + large_read_megabytes+small_read_megabytes ) - lag ( sum(large_write_megabytes+small_write_megabytes + large_read_megabytes+small_read_megabytes ) ) over (partition by functioN_id order by instance_number, snap_id) ) /1024,0) IO
from dba_hist_IOSTAT_FUNCTION
where snaP_id between &&begin_snap_id - 1 and &&end_snap_id and function_id in ( 0,2) group by snap_id, instance_number, function_id ) group by inst,snap_id ), iops as (
select snap_id, instance_number inst, round ( ( max(decode(stat_name,'physical read total IO requests' , value ) ) + max(decode(stat_name,'physical write total IO requests' , value ) ) ) /max(awrsecs), 1) "IOPS", round ( ( max(decode(stat_name,'physical write total bytes' , value ) ) + max(decode(stat_name,'physical read total bytes' , value ) ) ) /1048576/max(awrsecs), 1) "throughput-MBs"
from (
SELECT dos.instance_number, dos.snap_id, stat_name, begin_interval_time, (cast(END_INTERVAL_TIME as date) - cast(BEGIN_INTERVAL_TIME as date)) * 86400 awrsecs, value - lag( value) over ( partition by stat_name order by dos.instance_number,dos.snap_id ) value
FROM sys.wrh$_sysstat dos, dba_hist_snapshot dh, sys.wrh$_stat_name ws
Where dos.snap_id between &&begin_snap_id and &&end_snap_id and dos.snap_id = dh.snap_id and dos.instance_number = dh.instance_number and dos.stat_id = ws.stat_id and stat_name in ( 'physical read total IO requests', 'physical write total IO requests', 'physical write total bytes', 'physical read total bytes' ) order by stat_name, snap_id ) group by snap_id , instance_number, to_char(begin_interval_time, 'DD-MM-YY HH24:MI') )
select sys_context('userenv','DB_NAME') ||'-'||inst_id ||'-'|| host_name db_host, dio.snap_id, timeO,osload load, "CPU%USED", "WIO%", max(decode(event_name,'db file sequential read',av_wait_ms)) "IdxRd-ms", max(decode(event_name,'log file sync',av_wait_ms)) "LogSync", max(decode(event_name,'log file parallel write',av_wait_ms)) "LogPrllWrt", max(decode(event_name,'log file sequential read',av_wait_ms)) "LogSeqRd",
max(decode(event_name,'db file parallel write',av_wait_ms)) "dbPrllWrt",
--max(decode(event_name,'direct path read',av_wait_ms)) "DrRd",
--max(decode(event_name,'direct path read temp',av_wait_ms)) "DrRdTmp",
--max(decode(event_name,'direct path write temp',av_wait_ms)) "DrWtTmp",
--max(decode(event_name,'db file scattered read',av_wait_ms)) "Full-ms"
--max(decode(event_name,'direct path write',av_wait_ms)) "DrWrt",
RMAN, totio "IO-GB", toprd , topwrt
from (
select instance_number inst_id, event_name , snap_id , waits_delta , round(waits_Sec,0) waits_sec , to_char(decode(waits_delta, 0, 0, round(waits_sec*1000/waits_delta,2)),'9999.99') av_wait_ms, end_interval_time Timeo
from (
select s1.instance_number,s1.event_name , s1.snap_id,
--to_char(h.begin_interval_time, 'dd-mon-yy hh24:mi') || ' to ' || to_char(h.end_interval_time, 'hh24:mi') snap_int,
to_char(h.begin_interval_time, 'DD-MM-YY hh24:mi') end_interval_time, lag(total_waits) over(order by s1.snap_id) prev_waits, total_waits - lag(total_waits) over(partition by event_name order by s1.instance_number,s1.snap_id) as waits_delta, (TIME_WAITED_MICRO - lag(TIME_WAITED_MICRO) over(partition by event_name order by s1.instance_number,s1.snap_id))/1000000 as waits_sec
from dba_hist_system_event s1, dba_hist_snapshot h
where s1.snap_id between &&begin_snap_id - 1 and &&end_snap_id and h.snap_id = s1.snap_id and s1.INSTANCE_NUMBER = h.INSTANCE_NUMBER and s1.event_name in ( 'db file sequential read', 'db file scattered read', 'db file parallel write', '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' ) )
where prev_waits is not null and waits_delta is not null and waits_sec > 10 ) dio, osload , totio , rmanlgwr ,topio, toprd ,topwrt, iops
where dio.snap_id > &&begin_snap_id - 1 and dio.snap_id = osload.snap_id and dio.snap_id = totio.snap_id and dio.snap_id = toprd.snap_id and dio.snap_id = topwrt.snap_id and dio.inst_id = osload.inst and dio.inst_id = totio.inst and dio.inst_id = toprd.inst and dio.inst_id = topwrt.inst and dio.inst_id = iops.inst and dio.inst_id = rmanlgwr.inst(+) and dio.snap_id = rmanlgwr.snap_id(+) group by dio.inst_id, dio.snap_id,timeo,osload,totio , rman , topwrt, toprd, "CPU%USED","WIO%",host_name order by dio.inst_id, dio.snap_id