Oracle Database - Historical Events / Wait Times / Statistics - AWR Data Mining & Extraction

Oracle Database - Historical Events / Wait Times / Statistics - AWR Data Mining & Extraction

scripts Anonymous   April 24, 2017

In this blog I am going to discuss on how to mine and extract data from AWR Tables. 

In my previous blog I have discussed how to extract event / statistics currently (from V$views)

 Oracle Database - Current Wait Events / Metrics Statistics
 I will be discussing 3 particular AWR Views / Tables for extraction of data. 


DBA_HIST_SYSMETRIC_SUMMARY
  This view contains historical information for sysmetric V$ view. It is useful in getting important data such as Buffer Cache Hit Ratio.  This view is not cumulative, it is helpful in finding min/max/average values over period of time as a function of snap_id / time.

DBA_HIST_SYSTEM_EVENT
  This view is historical view to V$System_Event, the event statistics in V$system_event are snapped and kept in this view as per the AWR snapshot period. This event since is a snap of V$system_event is obviously cumulative, so to make sense or get the deltas, values should be subtracted from previous value



DBA_HIST_EVENT_HISTOGRAM
  This view is a snap of V$event_histogram, this view is again cumulative, so to find values for a one snap period period, values should be subtracted from previous snaps value. 


DBA_HIST_FILESTATXS

  This view is historical view to V$filestat, the event statistics in V$filestat are snapped and kept in the view. this view is also cumulative. 


Below are queries to get data from these views. 
I have written the queries in a such a way to get the # of days you want to look data for, the # of days can be like 1/24 signifying 1 hour. 

If the values of any of the historical stats are negative (in case of cumulative stats), that means instance was restarted before that snap.



Find out Buffer Cache Hit Ratio over Period of time 

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name'
ORDER BY SNAP_ID;

Find out Log file Sync Time over Period of time 

set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40


WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
 DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
 TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
 TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
  FROM
  DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
  WHERE
  EVENT_NAME  = '&event_name' AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID
 )
ORDER BY SNAP_ID;

Find out log file sync histogram details

SQL> break on snap_id on BEGIN_TIME skip 1
SQL> col BEGIN_TIME format a30
SQL> var snapid number;
SQL> EXEC SELECT MIN(SNAP_ID)  INTO :SNAPID FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME > sysdate - &days;
Enter value for days: 1/12
 
PL/SQL procedure successfully completed.
SQL> r
    with hist as  (
      select
           sn.snap_id,
           begin_interval_time btime,
           h.event_name,
           h.wait_time_milli,
           h.wait_count
      from dba_hist_event_histogram  h,
           dba_hist_snapshot sn
     where
            h.instance_number = &inst_num
        and sn.instance_number = h.instance_number
        and h.event_name =  '&event_name'
        and sn.snap_id=h.snap_id
        and sn.snap_id > :snapid
      )
   select  a.snap_id, a.btime BEGIN_TIME,
           a.wait_time_milli,
           b.wait_count - a.wait_count WAIT_COUNT
   from hist a,
        hist b
   where
      a.snap_id=b.snap_id-1
     and a.wait_time_milli = b.wait_time_milli
   order by a.snap_id, WAIT_TIME_MILLI

Enter value for inst_num: 1
old  11:          h.instance_number = &inst_num
new  11:          h.instance_number = 1
Enter value for event_name: log file sync
old  13:      and h.event_name =  '&event_name'
new  13:      and h.event_name =  'log file sync'

   SNAP_ID BEGIN_TIME                     WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------------ --------------- ----------
    278182 25-APR-17 06.00.47.887 AM                    1      26272
                                                        2       9054
                                                        4       6352
                                                        8       2734
                                                       16        827
                                                       32        273
                                                       64         27
                                                      128          1
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

    278183 25-APR-17 06.15.50.075 AM                    1      28470
                                                        2       9173
                                                        4       5343
                                                        8       1506
                                                       16        375
                                                       32        215
                                                       64         34
                                                      128          0
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

    278184 25-APR-17 06.30.51.845 AM                    1      28746
                                                        2       7457
                                                        4       3772
                                                        8       1121
                                                       16        369
                                                       32        214
                                                       64         26
                                                      128          2
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

    278185 25-APR-17 06.45.53.497 AM                    1      11102
                                                        2      10205
                                                        4      13808
                                                        8      10206
                                                       16       3895
                                                       32       4013
                                                       64       4255
                                                      128       2664
                                                      256        796
                                                      512       1679
                                                     1024       2481
                                                     2048       3255
                                                     4096        994
                                                     8192         36
                                                    16384          0

    278186 25-APR-17 07.00.55.247 AM                    1      17512
                                                        2      11811
                                                        4      10061
                                                        8       4623
                                                       16       1204
                                                       32        455
                                                       64         89
                                                      128         19
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

