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.
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 for your comment. I use it a lot. I am glad you also find it to be useful.
Pingback: Another python graph – one wait event | Bobby Durrett's DBA Blog
Remove the IDLE WAIT
and before.event_name not in (select name from SYS.V_$EVENT_NAME WHERE WAIT_CLASS = ‘Idle’)
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.