IO Montoring



How many bytes per second? IOPS?, here is the last 60 seconds

rkbs and wrkbs look to be just the buffer cache
rtkbs and wtkbs , ie total, looked to be everything except to controlfile I/O
have to look into this more

select
       round((sum(decode(metric_name, 'Physical Read Bytes Per Sec' , value,0)))/1024,0)  rkbps,
       round((sum(decode(metric_name, 'Physical Read Total Bytes Per Sec' , value,0)))/1024,0) rtkbps,
       round((sum(decode(metric_name, 'Physical Read Total IO Requests Per Sec' , value,0 ))),1) rtops,
       round((sum(decode(metric_name, 'Physical Write Bytes Per Sec' , value,0 )))/1024,0)  wbps,
       round((sum(decode(metric_name, 'Physical Write Total Bytes Per Sec' , value,0 )))/1024,0) wtbps,
       round((sum(decode(metric_name, 'Physical Write Total IO Requests Per Sec', value,0 ))),1) wtops
 from  v$sysmetric
 where metric_name in (
                    'Physical Read Total Bytes Per Sec' ,
                    'Physical Read Bytes Per Sec' ,
                    'Physical Write Bytes Per Sec' ,
                    'Physical Write Total Bytes Per Sec' ,
                    'Physical Write Total IO Requests Per Sec',
                    'Physical Read Total IO Requests Per Sec'
                    )
 and group_id=2;



What is the average latency over the last 60 seconds for I/O waits?

 
select
       n.name event,
       m.wait_count  cnt,
       10*m.time_waited ms,
       nvl(round(10*m.time_waited/nullif(m.wait_count,0),3) ,0) avg_ms
  from v$eventmetric m,
       v$event_name n
  where m.event_id=n.event_id
        and ( wait_class_id= 1740759767 --  User I/O
             or
             wait_class_id= 4108307767 --  System I/O
           )
        and m.wait_count > 0 ;


What are the sizes of the I/Os? The main use of this query is to find the max size of I/Os. The min and avg have little meaning as the data is coming from a sampled data source that is skewed heavily towards the longer time events and thus skewed towards the larger I/Os.

col event for a25
    set define off
    select * from (
    select event, round(min(p1)) mn, round(avg(p1)) av,round(max(p1)) mx, count(*)  cnt
    from v$active_session_history
    where  event in
         ( 'Datapump dump file I/O',  'dbms_file_transfer I/O',
           'kst: async disk IO', 'ksfd: async disk IO',
           'Log archive I/O', 'RMAN backup & recovery I/O',
           'Standby redo I/O', 'kfk: async disk IO',
           'DG Broker configuration file I/O',  'Data file init write',   'Log file init write')
    group by event
    union all
    select event, round(min(p3)) mn, round(avg(p3)) av, round(max(p3)) mx, count(*)  cnt
    from v$active_session_history
    where  event in
         ('db file scattered read' ,  'direct path read' ,
          'control file sequential read',  'control file single write',
          'log file sequential read', 'log file single write',
          'direct path read temp' ,  'direct path write' ,
          'direct path write temp' ,  'control file parallel read' )
    group by event
    union all
    select event,round(min(p2)) mn, round(avg(p2)) av,round(max(p2)) mx, count(*)  cnt
    from v$active_session_history
    where  event in    ( 'control file parallel write' )
    group by event)
    order by event;
    set define on


What are the p1, p2 , p3 values for I/O waits?

   
col parameter1 for a15
    col parameter2 for a15
    col parameter3 for a15
    select name,parameter1,parameter2,parameter3 from v$event_name where wait_class='User I/O';
    select name,parameter1,parameter2,parameter3 from v$event_name where wait_class='System I/O';        

   

Comments