Find out File Statistics over Period of time


set lines 500 pages 500
col BEGIN_TIME for a30
break on FILE# SKIP 1

 WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT FILE#, SNAP_ID, BEGIN_TIME,
round(WRITETM * 10 / PHYWRTS,2) AS AVG_WRT_MS,
round(READTIM * 10 /PHYRDS,2) AS AVG_RD_MS FROM
(
SELECT FILE#,
 DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME,
 PHYWRTS - LAG(PHYWRTS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYWRTS,
 WRITETIM - LAG(WRITETIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) WRITETM,
 PHYRDS - LAG(PHYRDS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYRDS,
 READTIM - LAG(READTIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) READTIM
 FROM DBA_HIST_FILESTATXS DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
 WHERE FILE# IN (&file_id) AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID
)
ORDER BY FILE#, SNAP_ID;  

  Query Variations for RAC and specific Instance

DBA_HIST_SYSMETRIC Summary

Specific Instance

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
DHSYSM.INSTANCE_NUMBER = &instance_number
ORDER BY SNAP_ID;

RAC All Instances

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHSYSM.INSTANCE_NUMBER, DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
ORDER BY DHSYSM.INSTANCE_NUMBER, SNAP_ID;

DBA_HIST_SYSTEM_EVENT

Specific Instance

set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
 DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
 TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
 TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
  FROM
  DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
  WHERE
  EVENT_NAME  = '&event_name' AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID AND
  DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
  DHSE.INSTANCE_NUMBER = &instance_number
   )
ORDER BY SNAP_ID;

RAC All Instances

set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
INSTANCE_NUMBER, SNAP_ID, BEGIN_TIME, EVENT_NAME ,
TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC,
round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
 DHSE.INSTANCE_NUMBER,DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
 TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
 TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
  FROM
  DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
  WHERE
  EVENT_NAME  = '&event_name' AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID AND
  DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
   )
ORDER BY INSTANCE_NUMBER, SNAP_ID;

Oracle Database - Current Wait Events / Metrics Statistics

Oracle Database - Current Wait Events / Metrics Statistics

scripts Anonymous   April 19, 2017

In this blog I am going to discuss on how to find out current statistics in the database.
There are quite a few events and metrics available.
I will be discussing on how to find out those from the below available views. 

In the next blog I have discussed how to extract data from AWR tables.

 Oracle Database - Historical Events / Wait Times / Statistics - AWR Data Mining & Extraction
  
 V$EVENTMETRIC

    This view is used to find out latencies / values of various database events as of current time in the system, these are wait event deltas for past 60 seconds.  These events are wait events in the database example - log file sync or db file parallel write.

This is very useful as it helps you find out what are the latencies related to each wait event currently. 

V$SYSTEM_EVENT
  This view is values cumulative since startup of the system events and their latencies. 
Basically V$Eventmetric provides values for past 60 seconds and those values from startup are in V$system_event view


V$EVENT_HISTOGRAM
  This view is really helpful, as it provides values in a histogram fashion, i.e. how are the waits distributed in time windows. how many waits under 1ms, how many under 2ms and so on.
 

V$SYSMETRIC

   This view is used to find out current deltas of various other statistical metrics which oracle provides

For example - redo generated per second or Average Active Sessions

V$SYSMETRIC_HISTORY

  This view is super set of the above  view, it provides values for each  minute in past hour. 

you can think of it like retaining values from V$Sysmetric every minute for one hour.

This can help you find out history of a metric for past one hour, which can be really powerful in performance scenarios. 

