From Oramosswiki
clear breaks
break on event# on event on total_ms_waited
column event# format 999 heading "Event#"
column event format a30 heading "Event"
column wait_time_ms format 999,999 heading "Maximum|Wait Time (ms)"
column wait_count format 999,999,999,999 heading "Wait|Count"
column percent_wait_count format 990.00 heading "Percent|Wait Count"
column percent_wait_time format 990.00 heading "Percent|Wait Time"
column total_waited_s format 999,999,999,999 heading "Total|Waited (s)"
SELECT event#
--******************************************************************************
--*
--* Description
--* ***********
--*
--* Looks at V$EVENT_HISTOGRAM for histogram of waits for specific events.
--*
--* Can change the events to whatever you wish - some sample IO events shown.
--*
--* ----------------------------------------------------------------------------
--* Date Author Description
--* =========== ================= ============================================
--* 30-APR-2009 Jeff Moss Created
--******************************************************************************
, event
, SUM(wait_time_milli * wait_count / 1000) OVER(PARTITION BY event#) total_waited_s
, wait_time_milli wait_time_ms
, wait_count
, ROUND(100 * RATIO_TO_REPORT(wait_count) OVER(PARTITION BY event#),2) percent_wait_count
, ROUND(100 * RATIO_TO_REPORT(wait_time_milli * wait_count) OVER(PARTITION BY event#),2) percent_wait_time
FROM v$event_histogram
WHERE event IN(
'xblock change tracking buffer space'
,'buffer busy waits'
,'db file parallel read'
,'db file parallel write'
,'db file scattered read'
,'db file sequential read'
,'db file single write'
,'direct path read'
,'direct path read temp'
,'direct path write'
,'direct path write temp'
,'local write wait'
,'Log archive I/O'
,'log file parallel write'
,'log file sequential read'
,'log file single write'
)
ORDER BY event
, wait_time_ms
/
