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 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.

Leave a Reply