V$SYSMETRIC_SUMMARY

  This view is similar to History view, however it provided last hours min, max and average values for the same metrics in sysmetric view.

 

V$WAITCLASSMETRIC
  This view is helpful in finding wait events on per class basis rather than individual events
for example - User I/O class has all db file scattered read, sequential read, read by other session events. These are deltas for past 60 seconds

 
V$WAITCLASSMETRIC_HISTORY
 This view provides deltas for 60 seconds for past one hour, again basically a superset of Waitclassmetric

  

V$SYSTEM_WAIT_CLASS
 This view is cumulative values since startup of waitclass wait events. 



V$FILEMETRIC
    This view is helpful in taking out file level statistics. It gives current latency in read / write for past 60 seconds on per file basis.  

 V$FILEMETRIC_HISTORY
 This view  provides file level statistics and extension to filemetric view for 10 minute interval for past one hour 

 
 V$FILESTAT
 This view provides read/write latency cumulative from instance startup at file level. 

Below are examples for usage of above views-

Find out current event statistics

set lines 500 pages 500
col event_name for a30

select evm.BEGIN_TIME, evm.END_TIME,
       evnt.name EVENT_NAME ,
       round(evm.time_waited,3) time_waited,
       evm.wait_count,
       round(10*evm.time_waited/nullif(evm.wait_count,0),3) AVG_MS  -- Multiply by 10 to convert centiseconds to milliseconds
from v$eventmetric evm,
     v$event_name evnt
where evm.event_id=evnt.event_id
  and evnt.name in ('log file sync',
                  'log file parallel write','direct path read',
                  'direct path read temp',
                  'direct path write',
                  'direct path write temp',
                  'log file single write',
                  'db file sequential read',
                  'db file scattered read',
                  'control file sequential read')
                  order by EVENT_NAME;
 

Find out event statistics cumulative from startup

SQL > set lines 500 pages 500
SQL >  col EVENT_NAME for a30
SQL >  r
    select EVENT AS  EVENT_NAME, AVERAGE_WAIT*10 AVERAGE_WAIT,AVERAGE_WAIT_FG*10 AVERAGE_WAIT_FG
    FROM
     V$SYSTEM_EVENT
    where event in ('log file sync',
                      'log file parallel write','direct path read',
                      'direct path read temp',
                      'direct path write',
                      'direct path write temp',
                      'log file single write',
                     'db file sequential read',
                     'db file scattered read',
                     'control file sequential read')
                    order by EVENT

Find out event statistics histogram for log file sync



 set numwidth 15
col EVENT for a30

SELECT
 EVENT, WAIT_TIME_MILLI, WAIT_COUNT
FROM V$EVENT_HISTOGRAM
WHERE EVENT='&event'
ORDER BY  WAIT_TIME_MILLI;

Enter value for event: log file sync
old   4: WHERE EVENT='&event'
new   4: WHERE EVENT='log file sync'

EVENT           WAIT_TIME_MILLI      WAIT_COUNT
--------------- --------------- ---------------
log file sync                 1       334695742
log file sync                 2      1068423223
log file sync                 4       853888410
log file sync                 8       303521739
log file sync                16       114332733
log file sync                32        96808378
log file sync                64        40842144
log file sync               128         6424359
log file sync               256          798555
log file sync               512          198027
log file sync              1024           49883
log file sync              2048           20747
log file sync              4096            2602
log file sync              8192             417
log file sync             16384              12
log file sync             32768              12
log file sync             65536             251

Find out various database metrics stats
                
set lines 500 pages 500
         
select BEGIN_TIME, END_TIME, METRIC_NAME, round(value,2) VALUE , METRIC_UNIT
from v$sysmetric
where metric_name in
('Average Synchronous Single-Block Read Latency',
'I/O Megabytes per Second','Redo Generated Per Sec',
'Physical Read Total Bytes Per Sec',
'Physical Read Bytes Per Sec',
'Physical Write Total Bytes Per Sec',
'Physical Write Bytes Per Sec',
'Average Active Sessions')
order by METRIC_NAME;

Find out Average Single Block Read Latency for Past One hour -

select BEGIN_TIME, END_TIME, METRIC_NAME, round(value,2) VALUE , METRIC_UNIT
from v$sysmetric_history
where metric_name in
('Average Synchronous Single-Block Read Latency')
order by begin_time;

