Script to see wait time over an interval

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

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've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply