Script：I/O monitor for last one minute

该脚本作用是从数据库层面监控最近1分钟的I/O情况，包括Iops、Mbps、I/O latency、TOP I/O sql等等。

col time for a30
col metric for a70
col username for a50
set lines 200 pages 20000
prompt
prompt ****************************************************************************************
prompt recent I/o evnet latency check
prompt ****************************************************************************************
select to_char(begin_time,'hh24:mi:ss')||'-'||to_char(end_time,'hh24:mi:ss') "time",inst_id,
       n.name event,
       m.wait_count  cnt,
       10*m.time_waited total_ms,
       nvl(round(10*m.time_waited/nullif(m.wait_count,0),3) ,0) avg_ms
  from gv$eventmetric m,
       v$event_name n
  where m.event_id=n.event_id
        and wait_class_id in (1740759767,4108307767)
        and m.wait_count > 0 order by 2,1,6;

prompt ****************************************************************************************
prompt recent Iops/Mbps check
prompt ****************************************************************************************
select * from
 (select to_char(begin_time,'hh24:mi:ss')||'-'||to_char(end_time,'hh24:mi:ss') "time",metric_name||' - '||metric_unit "metric",inst_id,round(value,1) value
  from gv$sysmetric  where metric_name in ('I/O Megabytes per Second','I/O Requests per Second','Average Synchronous Single-Block Read Latency')
  or metric_name like '%Physical Reads%Per Sec%' or metric_name like '%Physical Writes%Per Sec%'
 )
  pivot
  (sum(value) for inst_id in (1 as "inst_1",2 as "inst_2",3 as "inst_3",4 as "inst_4",5 as "inst_5",6 as "inst_6",7 as "inst_7",8 as "inst_8"))
order by 1,2;

prompt ****************************************************************************************
prompt recent IOFUNC check
prompt ****************************************************************************************
select inst_id,to_char(begin_time,'hh24:mi:ss')||'-'||to_char(end_time,'hh24:mi:ss') "time",FUNCTION_NAME,
ROUND(SMALL_READ_MBPS+LARGE_READ_MBPS,1) READ_MBPS,
ROUND(SMALL_WRITE_MBPS+LARGE_WRITE_MBPS,1) WRITE_MBPS,
ROUND(SMALL_READ_IOPS+LARGE_READ_IOPS,1)  READ_IOPS,
ROUND(SMALL_WRITE_IOPS+LARGE_WRITE_IOPS,1) WRITE_IOPS,
ROUND(AVG_WAIT_TIME ,1) AVG_WAIT_TIME
from gv$iofuncmetric order by 1,2,3;

prompt ****************************************************************************************
prompt recent topio sql check
prompt ****************************************************************************************
with b as (
 select inst_id,extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from gv$active_session_history
 where sample_time>systimestamp-numtodsinterval(60,'second') group by inst_id
 ),
c as (
select inst_id,username,sql_id,R_IOPs,R_MBPs,W_IOPs,W_MBPs,row_number()over(partition by inst_id order by R_IOPs+W_IOPs desc) rn1,
row_number()over(partition by inst_id,username,sql_id order by R_IOPs+W_IOPs desc) rn2 from (
select b.inst_id,(select us.name from sys.user$ us where us.user#=user_id)||case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id,
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs from gv$active_session_history a,b
where sample_time>systimestamp-numtodsinterval(60,'second') and a.inst_id=b.inst_id
group by user_id,program,b.deltaT,sql_id,b.inst_id))
select inst_id,username,sql_id,R_IOPs,R_MBPs,W_IOPs,W_MBPs from c where rn1<=10
union
select inst_id,username,sql_id,R_IOPs,R_MBPs,W_IOPs,W_MBPs from c where rn2<=10
order by 1,2,3;