Find out MIN/MAX/AVG Metric Values for Past one Hour

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a50
col METRIC_UNIT for a30

select BEGIN_TIME, END_TIME, METRIC_NAME, round(MINVAL,2) MIN_VAL, round(MAXVAL,2) MAX_VAL , round(AVERAGE,2) AVG_VAL, METRIC_UNIT
from v$sysmetric_summary
where metric_name in
('Average Synchronous Single-Block Read Latency',
'I/O Megabytes per Second','Redo Generated Per Sec',
'Physical Read Total Bytes Per Sec',
'Physical Read Bytes Per Sec',
'Physical Write Total Bytes Per Sec',
'Physical Write Bytes Per Sec',
'Average Active Sessions')
order by METRIC_NAME;

 


Find out Latency in wait class User I/o - 


SQL > set lines 500 pages 500SQL > col WAIT_CLASS for a15SQL > r
   select
           BEGIN_TIME, END_TIME, SWC.WAIT_CLASS, round(10*wcm.time_waited/nullif(wcm.wait_count,0),3) AVG_MS -- convert centisecs to millisecs
    from   v$waitclassmetric  wcm,
           v$system_wait_class swc
    where wcm.wait_class_id=swc.wait_class_id
     and swc.wait_class='User I/O'

BEGIN_TIME         END_TIME           WAIT_CLASS          AVG_MS
------------------ ------------------ --------------- ----------
19-APR-17 05:54:16 19-APR-17 05:55:16 User I/O              .496



Find out Latency in wait class User I/o for past one hour -

SQL > set lines 500 pages 500SQL > col WAIT_CLASS for a15SQL > r
    SELECT
            BEGIN_TIME, END_TIME, SWC.WAIT_CLASS, round(10*wcm.time_waited/nullif(wcm.wait_count,0),3) AVG_MS -- convert centisecs to millisecs
    FROM   V$WAITCLASSMETRIC_HISTORY  wcm,
           v$system_wait_class swc
    WHERE wcm.wait_class_id=swc.wait_class_id
     AND swc.wait_class='User I/O'

BEGIN_TIME         END_TIME           WAIT_CLASS          AVG_MS
------------------ ------------------ --------------- ----------
19-APR-17 05:55:16 19-APR-17 05:56:15 User I/O              .652
19-APR-17 05:54:16 19-APR-17 05:55:16 User I/O              .496
19-APR-17 05:53:16 19-APR-17 05:54:16 User I/O              .554
19-APR-17 05:52:15 19-APR-17 05:53:16 User I/O              .529
19-APR-17 05:51:16 19-APR-17 05:52:15 User I/O              .583
19-APR-17 05:50:16 19-APR-17 05:51:16 User I/O              .868
19-APR-17 05:49:15 19-APR-17 05:50:16 User I/O              .965
19-APR-17 05:48:15 19-APR-17 05:49:15 User I/O             1.022
19-APR-17 05:47:16 19-APR-17 05:48:15 User I/O              .728
19-APR-17 05:46:16 19-APR-17 05:47:16 User I/O              .651
19-APR-17 05:45:15 19-APR-17 05:46:16 User I/O              .581
19-APR-17 05:44:15 19-APR-17 05:45:15 User I/O              .649
19-APR-17 05:43:16 19-APR-17 05:44:15 User I/O              .658
19-APR-17 05:42:16 19-APR-17 05:43:16 User I/O              .637


Find out Latency in wait class User I/o cumulative from startup - 

  1  SELECT
  2          WAIT_CLASS, time_waited*10/TOTAL_WAITS AVG_MS    3          FROM
  4         V$SYSTEM_WAIT_CLASS
  5* WHERE WAIT_CLASS='User I/O'

WAIT_CLASS          AVG_MS
--------------- ----------
User I/O        1.00084214



Find out  current read / write latency at file level

set lines 500 pages 500
SELECT
FILE_ID, BEGIN_TIME, END_TIME, AVERAGE_READ_TIME * 10 AVG_RD_MS , AVERAGE_WRITE_TIME * 10 AVG_WRT_MS, INTSIZE_CSEC
FROM V$FILEMETRIC
WHERE FILE_ID IN (1,2,3)
ORDER BY FILE_ID;  

   FILE_ID BEGIN_TIME           END_TIME              AVG_RD_MS AVG_WRT_MS INTSIZE_CSEC
