Script to see single wait per hour

I’ve taken a script I use to show a particular wait event over time and parameterized it a bit to make it more general.

Here is a zip of the script: zip(updated).

This is the same sort of information you see in an AWR report but just for one event and over all of the snapshots you have in your AWR.

Here is the main query:

select sn.END_INTERVAL_TIME,
(after.total_waits-before.total_waits) "number of waits",
(after.time_waited_micro-before.time_waited_micro)/
(after.total_waits-before.total_waits) "ave microseconds",
before.event_name "wait name"
from DBA_HIST_SYSTEM_EVENT before, 
DBA_HIST_SYSTEM_EVENT after,
DBA_HIST_SNAPSHOT sn
where before.event_name='&&WAITNAME' and
after.event_name=before.event_name and
after.snap_id=before.snap_id+1 and
after.instance_number=1 and
before.instance_number=after.instance_number and
after.snap_id=sn.snap_id and
after.instance_number=sn.instance_number and
(after.total_waits-before.total_waits) > &&MINIMUMWAITS
order by after.snap_id;

Here is a description of the two variables:

WAITNAME = Wait event like “log file sync”.

MINIMUMWAITS = Least amount of waits per hour to show that hour’s events.  Sometimes it is helpful to remove the hours where there isn’t much activity.

Here is some output with WAITNAME=log file sync and MINIMUMWAITS=100000:

END_INTERVAL_TIME          number of waits ave microseconds
-------------------------- --------------- ----------------
08-SEP-13 04.00.24.598 AM           194356       802.426964
08-SEP-13 05.00.38.147 AM           301482       705.784296
08-SEP-13 07.00.05.089 AM           162397       2736.56333
08-SEP-13 08.00.19.046 AM           333388       2380.95826
08-SEP-13 09.00.33.324 AM           439911       2469.45262
08-SEP-13 10.00.47.515 AM           697083        2847.4531
08-SEP-13 11.00.01.544 AM           744137       3424.96033
08-SEP-13 12.00.15.859 PM           731348       4022.53643
08-SEP-13 01.00.30.117 PM           674319       3691.63971
08-SEP-13 02.00.44.221 PM           748557        3108.9068

You can use this to identify changes in a waits average time and use an AWR report to do a deeper dive on any interval where you see something different from what is normal.

– Bobby