set feed off verify off termout off
define tops=20

col st1 format a14 heading "Startup Time"
col st2 format a14 heading "System Date"
col st3 format a12 heading "Running Time"
col st4 format a12 heading "Running Secs"

col TM new_value p_order_by

SELECT decode( value, 'FALSE', 'total_waits', 'time_waited' ) TM
FROM v$parameter WHERE name='timed_statistics'
/
set termout on

SELECT
  to_char( startup_time, 'dd/mm/yy hh24"h"mi' ) st1,
  to_char( sysdate, 'dd/mm/yy hh24"h"mi' ) st2,
  lpad( to_char( trunc(sysdate,'YEAR') + (sysdate-startup_time-1),
        decode( trunc( sysdate-startup_time, 0 ), 0, 'fm" 0d "hh24"h"mi', 'fm""dd"d "hh24"h"mi' ) ), 12, ' ' ) st3,
  to_char( (sysdate-startup_time)*24*60*60, '999g999g990' ) st4
FROM v$instance;

col "EVENT"              format            a40 Heading "Evento"
col "TOTAL_WAITS"        format  9G999G999G990 Heading "Total Waits"
col "TIME_WAITED (hms)"  format            a17 Heading "Time Waited (hms)"
col "TIME_WAITED (s)"    format      9G999G990 Heading "Time Waited (s)"
col "SECONDS_WAITED"     format      9G999G990 Heading "Seconds Waited"
col "AVG_WAITS(ms)"      format    999G999G990 Heading "Avg Wait (ms)"

SELECT event "EVENT", total_waits "TOTAL_WAITS",
       decode( trunc( time_waited/84600/100, 0 ), 0,
       to_char( to_date('31/01/2001', 'dd/mm/yyyy' )+time_waited/8460000, '"00d:"HH24:MI:SS' ),
       to_char( to_date('31/01/2001', 'dd/mm/yyyy' )+time_waited/8460000, 'DD"d":HH24:MI:SS' ) ) "TIME_WAITED (hms)",
       round( time_waited/100,2) "TIME_WAITED (s)",
       round( average_wait*10,0) "AVG_WAITS(ms)"
FROM
 ( SELECT * FROM v$system_event
   WHERE total_waits > 0
   and event not in -- Eventos considerados idle na versão 8.1.7
    ( 'smon timer', 'pmon timer', 'rdbms ipc message', 'Null event', 'parallel query dequeue',
      'pipe get', 'client message', 'SQL*Net message to client', 'SQL*Net message FROM client',
      'SQL*Net more data FROM client', 'SQL*Net more data to client', 'dispatcher timer',
      'virtual circuit status', 'lock manager wait for remote message', 'PX Idle Wait',
      'wakeup time manager' )
   order by &p_order_by. desc
 )
WHERE rownum <= &tops.
UNION ALL
SELECT event "EVENT", total_waits "TOTAL_WAITS",
       decode( trunc( time_waited/84600/100, 0 ), 0,
       to_char( to_date('31/01/2001', 'dd/mm/yyyy' )+time_waited/8460000, '"00d:"HH24:MI:SS' ),
       to_char( to_date('31/01/2001', 'dd/mm/yyyy' )+time_waited/8460000, 'DD"d":HH24:MI:SS' ) ) "TIME_WAITED (hms)",
       round( time_waited/100,2) "TIME_WAITED (s)",
       round( average_wait*10,0) "AVG_WAITS(ms)"
FROM
 ( SELECT 'Outros' Event, vtot.total_waits-vdif.total_waits total_waits,
                          vtot.time_waited-vdif.time_waited time_waited,
                          vtot.average_wait-vdif.average_wait average_wait
   FROM
   (
     SELECT sum(total_waits) total_waits, sum(time_waited) time_waited, null average_wait
     FROM v$system_event
     WHERE total_waits > 0
     and event not in -- Eventos considerados idle na versão 8.1.7
     ( 'smon timer', 'pmon timer', 'rdbms ipc message', 'Null event', 'parallel query dequeue',
      'pipe get', 'client message', 'SQL*Net message to client', 'SQL*Net message FROM client',
      'SQL*Net more data FROM client', 'SQL*Net more data to client', 'dispatcher timer',
      'virtual circuit status', 'lock manager wait for remote message', 'PX Idle Wait',
      'wakeup time manager' )
   ) vtot,
   (
    SELECT sum(total_waits) total_waits, sum(time_waited) time_waited, null average_wait
    FROM
      ( SELECT vdif2.*
        FROM
         (
           SELECT total_waits, time_waited, average_wait
           FROM v$system_event
           WHERE total_waits > 0
           and event not in -- Eventos considerados idle na versão 8.1.7
           ( 'smon timer', 'pmon timer', 'rdbms ipc message', 'Null event', 'parallel query dequeue',
            'pipe get', 'client message', 'SQL*Net message to client', 'SQL*Net message FROM client',
            'SQL*Net more data FROM client', 'SQL*Net more data to client', 'dispatcher timer',
            'virtual circuit status', 'lock manager wait for remote message', 'PX Idle Wait',
            'wakeup time manager' )
           order by &p_order_by. desc
         ) vdif2
        WHERE rownum <= &tops.
     ) vdif1
   ) vdif
 )
UNION ALL
SELECT event "EVENT", total_waits "TOTAL_WAITS",
       decode( trunc( time_waited/84600/100, 0 ), 0,
       to_char( to_date('31/01/2001', 'dd/mm/yyyy' )+time_waited/8460000, '"00d:"HH24:MI:SS' ),
       to_char( to_date('31/01/2001', 'dd/mm/yyyy' )+time_waited/8460000, 'DD"d":HH24:MI:SS' ) ) "TIME_WAITED (hms)",
       round( time_waited/100,2) "TIME_WAITED (s)",
       round( average_wait*10,0) "AVG_WAITS(ms)"
FROM
 ( SELECT 'Total' Event, sum(total_waits) total_waits, sum(time_waited) time_waited, null average_wait
   FROM v$system_event
   WHERE total_waits > 0
   and event not in -- Eventos considerados idle na versão 8.1.7
    ( 'smon timer', 'pmon timer', 'rdbms ipc message', 'Null event', 'parallel query dequeue',
      'pipe get', 'client message', 'SQL*Net message to client', 'SQL*Net message FROM client',
      'SQL*Net more data FROM client', 'SQL*Net more data to client', 'dispatcher timer',
      'virtual circuit status', 'lock manager wait for remote message', 'PX Idle Wait',
      'wakeup time manager' )
 )
/

prompt
set feed on verify on