select trunc(dhs.snap_time,'HH') snaphour,
       sum(se.time_waited_micro)/1000 total_wait_time, sum(se.total_waits), sum(se.time_waited_micro)/1000 / sum(se.total_waits) avg_time_waited
  from stats$snapshot dhs, stats$system_event se, v$event_name en
 where (dhs.snap_id = se.snap_id and dhs.instance_number = se.instance_number)
   and se.event_id = en.event_id and en.name = 'log file sync'
   and dhs.snap_id in
   (select se.snap_id from stats$system_event se, v$event_name en
        where se.event_id = en.event_id and en.name = 'log file sync')
group by trunc(dhs.snap_time,'HH') order by 1
/
