该脚本作用是从数据库层面监控最近1分钟的I/O情况，包括Iops、Mbps、I/O latency、TOP I/O sql等等。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
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; |