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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

5 Responses to Script to see single wait per hour

  1. Thushara says:

    Really a useful script and used it to find out the ‘log file synch’ event after we changed our DataGuard from MaxAvailability to Maxperformance.

    Thanks,
    TDS.

  2. Pingback: Another python graph – one wait event | Bobby Durrett's DBA Blog

  3. kal says:

    Remove the IDLE WAIT
    and before.event_name not in (select name from SYS.V_$EVENT_NAME WHERE WAIT_CLASS = ‘Idle’)

    • Bobby says:

      Thanks for your comment! I think that in this script I am choosing which specific event to look at. So, I think that I am the one who decides whether the event is worth seeing instead of relying on the wait class to filter out events as not having value.

Leave a Reply to Bobby Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.