---------- -------------------- -------------------- ---------- ---------- ------------
         1 23-APR-2017 10:07:41 23-APR-2017 10:17:41 .481927711 3.88286334        60026
         2 23-APR-2017 10:07:41 23-APR-2017 10:17:41 .354154214 1.73882584        60026
         3 23-APR-2017 10:07:41 23-APR-2017 10:17:41 .561422736 6.98739217        60026 

Find out  current read / write latency at file level for past one hour

SELECT
FILE_ID, BEGIN_TIME, END_TIME, INTSIZE_CSEC, AVERAGE_READ_TIME * 10 AVG_RD_MS , AVERAGE_WRITE_TIME * 10 AVG_WRT_MS
FROM V$FILEMETRIC_HISTORY
WHERE FILE_ID IN (1,2,3)
ORDER BY FILE_ID, BEGIN_TIME;
  
   FILE_ID BEGIN_TIME           END_TIME             INTSIZE_CSEC  AVG_RD_MS AVG_WRT_MS
---------- -------------------- -------------------- ------------ ---------- ----------
         1 23-APR-2017 09:07:41 23-APR-2017 09:17:41        60009 .411153791 .476190476
         1 23-APR-2017 09:17:41 23-APR-2017 09:27:41        59974 .403685827 2.35294118
         1 23-APR-2017 09:27:41 23-APR-2017 09:37:40        59988 .388768898         .4
         1 23-APR-2017 09:37:40 23-APR-2017 09:47:40        60008 .409824361  2.4137931
         1 23-APR-2017 09:47:40 23-APR-2017 09:57:41        60025 .422555948          0
         1 23-APR-2017 09:57:41 23-APR-2017 10:07:41        59974 .439093484 2.33333333
         1 23-APR-2017 10:07:41 23-APR-2017 10:17:41        60026 .481927711 3.88286334
         2 23-APR-2017 09:07:41 23-APR-2017 09:17:41        60009 .348623853 1.59437129
         2 23-APR-2017 09:17:41 23-APR-2017 09:27:41        59974 .347034561 1.72499271
         2 23-APR-2017 09:27:41 23-APR-2017 09:37:40        59988 .352133255 1.65487745
         2 23-APR-2017 09:37:40 23-APR-2017 09:47:40        60008 .354619565 1.65991317
         2 23-APR-2017 09:47:40 23-APR-2017 09:57:41        60025 .350512946 1.60788234
         2 23-APR-2017 09:57:41 23-APR-2017 10:07:41        59974  .34965035 1.67443032
         2 23-APR-2017 10:07:41 23-APR-2017 10:17:41        60026 .354154214 1.73882584
         3 23-APR-2017 09:07:41 23-APR-2017 09:17:41        60009 .373831776 4.23076923
         3 23-APR-2017 09:17:41 23-APR-2017 09:27:41        59974 .362227143        4.2
         3 23-APR-2017 09:27:41 23-APR-2017 09:37:40        59988 .361088655 4.27272727
         3 23-APR-2017 09:37:40 23-APR-2017 09:47:40        60008 .373976202 5.70621469
         3 23-APR-2017 09:47:40 23-APR-2017 09:57:41        60025 .394984326  4.8255814
         3 23-APR-2017 09:57:41 23-APR-2017 10:07:41        59974 .394618834 4.31818182
         3 23-APR-2017 10:07:41 23-APR-2017 10:17:41        60026 .561422736 6.98739217



Find out  current read / write latency cumulative


SELECT FILE#,
 WRITETIM * 10 /PHYWRTS as AVG_WRT_MS ,READTIM * 10 /PHYRDS AS AVG_RD_MS
 FROM V$FILESTAT
 WHERE FILE# IN (1,2,3)
ORDER BY FILE#;  

     FILE# AVG_WRT_MS  AVG_RD_MS
---------- ---------- ----------
         1 1.54151474 .351090769
         2 1.23481134 .361346068
         3  3.2816945 .332541687