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

)

)

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

( 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

)

EVENT,

UPPER_WAIT_TIME_MS

));