set pages 999;

spool event.lst;

column c0 format 999;
column c1 heading 'event'        format a35;
column c2 heading 'tot waits'    format 9,999,999;
column c3 heading 'tot timeouts' format 9,999,999;
column c4 heading 'avg waits'    format 9,999,999;
column c5 heading 'time waited'    format 999,999,999;

select distinct 
   substr(event,1,30) c1, 
   sum(total_waits)        c2, 
--   sum(total_timeouts)     c3, 
   sum(average_wait)       c4,
   sum(time_waited)         c5
 from v$session_event
where 
average_wait > 0 
or 
total_timeouts > 0
group by
   substr(event,1,30) 
order by
sum(time_waited) desc
;


column c0 heading 'tablespace'   format a14;
column c1 heading 'event'        format a25;
column c2 heading 'tot waits'    format 9,999,999;
column c3 heading 'tot timeouts' format 9,999,999;
column c4 heading 'avg waits'    format 999,999;

break on c1 skip 1;

select distinct
   substr(a.event,1,25) c1,
   substr(b.tablespace_name,1,14) c0,
   sum(a.total_waits)        c2, 
   sum(a.total_timeouts)     c3, 
   sum(a.average_wait)       c4
 from v$session_event a, dba_data_files b, v$session c
where
a.sid = c.sid
and
b.file_id = c.ROW_WAIT_FILE#
and
(
average_wait > 0
or
total_timeouts > 10
)
and
total_waits > 1000
group by
   substr(tablespace_name,1,14),
   substr(event,1,25)
order by
   substr(a.event,1,25), 
sum(total_waits) desc
;