Lutz Haake | Freelance Oracle Consultant

dba_hist_event_histogram holds a lot of infor­mation which can prove useful when analyzing the overall system performance.

To run the analysis, first define its boundaries.

Time:

define snap_from='28.08.2014 07:30'
define snap_to='28.08.2014 15:16'

Scope:

define wait_class='User I/O'

RAC instance, if needed:

define instance_num="%"

Now run the analysis:

select * from
 (
 select q1.*, q2.wait_total, q2.wait_total as w2 from
 (
 select eh.event_name, eh.wait_class, eh.wait_time_milli, eh.wait_count
 from dba_hist_event_histogram eh
 inner join dba_hist_snapshot sn on eh.snap_id = sn.snap_id and eh.instance_number = sn.instance_number
 where sn.begin_interval_time between to_date('&&snap_from', 'dd.mm.yyyy hh24:mi') and to_date('&&snap_to', 'dd.mm.yyyy hh24:mi')
 and eh.wait_class = 'User I/O'
 and eh.instance_number like '&&instance_num'
 ) q1,
 (
 select event_name, sum(wait_count) wait_total, min(sn.snap_id), max(sn.snap_id)
 from dba_hist_event_histogram eh
 inner join dba_hist_snapshot sn on eh.snap_id = sn.snap_id and eh.instance_number = sn.instance_number
 where begin_interval_time between to_date('&&snap_from', 'dd.mm.yyyy hh24:mi') and to_date('&&snap_to', 'dd.mm.yyyy hh24:mi')
 and wait_class = '&&wait_class'
 and eh.instance_number like '&&instance_num'
 group by event_name
 ) q2
 where q2.event_name=q1.event_name
 )
 pivot
 (
 sum(round(wait_count/w2*100 ,1))
 for (wait_time_milli) in
 (
 1 as "<1ms",
 2 as "1-2ms",
 4 as "2-4ms",
 8 as "4-8ms",
 16 as "8-16ms",
 32 as "16-32ms",
 64 as "32-64ms",
 128 as "64-128ms"
 )
 )
 order by wait_total desc
 ;

q1 holds the wait counts per snapshot and slot (WAIT_TIME_MILLI), while q2 holds the totals. The output might look like this:

The predo­minant waits are at the top of the list. Pasted into a excel 3d column diagram (ommitting the values <1ms), one sees the disti­bution curve:

Use the curve’s maximum to determine whether the I/O response times are within expected or agreed parameters. Focus on the predo­minant waits.