Search

OakieTags

more tags

Who's online

There are currently 0 users and 32 guests online.

Recent comments

Affiliations

Home

AWR: I/O latency fluctuations

Submitted by khailey on Fri, 2011-05-13 02:15

One thing I’ve been wishing was more prominent in OEM or other tools is the displays of the fluctuation in I/O times for Oracle. I/O times can be come long for reasons such as load or hardware issues. Know that something is up with the I/O latency is less than obvious using OEM. Luckily the data is easily query-able form AWR repository. Here is a query that gets the hourly average I/O latency for all User I/O and System I/O:

#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;"> 
set pagesize 100
col event_name format a30
col avg_ms format 99999.99
select
       btime, event_name,
       (time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0) avg_ms,
       (count_end-count_beg) ct
from (
select
       e.event_name,
       to_char(s.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI')  btime,
       total_waits count_end,
       time_waited_micro/1000 time_ms_end,
       Lag (e.time_waited_micro/1000)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
       Lag (e.total_waits)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
from
       DBA_HIST_SYSTEM_EVENT e,
       DBA_HIST_SNAPSHOT s
where
       s.snap_id=e.snap_id
   and s.begin_interval_time > sysdate -2
   and e.wait_class in ( 'User I/O', 'System I/O')
order by e.event_name, begin_interval_time
)
where (count_end-count_beg) > 0
order by event_name,btime
/
#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;"> 
18-APR-11 18:00       db file scattered read              8.24       4404
18-APR-11 19:00       db file scattered read              7.85       3256
18-APR-11 20:00       db file scattered read              8.22       3511
18-APR-11 21:00       db file scattered read              7.40       5025
18-APR-11 22:00       db file scattered read              6.01     290640
18-APR-11 23:00       db file scattered read              8.99       6775
19-APR-11 00:00       db file scattered read             22.08      30126
19-APR-11 01:00       db file scattered read              8.63     105273
19-APR-11 02:00       db file scattered read              5.62      24064
19-APR-11 03:00       db file scattered read              8.32       3368
19-APR-11 04:00       db file scattered read              9.51       3218
19-APR-11 05:00       db file scattered read              5.64      28113
19-APR-11 06:00       db file scattered read              7.75      11014
19-APR-11 07:00       db file scattered read              8.80      18040
19-APR-11 08:00       db file scattered read              8.00      11443
19-APR-11 09:00       db file scattered read              5.51      11543
19-APR-11 10:00       db file scattered read              5.48      19038

It would be nice to see these I/O times broken down by histograms. Something to look into with the wait histograms.