PERF_TIMEDWAITS_EVENTHISTOGRAM.sql

col event format a40
col UPPER_WAIT_TIME_MS format 999999999999999

prompt
Prompt Enter Wait Event Name
Prompt Histogram of wait event since Instance startup
prompt
prompt
SELECT NULL EVENT, NULL UPPER_WAIT_TIME_MS, NULL WAIT_COUNT, NULL TIME_WAITED_MS,
  NULL PERCENT, NULL CUM_PERCENT FROM DUAL WHERE 1 = 0
UNION ALL (
SELECT NULL EVENT, NULL UPPER_WAIT_TIME_MS, NULL WAIT_COUNT, NULL TIME_WAITED_MS,
  NULL PERCENT, NULL CUM_PERCENT FROM DUAL WHERE 1 = 0
) UNION ALL ( SELECT * FROM (
WITH B_INFO AS
( SELECT 
    DECODE(INSTANCE_NUMBER, -1, USERENV('INSTANCE'), INSTANCE_NUMBER) INSTANCE_NUMBER,
    EVENT_NAME
  FROM
  ( SELECT
      -1 INSTANCE_NUMBER,
      '&event_name' EVENT_NAME
    FROM
      DUAL
  )
)
SELECT
  EVENT,
  LPAD(UPPER_WAIT_TIME_MS, 18) UPPER_WAIT_TIME_MS,
  LPAD(WAIT_COUNT, 10) WAIT_COUNT,
  TO_CHAR(TIME_WAITED_MS, 9999999999990) TIME_WAITED_MS,
  TO_CHAR(PERCENT, 990.99) PERCENT,
  TO_CHAR(SUM(PERCENT) OVER (ORDER BY UPPER_WAIT_TIME_MS 
    RANGE UNBOUNDED PRECEDING), 9999990.99) CUM_PERCENT
FROM
( SELECT 
    EH.EVENT,
    EH.WAIT_TIME_MILLI UPPER_WAIT_TIME_MS,
    EH.WAIT_COUNT,
    EH.WAIT_TIME_MILLI * DECODE(EH.WAIT_TIME_MILLI, 1, 0.5, 0.75) * 
      EH.WAIT_COUNT TIME_WAITED_MS,
    RATIO_TO_REPORT(EH.WAIT_TIME_MILLI * 
      DECODE(EH.WAIT_TIME_MILLI, 1, 0.5, 0.75) * EH.WAIT_COUNT) 
      OVER () * 100 PERCENT
  FROM 
    B_INFO BI,
    GV$EVENT_HISTOGRAM EH
  WHERE
    BI.INSTANCE_NUMBER = EH.INST_ID AND
    EH.EVENT LIKE BI.EVENT_NAME
)
ORDER BY
  EVENT,
  UPPER_WAIT_TIME_MS
));