Another simple script: zip.
This is like an AWR report where you see a difference in v$ view values over time. But, it is for one wait event over a single interval. Simple but possibly useful.
drop table waitdiff; create table waitdiff as select * from v$system_event where event='&&WAITNAME'; host sleep &&INTERVALSECONDS select s.event, (s.TOTAL_WAITS-d.TOTAL_WAITS) "Number of waits", (s.TIME_WAITED_MICRO-d.TIME_WAITED_MICRO)/ (s.TOTAL_WAITS-d.TOTAL_WAITS) "Avg microseconds" from waitdiff d, v$system_event s where d.event=s.event and (s.TOTAL_WAITS-d.TOTAL_WAITS) > 0;
WAITNAME is the wait event such as “log file sync”. INTERVALSECONDS is how long you want to wait between checks of the waits – i.e. 60 for 60 seconds.
Slightly edited output:
EVENT Number of waits Avg microseconds ------------------------ --------------- ---------------- log file parallel write 11412 2642.81151
– Bobby
